mysql之event

浏览数：18 / 时间：2015年06月12日

mysql之event

http://blog.csdn.net/lxgwm2008/article/details/9088521

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

 
一、事件调度器的配置
所有事件都是在事件调度线程（event scheduler thread）里面执行的；当提及事件调度器时，通常指该线程。如果用户有PROCESS权限，可以通过SHOW PROCESSLIST命令来查看该线程及其状态：
[sql] view plaincopy
 
  1. mysql> show processlist;  
  2. +-----+-----------------+-----------+------+---------+------+-----------------------------------+------------------+  
  3. | Id  | User            | Host      | db   | Command | Time | State                             | Info             |  
  4. +-----+-----------------+-----------+------+---------+------+-----------------------------------+------------------+  
  5. |   1 | system user     |           |      | Daemon  |    0 | Waiting for event from ndbcluster | NULL             |  
  6. |   3 | root            | localhost | test | Query   |    0 | NULL                              | show processlist |  
  7. | 203 | event_scheduler | localhost | NULL | Daemon  |    2 | Waiting for next activation       | NULL             |  
  8. +-----+-----------------+-----------+------+---------+------+-----------------------------------+------------------+  
  9. 3 rows in set (0.00 sec)  
系统变量event_scheduler决定事件调度器是否启用。它有三个值：
OFF：停止事件调度器。事件调度线程停止运行，同时也不会显示在SHOW PROCESSLIST的输出中。所有的调度时间也不会被执行。OFF是event_scheduler的默认值。
当事件调度器停止后，可以通过设置event_scheduler为ON来启动它。
ON：启动时间调度器。事件调度线程开始运行，并执行所有的调度事件。此时可以通过SHOW PROCESSLIST命令的输出中看到该线程及其状态。
通过设置event_scheduler为OFF可以停止该线程。
DISABLED：禁用事件调度器。SHOW PROCESSLIST命令的输出中看不到该线程。也不能通过设置event_scheduler为ON来启动该线程。如果要设置事件调度器为DISABLED，唯一的方法是在服务器启动时，使用如下选项：
--event-scheduler=DISABLED
或者在配置文件(/etc/my.cnf)的[mysqld]段中添加如下语句：
event_scheduler=DISABLED
 
不能在服务器运行时设置event_scheduler为DISABLED。通过如果在服务器启动时设置了event_scheduler为DISABLED，也不能动态改变event_scheduler的值为ON或OFF。
 
event_scheduler的值也支持数字形式：1为ON；0为OFF。如下四条语句均可启动事件调度线程：
[sql] view plaincopy
 
  1. set global event_scheduler = on  
  2. set @@global.event_scheduler = on  
  3. set global event_scheduler = 1  
  4. set @@global.event_scheduler = 1  
 
同理，如下四条语句也可以停止时间调度线程：
[sql] view plaincopy
 
  1. SET GLOBAL event_scheduler = OFF  
  2. SET @@global.event_scheduler = OFF  
  3. SET GLOBAL event_scheduler = 0  
  4. SET @@global.event_scheduler = 0  
设置时必须使用global关键字，否则会出错：
[sql] view plaincopy
 
  1. mysql< SET @@event_scheduler = OFF;  
  2. ERROR 1229 (HY000): Variable ‘event_scheduler‘ is a GLOBAL variable and should be set with SET GLOBAL  
 
启动服务器时如果指定了--skip-grant-tables选项，则event_scheduler则自动被设置为DISABLED。命令行或配置文件的设置都会被覆盖。
 
二、事件信息
所有定义的时间可以通过如下方法查看：
1、查询mysql数据库的event表：
[sql] view plaincopy
 
  1. mysql> select * from mysql.event \G  
  2. *************************** 1. row ***************************  
  3.                   db: test  
  4.                 name: evt_insert  
  5.                 body: insert into account values(NULL, 100.00)  
  6.              definer: root@localhost  
  7.           execute_at: NULL  
  8.       interval_value: 10  
  9.       interval_field: SECOND  
  10.              created: 2013-06-13 14:07:54  
  11.             modified: 2013-06-13 14:07:54  
  12.        last_executed: 2013-06-13 19:29:44  
  13.               starts: 2013-06-13 18:07:54  
  14.                 ends: NULL  
  15.               status: ENABLED  
  16.        on_completion: DROP  
  17.             sql_mode:   
  18.              comment:   
  19.           originator: 1  
  20.            time_zone: SYSTEM  
  21. character_set_client: utf8  
  22. collation_connection: utf8_general_ci  
  23.         db_collation: utf8_general_ci  
  24.            body_utf8: insert into account values(NULL, 100.00)  
  25. 1 row in set (0.01 sec)  
