4.4 Event的使用

MySQL Event是用来做事件(通常是SQL)的定时调度,HybridDB完全兼容了MySQL的event能力

1. 创建Event

CREATE EVENT event_name
  ON SCHEDULE schedule
  [COMMENT 'string']
  DO dml_or_ddl

schedule:
    AT 'timestamp'
    |
    EVERY n {SECOND | MINUTE | HOUR | DAY | MONTH | YEAR} [STARTS 'timestamp']
  • event_name可带db名
  • 支持两种schedule模式
    • 指定某个时间戳(只调度一次)
    • 指定调度间隔(无限调度),可选指定起始时间
  • Event实体可以是单句的dml或者部分ddl
  • Event中目前不支持多句sql或者存储过程

如:

use edb;

CREATE EVENT e1 on schedule
at '2018-12-12 11:12:21'
do
  insert into tbl select * from tbl;

CREATE EVENT e2 on schedule
every 5 minute
comment 'e2 as test'
do
  select 1,2,3,4,5;

CREATE EVENT edb.e3 on schedule
every 12 hour starts '2018-10-30 02:00:00'
do
  truncate table tbl;

2. 查看Event

mysql> show events;
+------------+---------------------+----------+------+-----------------------+-----------------------+-----------------------+--------+----------+
| event_name | at_timestamp        | interval | unit | create_time           | last_schedule_time    | next_schedule_time    | status | fail_msg |
+------------+---------------------+----------+------+-----------------------+-----------------------+-----------------------+--------+----------+
| e2         | 2017-12-19 19:09:01 | NULL     | NULL | 2017-12-19 19:07:06.0 | 2017-12-19 19:09:36.0 | 2017-12-19 19:09:01.0 | FINISH | NULL     |
+------------+---------------------+----------+------+-----------------------+-----------------------+-----------------------+--------+----------+
1 row in set (0.03 sec)

mysql> show create event edb.e2;
+-------+----------+-----------+-------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event | sql_mode | time_zone | Create Event                                                                        | character_set_client | collation_connection | Database Collation |
+-------+----------+-----------+-------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| e2    |          | SYSTEM    | CREATE EVENT e2 on schedule every 5 minute comment 'e2 as test' do select 1,2,3,4,5 | utf8_bin             | utf8_bin             | utf8_bin           |
+-------+----------+-----------+-------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.03 sec)

3. 删除Event

DROP EVENT [IF EXISTS] event_name;
  • event_name可带db名

results matching ""

    No results matching ""