查詢定時任務是否開啟
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;