2、查询information_schema数据库的events表：
[sql] view plaincopy
 
  1. mysql> select * from information_schema.events \G  
  2. *************************** 1. row ***************************  
  3.        EVENT_CATALOG: def  
  4.         EVENT_SCHEMA: test  
  5.           EVENT_NAME: evt_insert  
  6.              DEFINER: root@localhost  
  7.            TIME_ZONE: SYSTEM  
  8.           EVENT_BODY: SQL  
  9.     EVENT_DEFINITION: insert into account values(NULL, 100.00)  
  10.           EVENT_TYPE: RECURRING  
  11.           EXECUTE_AT: NULL  
  12.       INTERVAL_VALUE: 10  
  13.       INTERVAL_FIELD: SECOND  
  14.             SQL_MODE:   
  15.               STARTS: 2013-06-13 14:07:54  
  16.                 ENDS: NULL  
  17.               STATUS: ENABLED  
  18.        ON_COMPLETION: NOT PRESERVE  
  19.              CREATED: 2013-06-13 14:07:54  
  20.         LAST_ALTERED: 2013-06-13 14:07:54  
  21.        LAST_EXECUTED: 2013-06-13 15:30:14  
  22.        EVENT_COMMENT:   
  23.           ORIGINATOR: 1  
  24. CHARACTER_SET_CLIENT: utf8  
  25. COLLATION_CONNECTION: utf8_general_ci  
  26.   DATABASE_COLLATION: utf8_general_ci  
  27. 1 row in set (0.01 sec)  
3、使用show create event命令可以查看事件创建信息
[sql] view plaincopy
 
  1. mysql> show create event evt_insert \G  
  2. *************************** 1. row ***************************  
  3.                Event: evt_insert  
  4.             sql_mode:   
  5.            time_zone: SYSTEM  
  6.         Create Event: CREATE DEFINER=`root`@`localhost` EVENT `evt_insert` ON SCHEDULE EVERY 10 SECOND STARTS ‘2013-06-13 14:07:54‘ ON COMPLETION NOT PRESERVE ENABLE DO insert into account values(NULL, 100.00)  
  7. character_set_client: utf8  
  8. collation_connection: utf8_general_ci  
  9.   Database Collation: utf8_general_ci  
  10. 1 row in set (0.00 sec)  



4、使用show events命令。其语法格式如下：
 
SHOW EVENTS [{FROM | IN} schema_name] [LIKE ‘pattern‘ | WHERE expr]
可以查看某一个数据库中的所有事件，或者符合某一pattern的事件。
[sql] view plaincopy
 
  1. mysql> show events \G  
  2. *************************** 1. row ***************************  
  3.                   Db: test  
  4.                 Name: evt_insert  
  5.              Definer: root@localhost  
  6.            Time zone: SYSTEM  
  7.                 Type: RECURRING  
  8.           Execute at: NULL  
  9.       Interval value: 10  
  10.       Interval field: SECOND  
  11.               Starts: 2013-06-13 14:07:54  
  12.                 Ends: NULL  
  13.               Status: ENABLED  
  14.           Originator: 1  
  15. character_set_client: utf8  
  16. collation_connection: utf8_general_ci  
  17.   Database Collation: utf8_general_ci  
  18. 1 row in set (0.00 sec)  

 

三、事件调度器的权限
事件的创建、修改、删除需要event权限。通过grant语句可以将某一个数据库的event权限赋予某一用户，如：
GRANT EVENT ON myschema.* TO jon@ghidora;
如果要赋予用户所有数据库的event权限，可以：
GRANT EVENT ON *.* TO jon@ghidora;
event权限属于数据库级别，因此不能将该权限用户表级别，否则报错：
mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
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
[sql] view plaincopy
 
  1. mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME=‘e_store_ts‘ AND EVENT_SCHEMA=‘myschema‘\G  
  2. *************************** 1. row ***************************  
  3. EVENT_CATALOG: NULL  
  4. EVENT_SCHEMA: myschema  
  5. EVENT_NAME: e_store_ts  
  6. DEFINER: jon@ghidora  
  7. EVENT_BODY: SQL  
  8. EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())  
  9. EVENT_TYPE: RECURRING  
  10. EXECUTE_AT: NULL  
  11. INTERVAL_VALUE: 5  
  12. INTERVAL_FIELD: SECOND  
  13. SQL_MODE: NULL  
  14. STARTS: 0000-00-00 00:00:00  
  15. ENDS: 0000-00-00 00:00:00  
  16. STATUS: ENABLED  
  17. ON_COMPLETION: NOT PRESERVE  
  18. CREATED: 2006-02-09 22:36:06  
  19. LAST_ALTERED: 2006-02-09 22:36:06  
  20. LAST_EXECUTED: NULL  
  21. EVENT_COMMENT:  
  22. 1 row in set (0.00 sec)  

 

