MySQL/Event

添加链接
维基教科书，自由的教学读本

事件（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”