MySQL快速插入500万条数据
2021-09-03 14:45:44
# 创建用户表
```
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
# 创建存储过程
```
CREATE PROCEDURE insertPro(in sum INT)
BEGIN
DECLARE count INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
set count=0;
set i = 1;
set @exesql = concat("insert into t1(name) values ");
set @exedata = "";
while count<sum do
set @exedata = concat(@exedata, ",('", i, "')");
set count=count+1;
set i=i+1;
if i%1000=0
then
set @exedata = SUBSTRING(@exedata, 2);
set @exesql = concat("insert into t1(name) values ", @exedata);
prepare stmt from @exesql;
execute stmt;
DEALLOCATE prepare stmt;
set @exedata = "";
end if;
end while;
if length(@exedata)>0
then
set @exedata = SUBSTRING(@exedata, 2);
set @exesql = concat("insert into t1(name) values ", @exedata);
prepare stmt from @exesql;
execute stmt;
DEALLOCATE prepare stmt;
end if;
end;
```
# 调用存储过程
`call insertPro(5000000); `
# 删除存储过程(当出现PROCEDURE already exist 时使用)
`DROP PROCEDURE insertPro`