这篇文章主要介绍了MySQL中的事件调度基础学习教程,本文介绍了对Event Scheduler的一些基本操作方法,需要的朋友可以参考下 经常需要有一些定时任务在MySQL表上执行，例如统计、迁移、删

这篇文章主要介绍了MySQL中的事件调度基础学习教程,本文介绍了对Event Scheduler的一些基本操作方法,需要的朋友可以参考下

MySQL中的事件调度基础学习教程（mysql 事件调度器） MySQL 事件调度 第1张

经常需要有一些定时任务在MySQL表上执行，例如统计、迁移、删除无用数据等。之前的作法是利用Linux cron定时运行脚本，但是发现这样的额外依赖有时并不方便，例如单机多实例部署时，就需要分别手动分别配置不同的cron任务，需要额外配置相应的用户和权限;新环境部署时容易遗漏cron任务等。

MySQL提供了Event Scheduler，与Linux下的crontab类似，可以根据时间调度来运行任务，运行一次或多次。

完整的Event Schduler创建语句如下：

 

  1. CREATE 
  2. [DEFINER = { user | CURRENT_USER }] 
  3. EVENT 
  4. [IF NOT EXISTS] 
  5. event_name 
  6. ON SCHEDULE schedule 
  7. [ON COMPLETION [NOT] PRESERVE] 
  8. [ENABLE | DISABLE | DISABLE ON SLAVE] 
  9. [COMMENT 'comment'] 
  10. DO event_body; 
  11.  
  12. schedule: 
  13. AT timestamp [+ INTERVAL interval] … 
  14. | EVERY interval 
  15. [STARTS timestamp [+ INTERVAL interval] …] 
  16. [ENDS timestamp [+ INTERVAL interval] …] 
  17.  
  18. interval: 
  19. quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
  20. WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | 
  21. DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} 

一、调度Scheduler

MySQL中的调度可以是只运行一次，也可以指定时间间隔重复运行。其定义是在event定义的ON SCHEDULE子句中。该子句格式如下：

 

 
  1. ON SCHEDULE 
  2. AT timestamp [+ INTERVAL interval] … 
  3. | EVERY interval 
  4. [STARTS timestamp [+ INTERVAL interval] …] 
  5. [ENDS timestamp [+ INTERVAL interval] …] 

其中，timestamp必须包括”年月日时分秒“，它参与表达式计算后，结果是datetime或者timestamp类型。

而时间间隔interval可以如下：

 

 
  1. <数字> {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
  2. WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | 
  3. DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} 

其含义很清晰，如YEAR 年;QUARTER 季度;YEAR_MONTH 年+月;MINUTE_SECOND 分钟+秒。

补充：

YEAR | QUARTER | MONTH | YEAR_MONTH 后台都转换成MONTH，其他时间间隔都转换成SECOND

ON SCHEDULE中的时间使用创建时本会话中的时区信息time_zone，这个时区默认是服务端的全局time_zone，也可能后续手动更新掉。这些时间会转化成UTC时间，存储到MySQL.event表中。

1.一次运行

AT直接指定时间，或者使用时间表达式计算得出确定的时间点。

示例：

AT '2006-02-10 23:59:00′ 指定确切运行时间，本地时区。

AT current_timestamp + INTERVAL '1:15′ MINUTE_SECOND 指定1分15秒后运行。

2.多次运行

EVERY设置运行的时间间隔，这里不能再指定[+ INTERVAL interval]。

指定STARTS、ENDS是可选的。

STARTS是指定重复运行的第一次是什么时候。不指定的情况下，会在事件创建时运行第一次，即等价于STARTS CURRENT_TIMESTAMP!

ENDS告知MySQL结束重复运行的时间点。不指定的情况下，MySQL会永远重复运行下去。

示例：

EVERY 5 WEEK 每5周运行一次，创建时运行第一次。

EVERY 3 DAY STARTS '2013-12-4 09:10:00′ 从'2013-12-4 09:10:00′开始运行第一次，每隔3天运行一次。

EVERY 2 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE ENDS '2014-12-31 23:59:59′ 10分钟后开始到2014年底，每两个月运行一次。

