MySQL計劃任務事件

2019-10-16 22:56:00

在本教學中,您將了解MySQL事件排程程式以及如何建立MySQL事件以自動執行資料庫任務。

MySQL事件是基於預定義的時間表執行的任務,因此有時它被稱為預定事件。MySQL事件也被稱為「時間觸發」,因為它是由時間觸發的,而不是像觸發器一樣更新錶來觸發的。MySQL事件類似於UNIX中的cron作業或Windows中的任務排程程式。

您可以在許多情況下使用MySQL事件,例如優化資料庫表,清理紀錄檔,歸檔資料或在非高峰時間生成複雜的報告。

MySQL事件排程器組態

MySQL使用一個名為事件排程執行緒的特殊執行緒來執行所有排程的事件。可以通過執行以下命令來檢視事件排程程式執行緒的狀態:

SHOW PROCESSLIST;

執行上面查詢語句,得到以下結果 -

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host            | db       | Command | Time | State    | Info             |
+----+------+-----------------+----------+---------+------+----------+------------------+
|  2 | root | localhost:50405 | NULL     | Sleep   | 1966 |          | NULL             |
|  3 | root | localhost:50406 | yiibaidb | Sleep   | 1964 |          | NULL             |
|  4 | root | localhost:50407 | yiibaidb | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

預設情況下,事件排程程式執行緒未啟用。 要啟用和啟動事件排程程式執行緒,需要執行以下命令:

SET GLOBAL event_scheduler = ON;

現在看到事件排程器執行緒的狀態,再次執行SHOW PROCESSLIST命令,結果如下所示 -

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| Id | User            | Host            | db       | Command | Time | State                  | Info             |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
|  2 | root            | localhost:50405 | NULL     | Sleep   | 1986 |                        | NULL             |
|  3 | root            | localhost:50406 | yiibaidb | Sleep   | 1984 |                        | NULL             |
|  4 | root            | localhost:50407 | yiibaidb | Query   |    0 | starting               | SHOW PROCESSLIST |
|  5 | event_scheduler | localhost       | NULL     | Daemon  |    6 | Waiting on empty queue | NULL             |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
4 rows in set

要禁用並停止事件排程程式執行緒,可通過執行SET GLOBAL命令將event_scheduler其值設定為OFF

SET GLOBAL event_scheduler = OFF;

建立新的MySQL事件

建立事件與建立其他資料庫物件(如儲存過程或觸發器)類似。事件是一個包含SQL語句的命名物件。

儲存過程僅在直接呼叫時執行; 觸發器則與一個表相關聯的事件(例如插入更新刪除)事件發生時,可以在一次或更多的規則間隔執行事件時執行觸發。

要建立和計劃新事件,請使用CREATE EVENT語句,如下所示:

CREATE EVENT [IF NOT EXIST]  event_name
ON SCHEDULE schedule
DO
event_body

下面讓我們更詳細地解釋語法中的一些引數 -

  • 首先,在CREATE EVENT子句之後指定事件名稱。事件名稱在資料庫模式中必須是唯一的。
  • 其次,在ON SCHEDULE子句後面加上一個表。如果事件是一次性事件,則使用語法:AT timestamp [+ INTERVAL],如果事件是迴圈事件,則使用EVERY子句:EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]

  • 第三,將DO語句放在DO關鍵字之後。請注意,可以在事件主體內呼叫儲存過程。 如果您有復合SQL語句,可以將它們放在BEGIN END塊中。

我們來看幾個建立事件的例子來了解上面的語法。

首先,建立並計劃將一個訊息插入到messages表中的一次性事件,請執行以下步驟:

USE testdb;
CREATE TABLE IF NOT EXISTS messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL
);

其次,使用CREATE EVENT語句建立一個事件:

CREATE EVENT IF NOT EXISTS test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test MySQL Event 1',NOW());

第三,檢查messages表; 會看到有1條記錄。這意味著事件在建立時被執行。

SELECT * FROM messages;

執行上面查詢語句,得到以下結果 -

mysql> SELECT * FROM messages;
+----+--------------------+---------------------+
| id | message            | created_at          |
+----+--------------------+---------------------+
|  1 | Test MySQL Event 1 | 2017-08-03 04:23:11 |
+----+--------------------+---------------------+
1 row in set

要顯示資料庫(testdb)的所有事件,請使用以下語句:

SHOW EVENTS FROM testdb;

執行上面查詢看不到任何行返回,因為事件在到期時自動刪除。 在我們的範例中,它是一次性的事件,在執行完成時就過期了。

要更改此行為,可以使用ON COMPLETION PRESERVE子句。以下語句建立另一個一次性事件,在其建立時間1分鐘後執行,執行後不會被刪除。

CREATE EVENT test_event_02
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
   INSERT INTO messages(message,created_at)
   VALUES('Test MySQL Event 2',NOW());

等待1分鐘後,檢視messages表,新增了另一條記錄:

SELECT * FROM messages;

執行上面查詢語句,得到以下結果 -

mysql> SELECT * FROM messages;
+----+--------------------+---------------------+
| id | message            | created_at          |
+----+--------------------+---------------------+
|  1 | Test MySQL Event 1 | 2017-08-03 04:23:11 |
|  2 | Test MySQL Event 2 | 2017-08-03 04:24:48 |
+----+--------------------+---------------------+
2 rows in set

如果再次執行SHOW EVENTS語句,看到事件是由於ON COMPLETION PRESERVE子句的影響:

SHOW EVENTS FROM testdb;

執行上面查詢語句,得到以下結果 -

mysql> SHOW EVENTS FROM testdb;
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db     | Name          | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| testdb | test_event_02 | root@localhost | SYSTEM    | ONE TIME | 2017-08-03 04:24:48 | NULL           | NULL           | NULL   | NULL | DISABLED |          0 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set

以下語句建立一個迴圈的事件,每分鐘執行一次,並在其建立時間的1小時內過期:

CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO messages(message,created_at)
   VALUES('Test MySQL recurring Event',NOW());

請注意,使用STARTSENDS子句定義事件的有效期。等待個3,5分鐘後再檢視messages表資料,以測試驗證此迴圈事件的執行。

SELECT * FROM messages;

執行上面查詢語句,得到以下結果 -

mysql> SELECT * FROM messages;
+----+----------------------------+---------------------+
| id | message                    | created_at          |
+----+----------------------------+---------------------+
|  1 | Test MySQL Event 1         | 2017-08-03 04:23:11 |
|  2 | Test MySQL Event 2         | 2017-08-03 04:24:48 |
|  3 | Test MySQL recurring Event | 2017-08-03 04:25:20 |
|  4 | Test MySQL recurring Event | 2017-08-03 04:26:20 |
|  5 | Test MySQL recurring Event | 2017-08-03 04:27:20 |
+----+----------------------------+---------------------+
5 rows in set

刪除MySQL事件

要刪除現有事件,請使用DROP EVENT語句,如下所示:

DROP EVENT [IF EXISTS] event_name;

例如,要刪除test_event_03的事件,請使用以下語句:

DROP EVENT IF EXISTS test_event_03;

在本教學中,您已經了解了MySQL事件,如何從資料庫模式建立和刪除事件。 在下一個教學中,我們將向您展示如何修改事件。