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`