二、事件Event 1.启用Event Scheduler功能

Event是由一个特定的Event Scheduler线程执行的，运行过程中可以通过show full processlist查看其当前状态信息，如：

7384313 event_scheduler localhost [NULL] Daemon 3 Waiting on empty queue [NULL]

默认事件调度Event Scheduler功能是未启用的，需要配置全局参数event_scheduler，本参数可以动态设置，即时生效。

event_scheduler有如下三种取值：

OFF/0 关闭，默认值。不运行Event Scheduler线程，也就无法进行事件调度。设置为ON可以立即启用。

ON/1 启用。

DISABLED 禁用。同样不运行Event Scheduler线程。只有在MySQL服务启动时设置才有用。当event_scheduler是ON或者OFF时，不能在运行时设置event_scheduler为DISABLED。如果启动时配置了event-scheduler=DISABLED，则运行时就不能设置为ON/OFF。换句话中，可以在MySQL服务启动时设置为DISABLED，然后完全禁用了event_scheduler，不能动态调整。

所以，要启用event_scheduler，运行时执行：

 

 
  1. set global event_scheduler=on 

要随MySQL服务一起启用，则在/etc/my.cnf中添加

 

 
  1. [mysqld] 
  2. event-scheduler=on 

2.创建事件的语法

 

  1. CREATE 
  2. [DEFINER = { user | CURRENT_USER }] 
  3. EVENT 
  4. [IF NOT EXISTS] 
  5. event_name 
  6. ON SCHEDULE schedule 
  7. [ON COMPLETION [NOT] PRESERVE] 
  8. [ENABLE | DISABLE | DISABLE ON SLAVE] 
  9. [COMMENT 'comment'] 
  10. DO event_body; 
  11.  
  12. schedule: 
  13. AT timestamp [+ INTERVAL interval] ... 
  14. | EVERY interval 
  15. [STARTS timestamp [+ INTERVAL interval] ...] 
  16. [ENDS timestamp [+ INTERVAL interval] ...] 
  17. interval: 
  18. quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
  19. WEEK | SECOND | YEAR_MONTH | DAY_HOUR | 
  20. DAY_MINUTE |DAY_SECOND | HOUR_MINUTE | 
  21. HOUR_SECOND | MINUTE_SECOND} 

参数详细说明：

DEFINER: 定义事件执行的时候检查权限的用户。

ON SCHEDULE schedule: 定义执行的时间和时间间隔。

ON COMPLETION [NOT] PRESERVE: 定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。

ENABLE | DISABLE | DISABLE ON SLAVE: 定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。

COMMENT 'comment': 定义事件的注释。

3.更改事件的语法

 

 
  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] 

4.删除事件的语法

 

 
  1. DROP EVENT [IF EXISTS] event_name 

5.Do子句

在Do子句中实现事件的具体逻辑，几乎所有可以在存储程序中运行的MySQL语句都可以在event中使用。

1)简单SQL示例：

 

  1. CREATE EVENT e_hourly 
  2. ON SCHEDULE 
  3. EVERY 1 HOUR 
  4. COMMENT ‘Clears out sessions table each hour.' 
  5. DO 
  6. DELETE FROM site_activity.sessions; 

2)复杂SQL示例：

 

 
  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 ; 

3)Do子句中SQL的限制

基本上Do中可以使用任何在存储程序(Stored Routine)中允许的SQL语句，而存储程序中有些限制，event还有些额外的限制。

Stored Routine中如下语句不允许：

LOCK TABLES/UNLOCK TABLES

LOAD DATA与LOAD TABLE

支持动态SQL(PREPARE, EXECUTE, DEAALOCATE PREPARE)!但是PREPARE本身有些语句不允许执行。

INSERT DELAYED不会生效

EVENT的限制：

如果Do子句中包含ALTER EVENT子句，虽然能够创建，但是运行时会出错。

不要在Do子句中使用SELECT或SHOW这样仅仅是查询的语句，因为其输出无法从外部获取到。可以使用SELECT … INTO 这样的形式将查询结果保存起来。

