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 hour
do
    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: 2013306
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 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: NULL
Command: 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_2
on 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_3
on schedule every 5 second ends current_timestamp + interval 1 minute 
ON COMPLETION PRESERVE
do
    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 1

create event test_event_4
on schedule every 5 second ends current_timestamp + interval 1 minute 
ON COMPLETION PRESERVE
do
    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 ;