mysql大数据量批量提交
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;
更多推荐
所有评论(0)