5.查看EVENT

有如下方式可以查看event的信息：

 

 
  1. mysql.event 
  2. information_schema.events 
  3. show events 
  4. show create event 

三、event schedule其他注意点

MySQL保存了事件创建时的sql_mode作为其运行时的sql_mode;

如果在一个调度区间内任务没有处理完成，新的调度依然会生成，这样就会出现同时又多个任务在运行的情况。如果要避免多个任务同时存在，可以使用GET_LOCK()函数或者行锁、表锁。

四、 Mysql事件实战 测试环境

创建一个用于测试的test表：

 

 
  1. CREATE TABLE `test` ( 
  2. `id` int(11) NOT NULL AUTO_INCREMENT, 
  3. `t1` datetime DEFAULT NULL, 
  4. `id2` int(11) NOT NULL DEFAULT '0', 
  5. PRIMARY KEY (`id`) 
  6. ) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8 

实战1

Ø 创建一个每隔3秒往test表中插入一条数据的事件，代码如下：

 

 
  1. CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND 
  2. ON COMPLETION PRESERVE 
  3. DO INSERT INTO test(id,t1) VALUES('',NOW()); 

Ø 创建一个10分钟后清空test表数据的事件

 

 
  1. CREATE EVENT IF NOT EXISTS test 
  2. ON SCHEDULE 
  3. AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE 
  4. DO TRUNCATE TABLE test.aaa; 

Ø 创建一个在2012-08-23 00:00:00时刻清空test表数据的事件，代码如下：

 

 
  1. CREATE EVENT IF NOT EXISTS test 
  2. ON SCHEDULE 
  3. AT TIMESTAMP '2012-08-23 00:00:00' 
  4. DO TRUNCATE TABLE test; 

Ø 创建一个从2012年8月22日21点45分开始到10分钟后结束，运行每隔3秒往test表中插入一条数据的事件，代码如下：

 

 
  1. CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND 
  2. STARTS '2012-08-22 21:49:00' 
  3. ENDS '2012-08-22 21:49:00'+ INTERVAL 10 MINUTE 
  4. ON COMPLETION PRESERVE 
  5. DO INSERT INTO test(id,t1) VALUES('',NOW()); 

实战2

通常的应用场景是通过事件来定期的调用存储过程，下面是一个简单的示例：

创建一个让test表的id2字段每行加基数2的存储过程，存储过程代码如下：

 

 
  1. DROP PROCEDURE IF EXISTS test_add; 
  2. DELIMITER // 
  3. CREATE PROCEDURE test_add() 
  4. BEGIN 
  5. DECLARE 1_id INT DEFAULT 1; 
  6. DECLARE 1_id2 INT DEFAULT 0; 
  7. DECLARE error_status INT DEFAULT 0; 
  8. DECLARE datas CURSOR FOR SELECT id FROM test; 
  9. DECLARE CONTINUE HANDLER FOR NOT FOUND SET error_status=1; 
  10. OPEN datas; 
  11. FETCH datas INTO 1_id; 
  12. REPEAT 
  13. SET 1_id2=1_id2+2; 
  14. UPDATE test SET id2=1_id2 WHERE id=1_id; 
  15. FETCH datas INTO 1_id; 
  16. UNTIL error_status 
  17. END REPEAT; 
  18. CLOSE datas; 
  19. END 
  20. // 

事件设置2012-08-22 00:00:00时刻开始运行，每隔1调用一次存储过程，40天后结束，代码如下：

 

 
  1. CREATE EVENT test ON SCHEDULE EVERY 1 DAY 
  2. STARTS '2012-08-22 00:00:00' 
  3. ENDS '2012-08-22 00:00:00'+INTERVAL 40 DAY 
  4. ON COMPLETION PRESERVE DO 
  5. CALL test_add(); 


注：相关教程知识阅读请移步到MYSQL教程频道。
转载请说明出处
知优网 » MySQL中的事件调度基础学习教程（mysql 事件调度器）
admin

admin

分享到：

相关推荐

发表评论

您需要后才能发表评论