MySQL存储过程，定时清理数据

1、创建存储过程：

CREATE DEFINER=`ocsadmin`@`%` PROCEDURE `px_del_logs`()
    COMMENT '删除日志'
BEGIN
    DELETE FROM xxl_job.xxl_job_log WHERE (TO_DAYS(NOW()) - TO_DAYS(trigger_time))>=3;
END

2、创建事件调度器：

CREATE EVENT event_px_del_logs
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
DO
   CALL px_del_logs();

每天凌晨2点开始执行

create event event_px_del_logs on schedule  EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 2 HOUR)

ON COMPLETION PRESERVE ENABLE  do call px_del_logs()

3、删除事件调度器

DROP EVENT px_del_logs;

4、查看事件调度器

SELECT * FROM INFORMATION_SCHEMA.EVENTS; 
或
show events