【MySQL基础】09：事件调度器（EVENT）

     

     本教程以MySQL8为主版本（内容兼顾MySQL5.7）。

     所有MySQL文章的目录为：总目录    https://blog.csdn.net/zyplanke/article/details/102968014     

 

   MySQL的事件调度器，为按照定义时间和周期运行运行某个调度任务。类似于linux下的crontab机制。

 

创建语法：

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    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}

举例，创建一个简单的EVENT：

mysql> CREATE EVENT e_store_ts
    -> ON SCHEDULE
    ->   EVERY 10 SECOND
    -> DO
    ->   INSERT INTO test.TB VALUES ('AAAA', 'BBBBBBBBBB');
Query OK, 0 rows affected (0.00 sec)

mysql> show events \G;
*************************** 1. row ***************************
                  Db: test
                Name: e_store_ts
             Definer: root@%
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2019-11-22 21:56:59
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

  发现，等啊等啊，表中的并没有新记录。查看调度器执行进程的状态，原来运行EVENT的进程是OFF状态，需要开启。

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

开启调度器执行进程

 通过以下将进程开启，发现多了一个进程：

mysql> set GLOBAL event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show PROCESSLIST
*************************** 2. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 3
  State: Waiting for next activation
   Info: NULL
2 rows in set (0.00 sec)

ERROR: 
No query specified

过十几秒，这时再看Table中数据条数不断的增加。

停止（禁用）事件调度器

 以下命令可以停用某个调度器。     还可以删除该调度器

mysql> alter event e_store_ts disable;
Query OK, 0 rows affected (0.00 sec)

#删除
mysql> drop event e_store_ts;