CREATE PROCEDURE `pro_insert_tssshis`()
BEGIN
/*定义错误标识*/
DECLARE t_error INTEGER DEFAULT 0;
DECLARE t_count INTEGER DEFAULT 0;
DECLARE t_i int DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
set @create_time = CAST((SELECT UNIX_TIMESTAMP(CURDATE()-INTERVAL 61 DAY))*1000 AS DECIMAL(30));
select count(*) into t_count from tsss_waybill
-- where flag<>4 and create_order_time<@create_time;
where create_order_time<@create_time;
WHILE t_i < CEIL(t_count/100000) DO
insert into test_pro(p0,p1,p2,p5) values(@create_time,0,"tssshis",t_i);
START TRANSACTION;
/*订单数据备份表*/
insert into tsss_waybill_his
select * from tsss_waybill
where create_order_time<@create_time limit 0,100000;
-- where flag<>4 and create_order_time<@create_time limit 0,100000;
/*删除已备份订单数据*/
delete a from tsss_waybill a,tsss_waybill_his b
where a.id=b.id and a.create_order_time<@create_time;
/*更新执行结果*/
update test_pro set p3=t_error,p6=t_count where p0=@create_time and p5=t_i;
SET t_i = t_i+1;
/*如果错误则进行回滚*/
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END WHILE;
end
mysql 存储过程,数据迁移历史存储过程,每次10万条commit,错误回滚
最新推荐文章于 2021-04-22 00:08:18 发布