mysql 使用存储过程循环批量创建表和数据sql

 -- 删除存储过程
drop PROCEDURE createTables;

-- 创建存储过程
DELIMITER $$
CREATE
PROCEDURE `createTables`()
BEGIN

DECLARE i INT;

DECLARE table_name VARCHAR(20);

DECLARE table_pre VARCHAR(20);

DECLARE sql_text VARCHAR(2000);

SET i=0;

SET table_name='';

SET table_pre='ide_data_';

SET sql_text='';

WHILE i<20000 DO

IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);

ELSE SET table_name=CONCAT(table_pre,i);

END IF;

SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
  `id` varchar(100) NOT NULL,
  `table_code` varchar(10) DEFAULT \'\',
  `tenant_id` varchar(100) DEFAULT \'\',
  `create_date` varchar(32) DEFAULT \'\',
  `create_by` varchar(255) DEFAULT \'\',
  `update_date` varchar(32) DEFAULT \'\',
  `update_by` varchar(255) DEFAULT \'\',
  `org_id` varchar(100) DEFAULT \'\',
  `ext_data` blob DEFAULT NULL,
  `sys_remark` varchar(255) DEFAULT \'\' COMMENT \'系统备注\',
  PRIMARY KEY (`id`,tenant_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 PARTITION by key(tenant_id) partitions 100 ');
-- set table_name='ide_data_01';
-- set sql_text=concat('insert into ' ,table_name,' (id,table_code,tenant_id,sys_remark ,ext_data ) values (',i,',\'vaa\',\'100\',\'xxxx\',column_create(\'name\',\'==name\',\'code\',\'C',i,'\')) ');
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END$$
DELIMITER ;

-- 执行存储过程
call createTables(); 

show procedure status; 列出当前的存储过程

-- 显示存储过程的具体内容
show create procedure createTables