迁移数据到历史表，减少业务表中数据压力 Mysql

#数据迁移存储过程
DROP PROCEDURE IF EXISTS `delete_platform_patient`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `delete_platform_patient`()
BEGIN
declare counts integer;-- 记录迁移条数
declare date_former datetime;-- 定义一个时间变量用来存放时间
declare result_code integer; -- 定义返回结果并赋初始值
declare CONTINUE HANDLER for SQLEXCEPTION set result_code=1; -- 在执行过程中出现任何异常result_code为1
select date_sub(now(),interval 3 month) into date_former; -- 获取三个月前的时间存入定义的时间变量中
select count(1) into counts from platform_patient where create_time<date_former or update_time<date_former;
start TRANSACTION;-- 进行事物处理
insert into platform_patient_historys select * from platform_patient p where p.create_time<date_former or p.update_time<date_former;-- 向历史表插入三个月前的数据
delete from platform_patient where create_time<date_former or update_time<date_former; -- 插入后就删除历史数据
insert into t_yh_move_data_log (time,tablename,count) values(NOW(),'platform_patient',counts);
IF result_code = 1 THEN -- 发生异常就回滚反之就提交
ROLLBACK;
ELSE
COMMIT;
END IF;
END
;;
DELIMITER ;

#每天定时触发
DROP EVENT
IF EXISTS `delete_platform_patient`;
DELIMITER ;;

CREATE DEFINER = `root`@`%` EVENT `delete_platform_patient` ON SCHEDULE EVERY 1 DAY STARTS '2019-07-30 01:10:00' ON COMPLETION PRESERVE ENABLE DO
CALL delete_platform_patient ();;
DELIMITER ;
#解决事件不起作用
set global event_scheduler = ON;show variables like 'event%';

转载于:https://www.cnblogs.com/ruize-coding/p/11269587.html