想要定时根据条件更新数据库，但又不想写脚本定时去跑任务。这两天发现一个好用的东西event scheduler

整理了一下 Event Scheduler 的主要用法

打开
1
SET GLOBAL event_scheduler = ON;
关闭
1
SET GLOBAL event_scheduler = OFF;
创建一个 Event
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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}

例子：

  1. 每一分钟往数据库里插入一条数据(仅id 和 time 字段)

    1
    2
    3
    4
    5
    6
    CREATE EVENT IF NOT EXISTS
      insert_every_minute
    ON SCHEDULE
      EVERY 1 MINUTE
    DO
      insert into time_interval (time) values(CURRENT_TIMESTAMP);

  2. 一周后开始每天凌晨1点插入一条数据,两周后结束执行

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE EVENT IF NOT EXISTS
      insert_every_1am_after_one_week
    ON SCHEDULE
      EVERY 1 DAY 
      STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 WEEK + INTERVAL 1 HOUR)
      ENDS (TIMESTAMP(CURRENT_DATE) + INTERVAL 2 WEEK + INTERVAL 1 HOUR)
    DO
      insert into time_interval(time) values(CURRENT_TIMESTAMP);
查看现在所有的 Events
1
show events;

以下是我测试的 event
所有 event

图中Status 为 disable 是我手动停止的

停止一个 Event，修改它
1
2
3
4
5
6
7
8
9
ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]

例子：

  1. 停止刚才的 insert_every_second
    1
    ALTER EVENT insert_every_second DISABLE
查看一个 event 创建的语法
1
SHOW CREATE EVENT event_name;

例子：

  1. 查看刚才的insert_every_1am_after_one_week
    1
    show create event insert_every_1am_after_one_week

show create event

删除一个 event

上次遇到一个因为 definer 不对的 event 报错：

[ERROR] Event Scheduler: [USER@%][MY_DB.recalcSortWeight] The user specified as a definer (‘USER’@’%’) does not exist 2016-03-11 00:04:34
查看了一下，这个 event 并不是我需要的，也不知道谁写的，所以删了算了

1
DROP EVENT [IF EXISTS] event_name