查看当前是否已开启事件调度器,可执行如下SQL:
SHOW VARIABLES LIKE 'event_scheduler';
或SELECT @@event_scheduler;
或SHOW PROCESSLIST;
开启event_scheduler
SET GLOBAL event_scheduler = 1;
或SET GLOBAL event_scheduler = ON;
创建事件(CREATE EVENT)
CREATE EVENT [IFNOT EXISTS] event_name
ONSCHEDULE schedule
[ONCOMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
eg:
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES(CURRENT_TIMESTAMP);
5天后开启每天定时清空test表,一个月后停止执行:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP+ INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP+ INTERVAL 1 MONTH
DO TRUNCATETABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行,默认为NOT PRESERVE。
每天定时清空test表(只执行一次,任务完成后就终止该事件):
CREATE EVENT e_test
ONSCHEDULE EVERY 1 DAY
ONCOMPLETION NOT PRESERVE
DO TRUNCATETABLE test.aaa;
修改事件(ALTER EVENT)
ALTER EVENT event_name
[ONSCHEDULE schedule]
[RENAME TOnew_event_name]
[ONCOMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
临时关闭事件 ALTER EVENT e_test DISABLE;
开启事件 ALTER EVENT e_test ENABLE;
删除事件(DROP EVENT) DROP EVENT [IF EXISTS] event_name
SHOW VARIABLES LIKE 'event_scheduler';
或SELECT @@event_scheduler;
或SHOW PROCESSLIST;
开启event_scheduler
SET GLOBAL event_scheduler = 1;
或SET GLOBAL event_scheduler = ON;
创建事件(CREATE EVENT)
CREATE EVENT [IFNOT EXISTS] event_name
ONSCHEDULE schedule
[ONCOMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
eg:
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES(CURRENT_TIMESTAMP);
5天后开启每天定时清空test表,一个月后停止执行:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP+ INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP+ INTERVAL 1 MONTH
DO TRUNCATETABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行,默认为NOT PRESERVE。
每天定时清空test表(只执行一次,任务完成后就终止该事件):
CREATE EVENT e_test
ONSCHEDULE EVERY 1 DAY
ONCOMPLETION NOT PRESERVE
DO TRUNCATETABLE test.aaa;
修改事件(ALTER EVENT)
ALTER EVENT event_name
[ONSCHEDULE schedule]
[RENAME TOnew_event_name]
[ONCOMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
临时关闭事件 ALTER EVENT e_test DISABLE;
开启事件 ALTER EVENT e_test ENABLE;
删除事件(DROP EVENT) DROP EVENT [IF EXISTS] event_name