MySql事件部署(EventScheduler)

mysql事件调度(Event Scheduler)

原文来自：http://ramonblog.cloudfoundry.com/blog/4fd8d0702de195990a000002

启用event_scheduler

MySql的事件调度是在5.1引入的，从而可以将数据的定时操作放在数据库级别，而不是通过其它外部的程序定时执行。要定义Event必须先开启event_scheduler。

-- 查看event_scheduler状态, ON表示开启，Off表示关闭 show variables like 'event_scheduler';  -- 开启event_scheduler   set global event_scheduler=1; 

欢迎大家阅读《MySql事件部署(Event Scheduler)》,跪求各位点评，by

这里的global表示是整个数据库级别开启，还有其它的级别，譬如Session。更多的参考http://dev.mysql.com/doc/refman/5.6/en/events-configuration.html.

创建Event

delimiter | CREATE EVENT Aggregate_Event     ON SCHEDULE          EVERY 1 DAY     COMMENT 'Aggregate Event data to AppEventDate'     DO         BEGIN             UPDATE Event set readyToAggregated=1 WHERE readyToAggregated=0;             INSERT INTO AppEventDate(name, app, count, totalTime, timestampUTC)               SELECT name, app, SUM(count), SUM(totalTime), timestampUTC FROM Event WHERE readyToAggregated=1 GROUP BY app, name, DATE_FORMAT(timestampUTC, '%Y-%m-%d');             UPDATE Event set readyToAggregated=2 where readyToAggregated=1;         END | delimiter ; 

开始的delimiter |表示修改语句的分隔符为’|’,默认是’;’。这样做的目的是为了将后面的语句能够一次性发到服务器。其实这个是在mysql的控制台下用的，如果你是用Mysql Workbench根本不需要这样，只需要选择所有的语句，按执行所选语句的命令即可。

这里创建的事件名字为’Aggregate_Event’,每天执行一次，第一次是从语句创建的时候开始执行。当然可以在EVERY 1 DAY后面加上STARTS来定义第一次执行的时间。如:

STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR 

表示开始执行时间是当前时间之后的1小时。你也可以指定特定时间执行，如STARTS '2012-6-14 14:20:00',但是注意这个时间是相对于你的timezone设置来说的，你可以运行’SHOW VARIABLES LIKE ‘timezone”来查看你的time_zone设置，譬如我的是UTC，并不是我本地的时区+8，所以转成本地时间则是2012-06-14 22:20:00。

后面的语句则是你需要实现的SQL语句了。

增加事务(Transaction)

增加事务自然是为了保证一致性，即要么都成功，要么失败回滚(Rollback)。MySql的事务定义有一些不同，具体的看下面的例子。

-- 创建测试表 CREATE TABLE TT(id int, PRIMARY KEY (id)); CREATE TABLE EventLog (id int(10) unsigned NOT NULL AUTO_INCREMENT, log varchar(200), created TIMESTAMP, PRIMARY KEY (id));  -- 创建Event，并增加事务机制 delimiter | CREATE EVENT testEvent ON SCHEDULE     EVERY 1 MINUTE DO     BEGIN          DECLARE EXIT HANDLER FOR SQLEXCEPTION         BEGIN             ROLLBACK;             INSERT INTO EventLog (log) values ('aggregate fail');         END;          START TRANSACTION;             INSERT INTO tt (id) values (1);         COMMIT;     END | delimiter ; 

首先我们创建了两个要用的表，然后创建Event。这里的不同是在BEGIN…END之间增加了一个Handler的声明，该声明是用来处理所有SQLEXCEPTION的，具体来说，首先是回滚，然后是插入失败记录。而Event的正常操作则是在START TRANSACTION;和COMMIT之间，如果不发生异常，则会提交事务。关于更多Handler的内容可以参考http://dev.mysql.com/doc/refman/5.6/en/declare-handler.html.

查看和修改EVENT

Event创建之后，会将相关信息存储在mysql.event表里面，所以通过下面的SQL语句可以看到详细信息：

SELECT * FROM mysql.event; 

重要的信息包括name, intervalvalue, intervalfield, status, comment.开始创建的event相应的值就是：Aggregate_Event， 1, DAY, ENABLED,’Aggregate Event data to AppEventDate’.

停止Event调度，则是运行下面的语句.之后你再利用上面的语句查看Event的信息会发现其status已经变成disabled了。

ALTER EVENT Aggregate_Event DISABLE; 

删除Event则是：

DROP EVENT Aggregate_Event; 

更多信息参考官方文档： http://dev.mysql.com/doc/refman/5.1/en/create-event.htmlhttp://dev.mysql.com/doc/refman/5.1/en/alter-event.html

一些有趣的事情

1. TIMESTAMP数据类型 特别的，在定义Timestamp时，可以指定自动初始化和自动更新。如果单独指定其中任意一个，则意味着关闭另一个。如果两个都不指定，则意味着两个同时开启。所以在开始的Event定义里面犯了一个错误. 表Event有一个Timestamp字段timestampUTC,我的目的是，定时将Event中的数据Aggregate起来存在AppEventDate表里面去，之后将操作过的Event数据删除掉。由于在事件调度的时候，Event表还是可能发生数据插入的情形，为了标明哪些数据是聚合过的，所以用字段readyToAggregated表示。但是问题在于Event表定义列timestampUTC的时候没有指明自动初始化和自动更新，导致于默认情形下两者都支持，所以在第一个sql语句update的时候，所有的timestampUTC字段都成了当前时间，显然就导致后面的聚合语句全部出错了。因此解决办法就是将该字段指明为自动初始化。