插入1000表(WHILE i<1000 DO)控制
DELIMITER $$
USE `source`$$
DROP PROCEDURE IF EXISTS `pro_TableCreate`$$
CREATE DEFINER=`root`@`%` PROCEDURE `pro_TableCreate`(
)
BEGIN
DECLARE i INT;
DECLARE num INT;
DECLARE table_name VARCHAR(20);
SET i = 0;
SET num = 0;
WHILE i<1000 DO
#为了使表名成为xxx00这样的格式加的条件判断
IF i<10 THEN
SET table_name = CONCAT('transfer_table0',i);
ELSE
SET table_name = CONCAT('transfer_table',i);
END IF;
SET @csql = CONCAT(
'CREATE TABLE ',table_name,'(
id int not null primary key,
score int null);'
);
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
END$$
DELIMITER ;
CALL pro_TableCreate();
插入两条数据每个表(注意主键的区域不要冲突)
DELIMITER $$
USE `source`$$
DROP PROCEDURE IF EXISTS `pro_TableCreate`$$
CREATE DEFINER=`root`@`%` PROCEDURE `pro_TableCreate`(
)
BEGIN
DECLARE i INT;
DECLARE num INT;
DECLARE table_name VARCHAR(20);
SET i = 0;
SET num = 0;
WHILE i<1000 DO
#为了使表名成为xxx00这样的格式加的条件判断
IF i<10 THEN
SET table_name = CONCAT('transfer_table0',i);
ELSE
SET table_name = CONCAT('transfer_table',i);
END IF;
SET @csql = CONCAT(
'CREATE TABLE ',table_name,'(
id int not null primary key,
score int null);'
);
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
END$$
DELIMITER ;
CALL pro_TableCreate();
执行结果:
单个表插入一万条数据可查看连接: