查询mysql定时任务_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;