mysql定期删除历史数据并备份

存储过程+事件 实现定期删除历史数据并备份

CREATE PROCEDURE Clean_epoint_job_log()
 BEGIN
-- 	 清空备份表
	truncate epoint_job_log_bak;
--  历史数据入库
	insert into epoint_job_log_bak select * from epoint_job_log where trigger_time < DATE_SUB(CURDATE(), INTERVAL 7 DAY);
--  删除7天前的数据
	delete from epoint_job_log where trigger_time < DATE_SUB(CURDATE(), INTERVAL 7 DAY);
  end;

-- 每天凌晨1点执行
CREATE EVENT Clean_epoint_job_log ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) ON COMPLETION PRESERVE DO
CALL Clean_epoint_job_log ( );