每天4点将4天前的数据转移到历史表,减少业务表的压力。
确认MySQL计划任务是否开启
查看定时任务状态
show variables like '%event_sche%'
开启定时任务
set global event_scheduler = 1
关闭定时任务
set global event_scheduler = 1
创建存储过程(即:函数)
CREATE DEFINER=`root`@`localhost` PROCEDURE `dtl_move_his`( )
BEGIN
/*备份4天之前的数据*/
INSERT INTO `dtl_his` SELECT
*
FROM
`dtl` AS d
WHERE
DATE( d.create_time ) <= DATE_SUB( CURDATE(), INTERVAL + 4 DAY );
/*删除4天之前的数据*/
DELETE
FROM
`dtl` AS d
WHERE
DATE( d.create_time ) <= DATE_SUB( CURDATE(), INTERVAL + 4 DAY );
END
创建计划任务(即:事件)
CREATE EVENT `db`.`dtl_move_his_event`
ON SCHEDULE
EVERY '1' DAY STARTS '2019-12-12 04:00:00'
DO CALL `dtl_move_his`();