維基教科書

MySQL/Event

< MySQL

事件（Event）也稱調度事件（Scheduled Event）或臨時觸發器（Temporal Trigger）。是在特定時刻或者指定時間間隔被執行的事件。

同一事件可以有多個實例同時在執行。這就需要使用鎖來保證數據一致性。

Event Scheduler負責啟動事件。禁用Event Scheduler： 

mysqld --event-scheduler=DISABLED

或在配置文件my.cnf中： 

event_scheduler=DISABLED

可以使用全局系統變量控制Event Scheduler： 

 SELECT event_scheduler -- values: ON / OFF / DISABLED
 SET GLOBAL event_scheduler = ON
 SET GLOBAL event_scheduler = OFF

如果Event Scheduler是ON，通過命令SHOW PROCESSLIST可檢查Event Scheduler的狀態。其`User`是'event_scheduler'

使用SQL命令CREATE EVENT，ALTER EVENT，DROP EVENT。

CREATE EVENT

編輯

例如，讓SQL命令24小時後執行： 

 CREATE EVENT `newEventName`
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
   DO
     INSERT INTO `mydatabase`.`news` (`title`, `text`) VALUES ('Example!', 'This is not a reale news')

AT子句指定運行時間。

如果要創建定期事件（在規定的周期間隔），需要使用EVERY子句: 

 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

