mysql中event(定时触发)的使用
1、从MySQL5.1.6版本开始增加事件调度器(Event Scheduler),在使用这个功能之前必须确保event_scheduler已开启
1.1、开启event_scheduler,可使用以下方法
SET GLOBAL event_scheduler = 1; SET GLOBAL event_scheduler = ON; 在配置my.cnf文件 中加上 event_scheduler = 1
1.2、查看是否已开启事件调度器,可执行如下SQL:
SHOW VARIABLES LIKE 'event_scheduler'; SELECT @@event_scheduler; SHOW PROCESSLIST; SELECT * from mysql.event; SELECT * from information_schema.events;
2、CREATE EVENT 语法如下:
CREATE EVENT
[IF NOT EXISTS] --------------------------------------*语法1
event_name --------------------------------------------*语法2
ON SCHEDULE schedule ------------------------------*语法3
[ON COMPLETION [NOT] PRESERVE] --------------*语法4
[ENABLE | DISABLE] ----------------------------------*语法5
[COMMENT 'comment'] -------------------------------*语法6
DO sql_statement --------------------------------------*语法7
说明如下:
语法1、[IF NOT EXISTS]
使用IF NOT EXISTS,当event名称存在时不创建,只有名称不存在时才创建,建议不使用以保证event创建成功。
语法2、event_name
event名称,最大长度为64个字节且唯一。
语法3、ON SCHEDULE 计划任务,有两种设定计划任务的方式:
方式1:AT 时间戳,用来完成单次的计划任务;
时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型,时间戳需要大于当前时间。
方式2: EVERY 时间(单位)的数量时间单位[STARTS 时间戳] [ENDS时间戳],用来完成重复的计划任务;
时间(单位)的数量可以是任意非空(Not Null)的整数式,时间单位是关键词:YEAR,MONTH,DAY,HOUR,MINUTE 或者SECOND
注: 其他的时间单位也是合法的如:QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND,不建议使用这些不标准的时间单位。
语法4、[ON COMPLETION [NOT] PRESERVE]
ON COMPLETION参数表示"当这个事件不会再发生的时候",即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。
而PRESERVE的作用是使事件在执行完毕后不会被Drop掉,建议使用该参数,以便于查看EVENT具体信息。
语法5、[ENABLE | DISABLE]:参数Enable和Disable表示设定事件的状态(Enable:执行这个事件、Disable:不执行该事件)
可以用如下命令关闭或开启事件:ALTER EVENT event_name ENABLE/DISABLE
语法6、[COMMENT 'comment']
注释会出现在元数据中,它存储在information_schema表的COMMENT列,最大长度为64个字节。'comment'表示将注释内容放在单引号之间,建议使用注释以表达更全面的信息。
语法7、 DO sql_statement
DO sql_statement字段表示该event需要执行的SQL语句或存储过程。这里的SQL语句可以是复合语句,例如:
BEGIN
CREATE TABLE tab_name;//创建表
DROP TABLE tab_name;//删除表
CALL proc_name();//调用存储过程
END
使用BEGIN和END标识符将复合SQL语句按照执行顺序放在之间。当然SQL语句是有限制的,对它的限制跟函数Function和触发器Trigger 中对SQL语句的限制是一样的,
如果你在函数Function 和触发器Trigger 中不能使用某些SQL,同样的在EVENT中也不能使用。明确的来说有下面几个:
LOCK TABLES
UNLOCK TABLES
CREATE EVENT
ALTER EVENT
LOAD DATA
3、 执行逻辑
For (已建立事件each event that has been created)
If (事件的状态非DISABLE)
And (当前时间在ENDS时间之前)
And (当前时间在STARTS时间之后)
And (在上次执行后经过的时间)
And (没有被执行)
Then:
建立一个新的线程
传递事件的SQL语句给新的线程
(该线程在执行完毕后会自动关闭)
4、修改事件
使用ALTER EVENT 来修改事件,具体的ALTER语法如下,与创建事件的语法类似:
ALTER EVENT
event_name
ON SCHEDULE schedule
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement
5、 删除事件
EVENT使用DROP EVENT语句来删除已经创建的事件,语法如下:
DROP EVENT [IF EXISTS] event_name
6、 实例
-- 创建存储过程 DROP PROCEDURE prc_delete_sys_menu_name; CREATE PROCEDURE prc_delete_sys_menu_name() BEGIN delete from sys_menu_name; END; -- 测试 CALL prc_delete_sys_menu_name(); -- 创建Event -- 每隔10秒执行 DROP EVENT IF EXISTS eve_delete_sys_menu_name; CREATE EVENT IF NOT EXISTS eve_delete_sys_menu_name ON SCHEDULE EVERY 10 SECOND ON COMPLETION PRESERVE DO CALL prc_delete_sys_menu_name(); -- 设置开启event_scheduler SET GLOBAL event_scheduler = ON; -- 查看是否开启event_scheduler SHOW VARIABLES LIKE 'event_scheduler'; SELECT * from mysql.event;