MySQL任務排程工具EVENT

概述

MySQL的EVENT 是根據計劃運作的任務，類似于Linux上的crontab 任務

主要具有以下屬性

mysql中，定時事件的對象是全局唯一的
event根據時間表來執行操作，包含一個SQL操作，也可以使用begin,end 實作多個SQL的複合執行
event 的權限（建立修改和删除）進行控制賦予
可以使用SQL語句設定或修改事件的許多屬性。這些屬性包括事件的名稱，時間，持久性（即是否在其計劃到期後保留），狀态（啟用或禁用），要執行的操作以及為其配置設定的架構

如何開啟

檢視系統變量，ON是表示已啟動，OFF表示已停止（預設值），也可以為DISABLED，表示無法運作，不推薦使用這個參數值，狀态沒法在運作是進行修改

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

隻要是開啟了這個功能，就可以在看到此子產品的線程，線程運作并執行所有定時任務

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 454
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 45647
  State: Waiting for next activation
   Info: NULL

開啟指令，如下，關閉也是同樣

SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;

另外，因為event是全局變量，未指定全局來設定，會導緻報錯

mysql> set @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL

權限賦予

EVENT 控制事件建立、修改和删除，權限由grant賦予，基本格式如下

mysql> GRANT EVENT ON test.* TO test;
Query OK, 0 rows affected (0.04 sec)

由于event是全局架構級别的權限，是以不能針對某個表進行賦權

mysql> GRANT EVENT ON test.test01 TO test;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

很顯然的，包含在events裡面的SQL語句，定時任務執行的使用者一定需要有相關的權限，否則定時任務觸發，但是任務執行也會失敗，此時可以在mysql日志裡可以看到資訊

## 相關資訊

建立event的基本文法

CREATE EVENT

CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [COMMENT 'comment']
    DO sql_statement;

schedule:
    AT timestamp [+ INTERVAL interval]
  | EVERY interval [STARTS timestamp] [ENDS timestamp]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

系統表資訊，會記錄每個建立的定時任務

mysql> select * from information_schema.events\G;
*************************** 1. row ***************************
       EVENT_CATALOG: def            --永遠是def
        EVENT_SCHEMA: test            --所在是的資料庫名
          EVENT_NAME: event_test_insert            --event名
             DEFINER: root@%            --建立這個event的使用者
           TIME_ZONE: SYSTEM            --排程時區，預設是SYSTEM
          EVENT_BODY: SQL                --主體語言
    EVENT_DEFINITION: INSERT INTO test.event_test VALUES (CURRENT_TIMESTAMP)                --需要執行的部分，就是DO 的部分内容
          EVENT_TYPE: RECURRING                --事件重複類型，一次（TRANSIENT）重複（REPEATING）
          EXECUTE_AT: NULL                --如果事件的計時由EVERY子句而不是AT子句确定（也就是說，事件類型為repeating），則此列的值為NULL。
      INTERVAL_VALUE: 1                --every的數字部分，對于一次性事件，此列為null
      INTERVAL_FIELD: SECOND                --every的機關部分，用于管理事件的時間，此處為秒
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION                    --在建立或更改事件并在其下執行事件時有效的SQL模式
              STARTS: 2020-03-19 13:51:52                --starts子句的事件
                ENDS: NULL                    --事件定義時包含了ENDS子句的循環事件
              STATUS: DISABLED                --ENABLE, DISABLE或SLAVESIDE_DISABLED 三個值之一，表示event的啟用狀态
       ON_COMPLETION: PRESERVE
             CREATED: 2020-03-19 13:51:52                --事件建立時間
        LAST_ALTERED: 2020-03-19 13:53:10                --最後修改時間
       LAST_EXECUTED: 2020-03-19 13:53:10                --最後一次執行時間
       EVENT_COMMENT:                     --備注
          ORIGINATOR: 0                --伺服器id，預設為0
CHARACTER_SET_CLIENT: utf8                --字元
COLLATION_CONNECTION: utf8_general_ci            
  DATABASE_COLLATION: latin1_swedish_ci

除了檢視系統表，可以有以下幾種方式來檢視已經建立的events，輸出資訊基本一緻

SHOW EVENTS;
SHOW CREATE EVENT xxxx;
SELECT * FROM  MYSQL.EVENTS;

測試建立

建立一張測試表

mysql> CREATE TABLE kmtest(timeline varchar(20));
Query OK, 0 rows affected (0.02 sec)

定時循環任務

計劃每一秒插入表中目前時間，如下

mysql> CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from kmtest limit 2;
+---------------------+
| timeline |
+---------------------+
| 2020-03-20 14:54:07 |
| 2020-03-20 14:54:08 |
+---------------------+
2 rows in set (0.00 sec)

停止目前的定時任務

mysql> ALTER EVENT event_kmtest ON COMPLETION PRESERVE DISABLE;
Query OK, 0 rows affected (0.00 sec)

如果不再需要，可以删除任務

mysql> drop event event_kmtest;
Query OK, 0 rows affected (0.00 sec)

定某一時刻執行

建立在某一時刻執行一次任務

mysql> CREATE EVENT event_kmtest ON SCHEDULE AT '2020-03-20 15:20:20' DO set @time= CURRENT_TIMESTAMP;INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

過了這個時間點再來檢視

mysql> select * from kmtest;
+---------------------+
| timeline |
+---------------------+
| 2020-03-20 15:20:20|
+---------------------+
1 row in set (0.00 sec)

在等待執行的這段時間可以看到event的執行資訊，到期執行完了就自動去除了，可以看到type是one time

mysql> show events\G;
*************************** 1. row ***************************
                  Db: test
                Name: event_kmtest
             Definer: root@%
           Time zone: SYSTEM
                Type: ONE TIME

多個SQL

一個events包含多個SQL，在使用的過程中，可能有時需要執行的SQL不僅僅是一個，有可能是多個，此時就需要使用定界符和begin end 來進行建立。例子如下：

delimiter |
CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND 
 DO 
  begin
  set @time= CURRENT_TIMESTAMP;
  INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP);
  end |
delimiter;

PS：文法是沒錯的，但是我在mysql的用戶端指令行去執行時，會預設為換行輸出，我也不知道啥原因，利用一些工具來執行是沒問題的

MySQL任務排程工具EVENT

概述

如何開啟

權限賦予

測試建立

定時循環任務

定某一時刻執行

多個SQL

繼續閱讀

MySQL的4種隔離級别？出現問題

Ubuntu Linux下Apache的配置檔案

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

samba伺服器的功能

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

【Linux】UDP廣播封包接收速率問題

SequoiaDB巨杉資料庫C++驅動概述

Linux裝置模型（中）之上層容器

PowerPC平台 Linux移植三