還可以指定開始與結束的時間點： 

 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY INTERVAL 1 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`
       STARTS CURRENT_TIMESTAMP + 1 MONTH
       ENDS CURRENT_TIMESTAMP + 3 MONTH

可用的時間單位： 

 YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND

DO子句指出要執行的語句。當包括的語句超過1條，需要使用BEGIN ... END語法： 

  delimiter |
  CREATE EVENT `newevent`
    ON SCHEDULE
      EVERY 1 DAY
    DO
      BEGIN
        DELETE FROM `logs`.`user` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
        DELETE FROM `logs`.`messages` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
        UPDATE `logs`.`activity` SET `last_cleanup` = CURRENT_TIMESTAMP;
    END |
  delimiter ;

禁止同名，可以使用IF NOT EXISTS子句： 

 CREATE EVENT `newevent2`
   IF NOT EXISTS
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

EVENT過期後會被MySQL默認刪除。使用ON COMPLETION子句來指明過期後保留： 

 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION PRESERVE
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

或者過期後不再保留： 

 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

If you don't tell MySQL to preserve the EVENT after it's expired, but it is already expired immediatly after creation (which happens if you specify a past TIMESTAMP in the AT / ENDS clause), the server creates and drop it as you requested. However, in this case it will inform you returning a 1588 warning.

可以指定 ENABLE, DISABLE，DISABLE ON SLAVES： 

 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   DISABLE
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

Comment最多64個字符。 

 CREATE EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   DISABLE
   COMMENT 'let\'s optimize some tables!'
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

可以指定以哪名用戶來檢查權限。默認是CURRENT_USER： 

 CREATE DEFINER = CURRENT_USER
   EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

特權用戶可以指定使用不同的用戶身份，並且必須指明host: 

 CREATE DEFINER = 'allen@localhost'
   EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

修改事件

編輯 
 ALTER EVENT `newevent2`
   ON SCHEDULE EVERY 2 DAY
   ON COMPLETION NOT PRESERVE
   RENAME TO `example_event`
   DISABLE
   COMMENT 'let\'s optimize some tables!'
   DO
     OPTIMIZE TABLE `mydatabase`.`news`

可以只寫必須的修改信息： 

 ALTER EVENT `newevent2` ENABLE;

刪除事件

編輯 
 DROP EVENT `event_name`

刪除不存在的事件會導致1517錯誤，可用IF EXISTS子句： 

 DROP EVENT IF EXISTS `event_name`


SHOW CREATE EVENT

編輯

展示事件被創建的SQL語句： Syntax: 

 SHOW CREATE EVENT newevent2;
  • Event - Event name.
  • sql_mode - SQL mode which was in effect when the CREATE EVENT statement was executed.
  • time_zone - Time zone that was used when the statement was executed.
  • Create Event - Statement used to create the event.
  • character_set_client
  • collation_connection
  • Database Collation

SHOW EVENTS

編輯

顯示指定資料庫中的事件： 

 SHOW EVENTS

 SHOW EVENTS FROM `my_nice_db`
 SHOW EVENTS IN `my_nice_db` -- synonym

 SHOW EVENTS LIKE 'my_%' -- name starts with 'my_'
 SHOW EVENTS WHERE definer LIKE 'admin@%' -- filters on any field
  • Db Database name.
  • Name Event name.
  • Definer User which created the EVENT and the host he used, in the form user@host.
  • Time zone Timezone in use for the EVENT. If it never changed, it should be 'SYSTEM', which means: server's timezone.
  • Type 'ONE TIME' for EVENTs which are executed only once, 'RECURRING' for EVENTs which are executed regularly.
  • Executed At The TIMESTAMP of the moment the EVENT will be executed. NULL for recursive EVENTs.
  • Interval Value Number of intervals between EVENT's executions. See next field. NULL for EVENTs which are executed only once.
  • Interval Field Interval type to wait between EVENTs executions. For example, if `Interval Field` is 'SECOND' and `Interval Value` is 30, the EVENT will be executed every 30 seconds. NULL for EVENTs which are executed only once.
  • Starts First execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
  • Ends Last execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
  • Status ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means that the EVENT is enabled on the master but disabled on the slaves.
  • Originator Id of the server where the EVENT was created. If it has been created on the current server this value is 0. Added in 5.1.
  • character_set_client
  • collation_connection
  • Database Collation

INFORMATION_SCHEMA.EVENTS

編輯

顯示伺服器上所有的事件

  • EVENT_CATALOG Always NULL (CATALOGs are not implemented in MySQL).
  • EVENT_SCHEMA Database name.
  • EVENT_NAME Event name.
  • DEFINER User which created the EVENT and the host he used, in the form user@host.
  • TIME_ZONE Timezone in use for the EVENT. If it never changed, it should be 'SYSTEM', which means: server's timezone.
  • EVENT_BODY Language used to write the routine that will be executed.
  • EVENT_DEFINITION Routine that will be executed.
  • EVENT_TYPE 'ONE TIME' for EVENTs which are executed only once, 'RECURRING' for EVENTs which are executed regularly.
  • EXECUTE_AT The TIMESTAMP of the moment the EVENT will be executed. NULL for recursive EVENTs.
  • INTERVAL_VALUE Number of intervals between EVENT's executions. See next field. NULL for EVENTs which are executed only once.
  • INTERVAL_FIELD Interval type to wait between EVENTs executions. For example, if `Interval Field` is 'SECOND' and `Interval Value` is 30, the EVENT will be executed every 30 seconds. NULL for EVENTs which are executed only once.
  • SQL_MODE SQL mode which was in effect when the EVENT has been created.
  • STARTS First execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
  • ENDS Last execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
  • STATUS ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means that the EVENT is enabled on the master but disabled on the slaves.
  • ON_COMPLETION 'NOT PRESERVE' (the EVENT will be deleted) or 'PRESERVE' (the EVENT won't be deleted'.
  • CREATED Creation DATETIME.
  • LAST_ALTERED Last edit's DATETIME. If the EVENT has never been altered, `LAST_ALTERED` has the same value as `CREATED`.
  • LAST_EXECUTED Last execution TIMESTAMP. If the EVENT has never been executed yet, this value is NULL.
  • EVENT_COMMENT Comment associated to the EVENT. Is there is no comment, this value is an empty string.
  • ORIGINATOR Id of the server where the EVENT was created. If it has been created on the current server this value is 0. Added in 5.1.
  • character_set_client
  • collation_connection
  • Database Collation
取自「https://zh.wikibooks.org/w/index.php?title=MySQL/Event&oldid=133911」