定时清理mysql表数据

查看定时任务开关是否开启;

SHOW VARIABLES LIKE 'event_scheduler'

如果是off需要打开;

SET GLOBAL event_scheduler = ON

测试表:

CREATE TABLE `xc_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

写定时任务的事件:

DELIMITER $$
DROP EVENT
IF EXISTS deleteXcLog ; CREATE EVENT deleteXcLog ON SCHEDULE EVERY 30 SECOND ON COMPLETION PRESERVE DO

BEGIN
    INSERT INTO xc_log (create_time) VALUES    (now()) ; 
    UPDATE xc_log SET update_time = now(), is_delete = 1 WHERE TO_DAYS(now()) - TO_DAYS(create_time) > 90 AND is_delete = 0 ; 
END$$

DELIMITER ;

 

查看事件

SHOW EVENTS;

可以使用`LAST_EXECUTED`和`STATUS`列来查看事件的上一次执行时间和当前状态

select * from information_schema.EVENTS