delimiter $$
create procedure load2(count int unsigned)
begin
declare s int unsigned default 1;
declare c char(80) default repeat('a',80);
while s<=count do
insert into t1 select null,c;
set s = s+1;
end while;
end$$
delimiter ;
上面的代码,在循环过程中提交事务,会非常慢。每一次提交事务都要写一次redo log。(事务提交时,需要将重做日志缓冲刷新到磁盘)
下面,将整个循环定义为一个事务,速度会比上面的快很多很多,只写redo log一次。
delimiter $$
create procedure load3(count int unsigned)
begin
declare s int unsigned default 1;
declare c char(80) default repeat('a',80);
start transaction;
while s<=count do
insert into t1 select null,c;
set s = s+1;
end while;
commit;
end$$
delimiter ;