介绍了MySQL中事件调度器的相关使用
事件调度器(Event Schedule)类似于Linux中的crontab(也就是定时任务),下面介绍事件调度器的基本使用方法
# 查看状态
show variables like '%event_scheduler%';
+---------------+-----+
|Variable_name |Value|
+---------------+-----+
|event_scheduler|ON |
+---------------+-----+
# 开启
SET GLOBAL event_scheduler = 1;
# 关闭
SET GLOBAL event_scheduler = 0;
注意:
如果是设定事件计划为0 或OFF,即关闭事件计划进程的时候,不会有新的事件执行,但现有的正在运行的事件会执行到完毕。
对于线上环境来说,使用even时,注意在主库上开启定时器,从库上关闭定时器,event触发所有操作均会记录binlog进行主从同步,从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。
创建语句
CREATE
[DEFINER = user]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string']
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}
create event
定义中通过on completion preserve
子句可以保留已过期的时间。默认:ON COMPLETION NOT PRESERVE,也就是不保存示例,创建一个每10秒插入一条数据的任务
CREATE EVENT sc1
ON SCHEDULE EVERY 10 SECOND
DO
BEGIN
INSERT INTO scheduler_test1(time) VALUE (now());
END;
事件查看
# 查看所有事件
SELECT * FROM information_schema.EVENTS;
SHOW EVENTS ;
修改
ALTER
[DEFINER = user]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string']
[DO event_body]
更多的详情可参考13.1.2 ALTER EVENT Statement
删除
DROP EVENT [IF EXISTS] event_name;