mysql定时清理数据

设置MySQL

SHOW VARIABLES LIKE 'event_scheduler'; # 查看开关

SET GLOBAL event_scheduler = ON; #打开事件调度器

永久开启方法：my.cnf中[mysqld]添加event_scheduler=on #重启服务

查看事件

SHOW EVENTS;  # 其中 EVENT_DEFINITION字段为事件执行的sql语句
select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
DROP EVENT [IF EXISTS] event_name;   #删除事件

每天定时清理 只保留最近30天数据table_name表

CREATE EVENT IF NOT EXISTS table_name ON SCHEDULE EVERY 1 DAY 
STARTS concat(CURRENT_DATE,' 00:00:00') 
DO
delete from table_name where timestamp < DATE_FORMAT( date_sub(curdate(),interval 30 day)  ,'%Y-%m-%d');

三个表格设置不同的事件启动

CREATE EVENT IF NOT EXISTS repoissue_event ON SCHEDULE EVERY 1 DAY STARTS CONCAT(CURRENT_DATE, ' 22:20:00') DO DELETE FROM repoissue where timecheck < DATE_FORMAT(DATE_S