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;