mysql bulk存储过程_mysql-使用存储过程创建大批量数据

参考：https://www.iteye.com/blog/825635381-2161290

场景1.创建1万个table，每个table种插入1条记录

DELIMITER $$

CREATE DATABASE `lr_test` $$

USE `lr_test` $$

DROP PROCEDURE IF EXISTS `cyc` $$

CREATE PROCEDURE `cyc`(

)

BEGIN

DECLARE i INT;

DECLARE MAX INT DEFAULT 10000;

DECLARE table_name VARCHAR(20);

SET i = 0;

WHILE i

SET table_name = CONCAT('e_',i);

SET @csql = CONCAT('CREATE TABLE ', table_name, '(ID int) ENGINE=Innodb default charset=utf8;');

PREPARE create_stmt FROM @csql;

EXECUTE create_stmt;

SET @insertsql = CONCAT('INSERT INTO ', table_name, ' VALUES(', 1, ') ');

PREPARE insert_stmt FROM @insertsql;

EXECUTE insert_stmt;

SET i = i + 1;

END WHILE;

END $$

DELIMITER ;

call cyc;

场景2.在1张table种插入1万条记录

DELIMITER $$

#CREATE DATABASE `lr_test` $$

#USE `lr_test` $$

DROP PROCEDURE IF EXISTS `cyc` $$

CREATE PROCEDURE `cyc`(

)

BEGIN

DECLARE i INT DEFAULT 0;

DECLARE MAX INT DEFAULT 10000;

WHILE i < MAX DO

SET @insertsql = CONCAT('INSERT INTO lr_test.a_1 VALUES(', i, ') ');

PREPARE insert_stmt FROM @insertsql;

EXECUTE insert_stmt;

SET i = i + 1;

END WHILE;

END $$

DELIMITER ;

call cyc;