mysql大数据量批量提交

DROP PROCEDURE IF EXISTS test.insert_bacth_commit_test1;

CREATE PROCEDURE test.`insert_bacth_commit_test1`()

begin

declare start_num int default 0; -- 初始设置起始行数

declare end_num int default 5;-- 初始设施结束行数

declare cnt_srouce int default 0; -- 定义源表数据count变量

declare cnt_t int default 0; -- 定义目标表数据count变量

set cnt_srouce = (select count(1) from t2); -- 初始化源表数据量统计值

set cnt_t=0; -- 初始化目标表数据量统计值

set autocommit = 0; -- 关闭自动提交

delete from t1; -- 清空目标表

commit; -- 提交事务

while cnt_t<cnt_srouce do -- 当目标表数据量小于源表数据量时执行循环，反之结束循环

select cnt_t;

select start_num;

select end_num;

insert into t1(id,name) -- 插入数据

select id,name from(select @rn:=@rn+1 as rn,id as id,name as name from t2,(select@rn:=0) b) t3 where t3.rn>=start_num and t3.rn<end_num;

set cnt_t = (select count(1) from t1); -- 统计目标标数据量赋值给cnt_t

set start_num=start_num+5;

set end_num=end_num+5;

commit;

end while;

-- select * from t1;

-- select cnt_srouce;

-- select cnt_t;

end;