查询定时任务是否开启
show variables like 'event_scheduler';
Variable_name
Value
event_scheduler
OFF
OFF 为关闭，ON 为开启
开启定时任务
set global event_scheduler = on;
创建定时任务
CREATE [DEFINER = { user | CURRENT_USER }] EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR |
DAY_MINUTE |DAY_SECOND | HOUR_MINUTE |
HOUR_SECOND | MINUTE_SECOND}
DEFINER: 执行事件时检查用户是否有权限
ON SCHEDULE schedule： 执行的时间或间隔
ON COMPLETION [NOT] PRESERVE：执行一次或永久执行
ENABLE | DISABLE | DISABLE ON SLAVE：事件定义后是否启用
COMMENT 'comment'：事件的注释
每1秒：
on schedule every 3 second
10分钟后：
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
指定时刻：
ON SCHEDULE AT TIMESTAMP '2020-01-11 01:01:00'
7天后，每1秒执行一次，1个月后停止：
ON SCHEDULE EVERY 1 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 7day
ENDS CURRENT_TIMESTAMP + INTERVAL 1 month
关闭事件：
alter event event_name disable;