查看定时任务开关是否开启;
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 ;