Event --mysql的scheduler.md

事件调度器event
相当于oracle scheduler

CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment’]
DO event_body;

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}

例

create event myevent on schedule at current_timestamp + interval 1 hourdo    update myschema.mytable set mycol=mycol+1;

调用存储过程就是 do call proc();

创建测试表

create table test(id varchar(10),create_time datetime);

创建事件调度器，每格5秒向test表插入一条记录

create event test_event_1 on schedule every 5 second do    insert into test.test(id,create_time) values ('test',now());

查看调度器状态

show events\G*************************** 1. row ***************************                  Db: test                Name: test_event_1             Definer: mysql@localhost           Time zone: SYSTEM                Type: RECURRING          Execute at: NULL      Interval value: 5      Interval field: SECOND              Starts: 2016-08-07 14:14:18                Ends: NULL              Status: ENABLED          Originator: 2013306character_set_client: gbkcollation_connection: gbk_chinese_ci  Database Collation: utf8_general_ci1 row in set (0.01 sec)

隔几秒后查看，发现没有数据插入

mysql> select * from test;Empty set (0.00 sec)

查看事件调度器状态，发现默认是关闭的

mysql> show variables like '%scheduler%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| event_scheduler | OFF   |+-----------------+-------+1 row in set (0.01 sec)

通过下面的命令打开调度器，同事show processlist发现产生一个后台进程

mysql> set global event_scheduler=1;Query OK, 0 rows affected (0.01 sec)>Turn ON event_scheduler的四种方式SET GLOBAL event_scheduler = ON;SET @@global.event_scheduler = ON;SET GLOBAL event_scheduler = 1;SET @@global.event_scheduler = 1;mysql> show variables like '%scheduler%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| event_scheduler | ON    |+-----------------+-------+1 row in set (0.00 sec)mysql> show processlist\G*************************** 4. row ***************************     Id: 7   User: event_scheduler   Host: localhost     db: NULLCommand: Daemon   Time: 3  State: Waiting for next activation

再次查看表

mysql> select * from test;+------+---------------------+| id   | create_time         |+------+---------------------+| test | 2016-08-07 14:16:43 || test | 2016-08-07 14:16:48 || test | 2016-08-07 14:16:53 || test | 2016-08-07 14:16:58 || test | 2016-08-07 14:17:03 || test | 2016-08-07 14:17:08 || test | 2016-08-07 14:17:13 || test | 2016-08-07 14:17:18 || test | 2016-08-07 14:17:23 || test | 2016-08-07 14:17:28 || test | 2016-08-07 14:17:33 || test | 2016-08-07 14:17:38 || test | 2016-08-07 14:17:43 |+------+---------------------+

为了防止表变得很大，创建一个新的调度器，每个一分钟清空一次test表
create event trunc_test
on schedule every 1 minute
do truncate table test.test;
常见完成后貌似就立马执行一次do

如果事件调度器不再使用，可以disable或删除
–禁用
alter event test_event_1 disable;
–删除
drop event test_event_1;

注意事项
权限GRANT EVENT ON myschema.* TO jon@ghidora;

DEFINER子句指定执行event时被检查权限的用户。格式是’user_name’@’host_name’，与grant语句，current_user或current_user()的格式一样

mysql> select current_user;+-----------------+| current_user    |+-----------------+| mysql@localhost |+-----------------+1 row in set (0.00 sec)mysql> select current_user();+-----------------+| current_user()  |+-----------------+| mysql@localhost |+-----------------+1 row in set (0.00 sec)

如果没有super权限，那么definer只可以指定你当前使用的account
如果有super权限，你可以指定任何account
你可以在创建event时指定一个不存在的account，但当event运行时，仍然会报错

AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY
表示从现在起，每隔3周2天执行一次

EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK
表示从下周开始，每三个月执行一次

EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL ‘6:15’ HOUR_MINUTE.
表示从现在起的6小时15分钟后，每2周执行一次

如果没指定starts，就是从创建时间为起点

EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK
表示现在算起的30分钟后，每12小时执行一次，现在算起的4周后停止任务

如果你的任务在下次执行前没有结束(比如需要运行1分钟，而循环为每隔30秒执行一次)。你可能需要使用GET_LOCK()函数，或者行锁表锁来避免本次任务没有执行完就又开始了一次新的任务

通常情况，当event expired。它会立即被drop。你可以在创建时使用ON COMPLETION PRESERVE和ON COMPLETION NOT PRESERVE来控制expire后是否删除

create event test_event_2on schedule every 5 second  ends current_timestamp + interval 1 minute do    insert into test.test(id,create_time) values ('default',now());create event test_event_3on schedule every 5 second ends current_timestamp + interval 1 minute ON COMPLETION PRESERVEdo    insert into test.test(id,create_time) values ('PRESERVE',now());

结束后，test_event_2被删除了，而test_event_3仍被保留

mysql> select EVENT_NAME,DEFINER,STATUS,LAST_EXECUTED from EVENTS;+--------------+-----------------+----------+---------------------+| EVENT_NAME   | DEFINER         | STATUS   | LAST_EXECUTED       |+--------------+-----------------+----------+---------------------+| test_event_1 | mysql@localhost | DISABLED | 2016-08-07 15:17:28 || trunc_test   | mysql@localhost | DISABLED | 2016-08-07 15:16:50 || test_event_3 | mysql@localhost | DISABLED | 2016-08-07 15:22:39 |+--------------+-----------------+----------+---------------------+3 rows in set (0.00 sec)

测试错误

mysql> truncate table test;Query OK, 0 rows affected (0.00 sec)mysql> insert into test.test(id,create_time) values ('longwordhahaha',now());ERROR 1406 (22001): Data too long for column 'id' at row 1create event test_event_4on schedule every 5 second ends current_timestamp + interval 1 minute ON COMPLETION PRESERVEdo    insert into test.test(id,create_time) values ('longwordhahaha',now());

插入必定都失败，不过events表中并没有相应的列记录执行是succeed还是faild，这点比oracle是在差了不少

只有在error-log中反映除了错误

Event Scheduler: [mysql@localhost][test.test_event_4] Data too long for column 'id' at row 1

对于复制环境，你也许希望在master创建的event不在slave执行，此时在创建时指定DISABLE ON SLAVE就可以满足你的需求

你可以为event附加comment，comment内容不超过64个字符，需要被引号括起

event可以do复合语句

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 (time, total)          SELECT CURRENT_TIMESTAMP, COUNT(*)            FROM site_activity.sessions;        DELETE FROM site_activity.sessions;      END |delimiter ;

更复杂的

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 ;