MySQL事件调度器

MySQL事件调度器（Event Scheduler）类似于定时器，可以在某一个时间点执行一个SQL语句或一个语句块（BEGIN ... END）；或者每隔固定间隔重复执行。类似于Linux下的crontab，或Windows下的Task Scheduler。

事件调度器的配置

所有事件都是通过事件调度线程（event scheduler thread）执行；当提及事件调度器时，通常指该线程。

系统变量event_scheduler决定事件调度器是否启用。它有三个值：

• OFF：停止事件调度器。事件调度线程停止运行，同时也不会显示在SHOW PROCESSLIST的输出中。所有的调度事件也不会被执行。OFF是event_scheduler的默认值。

• ON：启动时间调度器。事件调度线程开始运行，并执行所有的调度事件。此时可以通过
  SHOW PROCESSLIST命令的输出中看到该线程及其状态。

• DISABLED：禁用事件调度器。不能在服务器运行时设置event_scheduler为DISABLED。

通过如果在服务器启动时设置了event_scheduler为DISABLED，也不能动态改变event_scheduler的值为ON或OFF。
event_scheduler的值也支持数字形式：1为ON；0为OFF。
要设置事件调度器为DISABLED，在服务器启动时，使用如下命令行选项：
--event-scheduler=DISABLED
或者在配置文件(/etc/my.cnf)的[mysqld]段中添加如下语句：
event_scheduler=DISABLED

开启事件调度线程
mysql> SET GLOBAL event_scheduler = ON | 1;

关闭事件调度线程
mysql> SET GLOBAL event_scheduler = OFF | 0;

查看事件调度状态方式一
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.02 sec)

查看事件调度状态方式二
mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 441
  State: Waiting on empty queue
   Info: NULL
2 rows in set (0.00 sec)

查看事件信息

• 查询mysql.event表
• 查询information_schema.events表
• 使用show create event语句
• 使用show events语句

1、查询mysql数据库的event表：
mysql> select * from mysql.event \G;
*************************** 1. row ***************************
                  db: test
                name: e_showtime
                body: select now() from dual
             definer: root@localhost
          execute_at: 2014-01-15 10:34:34
      interval_value: NULL
      interval_field: NULL
             created: 2014-01-15 17:34:34
            modified: 2014-01-15 17:34:34
       last_executed: NULL
              starts: NULL
                ends: NULL
              status: ENABLED
       on_completion: DROP
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: latin1
collation_connection: latin1_swedish_ci
        db_collation: latin1_swedish_ci
           body_utf8: select now() from dual
1 row in set (0.00 sec)

2、查询information_schema数据库的events表：
mysql> select * from information_schema.events \G;
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: test
          EVENT_NAME: e_showtime
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: select now() from dual
          EVENT_TYPE: ONE TIME
          EXECUTE_AT: 2014-01-15 18:34:34
      INTERVAL_VALUE: NULL
      INTERVAL_FIELD: NULL
            SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: NULL
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2014-01-15 17:34:34
        LAST_ALTERED: 2014-01-15 17:34:34
       LAST_EXECUTED: NULL
       EVENT_COMMENT:
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
  DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

3、使用show create event命令可以查看事件创建信息
mysql> show create event e_showtime \G;
*************************** 1. row ***************************
               Event: e_showtime
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `e_showtime` 
                           ON SCHEDULE AT '2014-01-15 18:34:34' ON COMPLETION NOT PRESERVE ENABLE
                             DO 
                                 select now() from dual
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

4、使用show events命令。其语法格式如下：
SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]
可以查看某一个数据库中的所有事件，或者符合某一pattern的事件。
mysql> show events \G;
*************************** 1. row ***************************
                  Db: test
                Name: e_showtime
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2014-01-15 18:34:34
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.02 sec)

每一个session又会有一个session time zone(STZ)
CREATE EVENT或ALTER EVENT语句定义事件任务指定的时间叫做event time zone(ETZ)

事件信息中时间表示的总结

Value	mysql.event	INFORMATION_SCHEMA.EVENTS	SHOW EVENTS
Execute at	UTC	ETZ	ETZ
Starts	UTC	ETZ	ETZ
Ends	UTC	ETZ	ETZ
Last executed	UTC	ETZ	n/a
Created	STZ	STZ	n/a
Last altered	STZ	STZ	n/a

事件调度器的权限

事件的创建、修改、删除需要event权限。通过grant语句可以将所有数据库的event权限赋予某一用户，如：

mysql> GRANT EVENT ON *.* TO user@hostname;

event权限属于数据库级别，因此不能将该权限用户表级别，否则报错：
mysql> GRANT EVENT ON myschema.mytable TO svoid@localhost;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the
 manual to see which privileges can be used

事件调度器在执行事件的语句块时，使用的是其定义者的权限。如果定义事件时使用了insert语句，但是该定义者并没有insert权限，则该事件执行时会报错，同时其insert失败。
查看information_schema.events表时会发现事件存在并且其状态为enabled，但其last_executed列为NULL

通过revoke语句类收回用户的event权限，如：

mysql> REVOKE EVENT ON *.* FROM user@hostname;

虽然收回了用户的event权限，但用户已经定义的事件并不会被删除或disable。删除或者重命名一个用户，那么该用户定义的事件不会自动删除,也不会更改其定义者为新用户名。

