MySQL批量导入，执行慢？一招教你解决

关于实习过程中二当家要我测试500万数据的执行效率，上过刀山下过火海哪见过如此大场面的我果断想到了存储过程脚本。

delimiter @@;
create PROCEDURE doinsertsss()
BEGIN
DECLARE i int;
set i = 0;
While (i<5000000) DO
INSERT INTO device_record_info_2023
(dept_id,account_number,user_name,device_no,total_flow,freeze_date_flow,Instantaneous_flow,reverse_flow,start_frequency,temprature,battery_voltage,signal_value,valve_statu,upload_month,upload_time)
VALUES
(234,53620331,'测试3','3622123400000134',19.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,202303,'2023-03-19 00:00:00');
set i = i + 1;
end WHILE;
end @@;
delimiter ;

家人们说实话500万后面几个0我数了三遍个十百千万十万。。。。。。。。。

然后执行脚本,好慢，我想下班。好几分钟导了十几万数据？？？

call doinsertsss();

于是我加了个事务

DROP PROCEDURE doinsertsss;
delimiter @@;
create PROCEDURE doinsertsss()
BEGIN
DECLARE i int;
start transaction;
set i = 0;
While (i<5000000) DO
INSERT INTO device_record_info_2023
(dept_id,account_number,user_name,device_no,total_flow,freeze_date_flow,Instantaneous_flow,reverse_flow,start_frequency,temprature,battery_voltage,signal_value,valve_statu,upload_month,upload_time)
VALUES
(234,53620331,'测试3','3622123400000134',19.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,202303,'2023-03-19 00:00:00');
set i = i + 1;
end WHILE;
COMMIT;
end @@;
delimiter ;

 三分钟搞定！！！

如果有人知道原理，请告诉我，如果我先知道，我就不告诉你们，因为我要自己偷偷的卷，然后惊艳所有人！！！