MySQL 定时任务

查询定时任务是否开启

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;