有5个状态变量可以查看与event相关的操作：

• Com_create_event：服务器启动后执行create event语句的次数
• Com_alter_event：服务器启动后执行alter event语句的次数
• Com_drop_event：服务器启动后执行drop event语句的次数
• Com_show_create_event：服务器启动后执行show create event语句的次数
• Com_show_events：服务器启动后执行show events语句的次数

mysql> SHOW STATUS LIKE '%event%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_alter_event          | 0     |
| Com_create_event         | 1     |
| Com_drop_event           | 0     |
| Com_show_binlog_events   | 0     |
| Com_show_create_event    | 2     |
| Com_show_events          | 1     |
| Com_show_relaylog_events | 0     |
+--------------------------+-------+
7 rows in set (0.00 sec)

事件语法

创建事件

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name                               --唯一标识，最大长度64个字符，不区分大小写
    ON SCHEDULE schedule                     --何时执行及执行频率
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;                           --执行的SQL语句

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}

ON SCHEDULE ：确定何时、多久、多长时间一次重复执行sql_statement定义的语句
AT timestamp ：用于一次性事件。指定在一个具体的时间执行一次，包括日期和时间（DATETIME or TIMESTAMP 类型的），timestamp 必须是将来的时间，不能用一个过去的时间。
如果用CURRENT_TIMESTAMP，则一创建就执行

创造一个事件,发生在将来的某个时间日期和时间，如“从现在开始3个星期后"，可以使用选项“+ INTERVAL interval”，interval 由数量和时间的单位2部分组成，间隔的距离用“DATE_ADD() ”函数来支配。

你也可以结合时间间隔。比如：“AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY ”，表示从现在起3个星期零2天，这选项的的每个部分必须以“+ INTERVAL”开始。

在一段时间间隔内将常性的重复的起作用。你可以使用一个“EVERY ”选项，“EVERY ”关键字，“+INTERVAL 关键字时不用every”, 比如“EVERY 6 WEEK”表示每6周，“every”关键字后+interval。

在一个简单的every字句中不能用every关键字和+ INTERVAL 关键字联合起来，比如你可以用EVERY '2:10' MINUTE_SECOND.表示“every two minutes and ten seconds”

EVERY 选项也可以包含STARTS选项,当一个动作开始重复时用STARTS timestamp ,也可以用"+ INTERVAL "关键字,interval 表示一个从现在开始的时间,比如: EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK
表示从现在开始一个星期后,每3个月,同样的,EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + '6:15'
HOUR_MINUTE表示从现在开始6:15后每2个星期,不指定STARTS则表示STARTS CURRENT_TIMESTAMP ,为时间指定的操作从现在开始执行

every和end两个选项.timestamp值后是ends,他告诉mysql停止执行.你也可以用ends+ INTERVAL interval ,
比如EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP +INTERVAL 4 WEEK 表示从现在起开始三十分钟后开始每十二个小时执行一次,从现在起四个星期后结束.
不用ends关键字表示事件持续到不确定什么时候结束地执行.在every子句中STARTS, ENDS你可以两个都用,也可以都不用.

通常,一旦一个event已经到期,它会立刻结束,你可以用 ON COMPLETION PRESERVE覆盖该事件,用ON COMPLETION NOT PRESERVE 仅仅明确使缺省行为不再持续执行.

COMMENT给一个事件一个注释,用任何64个字符来描述这个事件

DO子句由sql语句组成,你可以用任何有效的sql语句,存储程序,甚至是一个计划执行的event

当mysql存储的sql_mode在创建和更改事件时生效，不会后来sql_mode的值有没有变化事件都将会被执行。

do子句可以用begin和end来包含多个sql 语句,如:

DELIMITER |
CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day.'
    DO
      BEGIN
        INSERT INTO site_activity.totals (when, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |
DELIMITER ;

有的用在存储程序和event中的as,如:用一个本地变量,一个错误的handler,一个流控制的创造

DELIMITER |
CREATE EVENT e
    ON SCHEDULE
      EVERY 5 SECOND
    DO
      BEGIN
        DECLARE v INTEGER;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
        SET v = 0;
        WHILE v < 5 DO
          INSERT INTO t1 VALUES (0);
          UPDATE t2 SET s1 = s1 + 1;
          SET v = v + 1;
        END WHILE;
    END |
DELIMITER ;

没有方法直接用event传递参数,但是仍然可以用参数调用存储程序.如:

CREATE EVENT e_call_myproc
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + 1 DAY
    DO CALL myproc(5, 27);

如果event的定义者拥有SUPER权限,这个event即拥有读写全局变量的权限,因为授予这种特权会有潜在的危险,慎重之!

修改event

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]

与create event语句基本相同
DISABLE ON SLAVE表示事件是由主库创建并被从库复制，但从库不会执行。

mysql> ALTER EVENT myevent DISABLE;                                      --将事件置为无效
mysql> ALTER EVENT myevent ENABLE;                                       --将事件置为有效
mysql> ALTER EVENT oldevent RENAME TO newevent;                          --将事件修改事件名称

删除event

mysql> DROP EVENT [IF EXISTS] event_name    --删除定义的事件，需要EVENT权限

整理自网络

svoid

2014-09-22