通过revoke语句类收回用户的event权限，如：
REVOKE EVENT ON myschema.* FROM jon@ghidora;
 
虽然收回了用户的event权限，但用户已经定义的事件并不会被删除或disable。通过如果删除一个用户，或者重命名一个用户，那么该用户定义的时间也不会自动删除，或者更改其定义者为新用户名。
 
用户的event权限存储在mysql.user和mysql.db两张表的event_priv字段中。event_priv字段的值为Y或N。N为默认值。只有当用户具有全局event权限（即grant event on *.*）时，mysql.user.event_priv字段值才为Y。对于数据库级别的event权限，grant语句会在mysql.db表中增加一条记录，并将其event_priv字段设为Y。用户不需要自己手动修改者两张表，因为grant和revoke语句会执行相同的操作。
 
有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语句的次数
 
可以通过如下语句来查看与event相关的所有状态变量：
[sql] view plaincopy
 
  1. SHOW STATUS LIKE ‘%event%‘;  
  2. mysql> show status like ‘%event%‘;  
  3. +--------------------------------------+-------+  
  4. | Variable_name                        | Value |  
  5. +--------------------------------------+-------+  
  6. | Com_alter_event                      | 0     |  
  7. | Com_create_event                     | 1     |  
  8. | Com_drop_event                       | 0     |  
  9. | Com_show_binlog_events               | 0     |  
  10. | Com_show_create_event                | 2     |  
  11. | Com_show_events                      | 3     |  
  12. | Com_show_relaylog_events             | 0     |  
  13. | Ndb_api_event_data_count_injector    | 1     |  
  14. | Ndb_api_event_nondata_count_injector | 0     |  
  15. | Ndb_api_event_bytes_count_injector   | 204   |  
  16. | Ndb_api_event_data_count             | 1     |  
  17. | Ndb_api_event_nondata_count          | 0     |  
  18. | Ndb_api_event_bytes_count            | 204   |  
  19. +--------------------------------------+-------+  
  20. 13 rows in set (0.00 sec)  

 

四、语法
1、create event语法
[sql] view plaincopy
 
  1. CREATE  
  2.   
  3.     [DEFINER = { user | CURRENT_USER }]  
  4.     EVENT  
  5.     [IF NOT EXISTS]  
  6.     event_name  
  7.     ON SCHEDULE schedule  
  8.     [ON COMPLETION [NOT] PRESERVE]  
  9.     [ENABLE | DISABLE | DISABLE ON SLAVE]  
  10.     [COMMENT ‘comment‘]  
  11.     DO event_body;  
  12.   
  13. schedule:  
  14.   
  15.     AT timestamp [+ INTERVAL interval] ...  
  16.     | EVERY interval  
  17.     [STARTS timestamp [+ INTERVAL interval] ...]  
  18.     [ENDS timestamp [+ INTERVAL interval] ...]  
  19.   
  20. interval:  
  21.   
  22.     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只需要三个部分：
1. create event关键字以及一个event名称
2. on schedule子句
3. do子句
例如：
[sql] view plaincopy
 
  1. create event evt_insert   
  2.       on schedule every 10 second  
  3.       do update myschema.mytable set mycol = mycol + 1;  
对于语句的详细解释：
definer：说明该event的用户。服务器在执行该事件时，使用该用户来检查权限。默认用户为当前用户，即definer = current_user。如果明确指明了definer，则必须遵循如下规则：
1.如果没有super权限，唯一允许的值就是自己当前用户，而不能设置为其他用户。
2.如果具有super权限，则可以指定任意存在的用户。如果指定的用户不存在，则事件在执行时会报错。
 
