MySQL事件调度器

mysql事件调度器

事件调度器(event_scheduler):可以定时执行某些特定任务,可以看做基于时间的触发器.

事件开关设置

SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SET GLOBAL event_scheduler=1;
SHOW CREATE EVENT insert_tdemo;
SHOW EVENTS;

事件的创建

CREATE EVENT [IF NOT EXISTS] event_name
 ON SCHEDULE schedule
 [ON COMPLETION [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 
 

例子

CREATE TABLE tdemo(
	timeline TIMESTAMP
);

-- 定期间隔执行某件事情 每隔一秒插入一条数据
DROP EVENT IF EXISTS e_insert_tdemo;
CREATE EVENT IF NOT EXISTS e_insert_tdemo
ON SCHEDULE EVERY 1 SECOND
DO
INSERT tdemo VALUES (CURRENT_TIMESTAMP);

-- 一分钟后清空数据表tdemo中的所有数据
DROP EVENT IF EXISTS e_truncate_tdemo;
CREATE EVENT IF NOT EXISTS e_truncate_tdemo
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
TRUNCATE TABLE tdemo;

-- 定时执行在2018-02-01 16:40:00 执行
CREATE EVENT e_drop_tdemo
ON SCHEDULE AT TIMESTAMP '2018-02-01 16:40:00'
DO
DROP TABLE tdemo;


-- 定时定期任务 xx时间后,每隔一段时间执行一次 (5秒之后,每3秒定时清空tdemo表)
CREATE EVENT e_clear_after_interval
ON SCHEDULE EVERY 3 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 SECOND
DO
TRUNCATE TABLE tdemo;


-- 间隔执行定时终止 每隔一秒钟执行一次,10秒后终止执行
DROP EVENT IF EXISTS e_clear_interval_end;
CREATE EVENT e_clear_interval_end
ON SCHEDULE EVERY 1 SECOND
ENDS CURRENT_TIMESTAMP + INTERVAL 10 SECOND
DO
TRUNCATE TABLE tdemo;

-- 定时开启间隔执行定时结束 --5秒开启,每秒执行,10秒钟停止执行
CREATE EVENT e_clear_interval_end
ON SCHEDULE EVERY 1 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 SECOND
ENDS CURRENT_TIMESTAMP + INTERVAL 15 SECOND
DO
INSERT tdemo VALUES (CURRENT_TIMESTAMP);

-- 每天定时1点执行
CREATE EVENT e_clear_interval_end
ON SCHEDULE EVERY 1 DAY 
STARTS '2010-12-18 01:00:00'
DO
INSERT tdemo VALUES (CURRENT_TIMESTAMP);

修改

ALTER EVENT event_name
 [ON SCHEDULE schedule]
 [RENAME TO new_event_name]
 [ON COMPLETION [NOT] PRESERVE]
 [COMMENT 'comment']
 [ENABLE | DISABLE] [DO sql_statement]
 

ALTER EVENT e_test DISABLE;