MySql 定时任务

-- 1、开启事件调度器
-- 确认是否开启
SHOW VARIABLES LIKE 'event_scheduler';
-- 开启命令
SET GLOBAL event_scheduler = ON;

-- 2、创建事件
-- 每30天(2592000S)清空30天前的所有记录，仅保留近30天数据
DROP EVENT IF EXISTS delete_log;
CREATE EVENT delete_log
ON SCHEDULE 
EVERY 2592000 SECOND
DO
DELETE FROM SysLogs WHERE CreateTime < DATE_SUB(CURDATE(),INTERVAL 30 DAY); 

-- 每天定时清空
DROP EVENT IF EXISTS truncate_log;
CREATE EVENT truncate_log
ON SCHEDULE
EVERY 1 DAY STARTS '2022-01-01 00:00:00'
DO
TRUNCATE TABLE Test_Study.SysLogs;

-- 指定时间将表清空
DROP EVENT IF EXISTS truncate_log_time;
CREATE EVENT truncate_log_time
ON SCHEDULE
AT '2022-07-08 00:00:00'
DO
INSERT INTO `Test_Study`.`SysLogs`(`Content`, `CreateTime`) VALUES ('sdxx', '2022-07-07 15:12:55');
-- TRUNCATE TABLE SysLogs;

-- 每60s清空2分钟前的所有记录，仅保留近30天数据
DROP EVENT IF EXISTS delete_2m_log;
CREATE EVENT delete_2m_log
ON SCHEDULE 
EVERY 60 SECOND
DO
DELETE FROM SysLogs WHERE CreateTime < DATE_SUB(NOW(),INTERVAL 2 MINUTE); 

-- 关闭事件
ALTER EVENT delete_2m_log DISABLE;
-- 开启事件
ALTER EVENT delete_2m_log ENABLE;
-- 删除事件
DROP EVENT IF EXISTS delete_2m_log;
-- 查看事件
SHOW EVENTS;

SELECT NOW(),CURDATE(),CURTIME();