if not exists：如果事件已经存在，则不会创建，也不会报错
 
on schedule子句指定何时执行该事件，以及如何执行该事件
1. at timestamp用于创建单次执行的事件。timestamp执行了事件执行的时间，必须包括日期和事件两部分，或者是一个datetime类型的表达式，如current_timestamp。如果指定的时间是过去的时间，则会产生一个warning。
[sql] view plaincopy
 
  1. mysql> SELECT NOW();  
  2. +---------------------+  
  3. | NOW() |  
  4. +---------------------+  
  5. | 2006-02-10 23:59:01 |  
  6. +---------------------+  
  7. 1 row in set (0.04 sec)  
  8. mysql > CREATE EVENT e_totals  
  9.   
  10.      ->    ON SCHEDULE AT ‘2006-02-10 23:59:00‘  
  11.      ->    DO INSERT INTO test.totals VALUES (NOW());  
  12.   
  13. Query OK, 0 rows affected, 1 warning (0.00 sec)  
  14. mysql > SHOW WARNINGS\G  
  15. *************************** 1. row ***************************  
  16. Level: Note  
  17. Code: 1588  
  18. Message: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.  
 
如果要指定将来某个时间间隔，如从现在开始的两个半小时之后，则可以使用interval关键字: + INTERVAL ‘2:30‘ HOUR_MINUTE。interval关键字可以进行组合，如上例可以这样：+ INTERVAL 2 HOUR + INTERVAL 30 MINUTE。
 
2. every子句用于创建重复执行的事件。如果每分钟执行一次，则可以：EVERY 1 MINUTE。every子句可以指定一个开始事件和结束时间，通过STARTS和ENDS关键字来表示，具体语法与前面类似。例如：
EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK。
 
通常情况下，如果一个事件过期已过期，则会被立即删除。通过on completion preserve子句可以保留已过期的时间。ON COMPLETION NOT PRESERVE为默认值。
 
默认情况下，事件一旦创建后就立即开始执行。可以通过disable关键字来禁用该事件。
 
comment子句用于给事件添加注释。
 
do子句用于指示事件需要执行的操作，可以是一条语句，也可以是被begin...end包括的语句块。如果语句块中包含sql语句，则可能需要修改delimiter。语句块中可以包含任意的控制结构，如：
[sql] view plaincopy
 
  1. delimiter //  
  2. CREATE EVENT e  
  3. ON SCHEDULE  
  4.     EVERY 5 SECOND  
  5. DO  
  6. BEGIN  
  7.     DECLARE v INTEGER;  
  8.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;  
  9.     SET v = 0;  
  10.     WHILE v < 5 DO  
  11.         INSERT INTO t1 VALUES (0);      
  12.         UPDATE t2 SET s1 = s1 + 1;  
  13.         SET v = v + 1;  
  14.     END WHILE;  
  15. END //  
  16. delimiter ;  


也可以在语句块中调用存储过程，如：
[sql] view plaincopy
 
  1. CREATE EVENT e_call_myproc  
  2.     ON SCHEDULE  
  3.     AT CURRENT_TIMESTAMP + INTERVAL 1 DAY  
  4.     DO CALL myproc(5, 27);  
 
2、alter event语法
[sql] view plaincopy
 
  1. ALTER  
  2.     [DEFINER = { user | CURRENT_USER }]  
  3.     EVENT event_name  
  4.     [ON SCHEDULE schedule]  
  5.     [ON COMPLETION [NOT] PRESERVE]  
  6.     [RENAME TO new_event_name]  
  7.     [ENABLE | DISABLE | DISABLE ON SLAVE]  
  8.     [COMMENT ‘comment‘]  
  9.     [DO event_body]  
 
alter event语句可以修改事件的一个或多个属性，DEFINER/ON SCHEDULE/ON COMPLETION PRESERVER/ENABLE/COMMENT/DO子句的语法与create event语句完全相同，唯一不同的是可以对事件重命名，使用RENAME TO子句，如：
[sql] view plaincopy
 
  1. ALTER EVENT OLDDB.MYEVENT RENAME TO NEWDB.MYEVENT;  
3、drop event语法
[sql] view plaincopy
 
  1. DROP EVENT [IF EXISTS] event_name  
该语句删除一个定义的事件

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： ie class text ip string io ff word blog t value ie class text ip string io ff word blog t value

相关文章

随机文章

您可能还喜欢

您可能还喜欢