MySQL批量插入测试数据慢，试试这个命令

项目所需，需要插入一些测试数据，但是直接执行速度很慢

delimiter $$
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE i INT;
SET i=1;
WHILE i <= 5000 DO
		INSERT INTO mall_user_address(`user_id` ,`user_name` , `province` , `city` , `district`  , `detail` , `phone`,`is_default`) VALUES(i,"奇奇怪怪的zz","广东省","深圳","龙岗区","某某花园","xxxxxxxxx",1);
SET i=i+1;
END WHILE;
END $$

delimiter ;
call proc_batch_insert();

5000 条数据居然执行了快两分钟

优化一下

SET AUTOCOMMIT = 0;
delimiter $$
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE i INT;
SET i=1;
WHILE i <= 5000 DO
		INSERT INTO mall_user_address(`user_id` ,`user_name` , `province` , `city` , `district`  , `detail` , `phone`,`is_default`) VALUES(i,"伟明","广东省","深圳","龙岗区","南岭花园","15989332320",1);
SET i=i+1;
END WHILE;
END $$

delimiter ;
call proc_batch_insert();
SET AUTOCOMMIT = 1;

在执行存储过程之前把自动提交关闭 SET AUTOCOMMIT = 0;

执行完成在重新设置回SET AUTOCOMMIT = 1;

看看效果

call proc_batch_insert()
> OK
> 时间: 1.451s

只用 1.5秒