mysql 存储过程，数据迁移历史存储过程，每次10万条commit，错误回滚

最新推荐文章于 2021-03-18 02:46:03 发布

原创最新推荐文章于 2021-03-18 02:46:03 发布 · 298 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个MySQL存储过程，用于批量迁移和备份大量数据。该过程通过将旧数据迁移到历史表并从源表中删除，实现数据的周期性清理与存档。详细步骤包括：设置错误标识，计算数据范围，循环执行数据迁移和删除，以及记录执行结果。

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