MySQL 8.0-13.1.13 CREATE EVENT Statement

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}

This statement creates and schedules a new event. The event does not run unless the Event Scheduler is enabled. For information about checking Event Scheduler status and enabling it if necessary, see Section 25.4.2, “Event Scheduler Configuration”.

该语句创建并调度一个新事件。除非启用了“事件计划程序”，否则事件不会运行。有关检查事件调度器状态并在必要时启用它的信息，请参见25.4.2节“事件调度器配置”。

CREATE EVENT requires the EVENT privilege for the schema in which the event is to be created. If the DEFINER clause is present, the privileges required depend on the user value, as discussed in Section 25.6, “Stored Object Access Control”.

CREATE EVENT需要要在其中创建事件的模式的EVENT权限。如果存在DEFINER子句，所需的特权取决于用户值，如第25.6节“存储对象访问控制”中所讨论的。

The minimum requirements for a valid CREATE EVENT statement are as follows:

有效的CREATE EVENT语句的最低要求如下:

• The keywords CREATE EVENT plus an event name, which uniquely identifies the event in a database schema.

• 关键字CREATE EVENT加上事件名称，它唯一地标识数据库模式中的事件。

• An ON SCHEDULE clause, which determines when and how often the event executes.

• ON SCHEDULE子句，用于确定事件执行的时间和频率。

• A DO clause, which contains the SQL statement to be executed by an event.

• DO子句，包含一个事件要执行的SQL语句。

This is an example of a minimal CREATE EVENT statement:

这是一个最小的CREATE EVENT语句示例:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

The previous statement creates an event named myevent. This event executes once—one hour following its creation—by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1.

前面的语句创建了一个名为myevent的事件。该事件在创建之后的一小时内执行一次——通过运行一条增加myschema值的SQL语句。Mytable表的mycol列乘以1。

The event_name must be a valid MySQL identifier with a maximum length of 64 characters. Event names are not case-sensitive, so you cannot have two events named myevent and MyEvent in the same schema. In general, the rules governing event names are the same as those for names of stored routines. See Section 9.2, “Schema Object Names”.

event_name必须是一个有效的MySQL标识符，最大长度为64个字符。事件名不区分大小写，因此在同一个模式中不能有两个名为myevent和myevent的事件。通常，控制事件名称的规则与存储例程的规则相同。参见第9.2节“架构对象名称”。

An event is associated with a schema. If no schema is indicated as part of event_name, the default (current) schema is assumed. To create an event in a specific schema, qualify the event name with a schema using schema_name.event_name syntax.

事件与模式相关联。如果没有指定模式作为event_name的一部分，则假定使用默认(当前)模式。要在特定模式中创建事件，请使用schema_name使用模式限定事件名称。event_name语法。

The DEFINER clause specifies the MySQL account to be used when checking access privileges at event execution time. If the DEFINER clause is present, the user value should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER, or CURRENT_USER(). The permitted user values depend on the privileges you hold, as discussed in Section 25.6, “Stored Object Access Control”. Also see that section for additional information about event security.

DEFINER子句指定在事件执行时检查访问权限时使用的MySQL帐户。如果存在DEFINER子句，用户值应该是一个MySQL帐户，指定为'user_name'@'host_name'， CURRENT_USER，或CURRENT_USER()。允许的用户值取决于您拥有的特权，如第25.6节“存储对象访问控制”中所讨论的。有关事件安全性的其他信息，请参见该部分。

If the DEFINER clause is omitted, the default definer is the user who executes the CREATE EVENT statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

如果省略了DEFINER子句，则默认定义器是执行CREATE EVENT语句的用户。这与显式指定DEFINER = CURRENT_USER相同。

Within an event body, the CURRENT_USER function returns the account used to check privileges at event execution time, which is the DEFINER user. For information about user auditing within events, see Section 6.2.23, “SQL-Based Account Activity Auditing”.

在事件体中，CURRENT_USER函数返回事件执行时用于检查特权的帐户，即DEFINER用户。有关事件内部用户审计的信息，请参见6.2.23节“基于sql的帐户活动审计”。

IF NOT EXISTS has the same meaning for CREATE EVENT as for CREATE TABLE: If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.)

对于CREATE EVENT, IF NOT EXISTS的含义与CREATE TABLE相同:如果名为event_name的事件已经存在于同一模式中，则不采取任何操作，也不会产生错误结果。(但是，在这种情况下会生成一个警告。)

The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms:

ON SCHEDULE子句确定为事件定义的event_body在何时、多长时间重复。这个条款有两种形式:

• AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value. You may use a value of either the DATETIME or TIMESTAMP type for this purpose. If the date is in the past, a warning occurs, as shown here: AT时间戳用于一次性事件。它指定事件只在时间戳给出的日期和时间执行一次，时间戳必须同时包含日期和时间，或者必须是解析为datetime值的表达式。为此目的，您可以使用DATETIME或TIMESTAMP类型的值。如果日期在过去，则会出现警告，如下所示:

  mysql> SELECT NOW();
  +---------------------+
  | NOW()               |
  +---------------------+
  | 2006-02-10 23:59:01 |
  +---------------------+
  1 row in set (0.04 sec)
  
  mysql> CREATE EVENT e_totals
      ->     ON SCHEDULE AT '2006-02-10 23:59:00'
      ->     DO INSERT INTO test.totals VALUES (NOW());
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  
  mysql> SHOW WARNINGS\G
  *************************** 1. row ***************************
    Level: Note
     Code: 1588
  Message: Event execution time is in the past and ON COMPLETION NOT
           PRESERVE is set. The event was dropped immediately after
           creation.

  CREATE EVENT statements which are themselves invalid—for whatever reason—fail with an error.

• You may use CURRENT_TIMESTAMP to specify the current date and time. In such a case, the event acts as soon as it is created. 您可以使用CURRENT_TIMESTAMP来指定当前日期和时间。在这种情况下，事件一旦创建就立即执行。

  To create an event which occurs at some point in the future relative to the current date and time—such as that expressed by the phrase “three weeks from now”—you can use the optional clause + INTERVAL interval. The interval portion consists of two parts, a quantity and a unit of time, and follows the syntax rules described in Temporal Intervals, except that you cannot use any units keywords that involving microseconds when defining an event. With some interval types, complex time units may be used. For example, “two minutes and ten seconds” can be expressed as + INTERVAL '2:10' MINUTE_SECOND. 要创建一个发生在相对于当前日期和时间的未来某个时间点的事件——比如短语“three weeks from now”——你可以使用可选子句+ INTERVAL INTERVAL。interval部分由两个部分组成，一个数量和一个时间单位，遵循Temporal interval中描述的语法规则，但是在定义事件时不能使用任何涉及微秒的单位关键字。对于某些间隔类型，可以使用复杂的时间单位。例如，“two minutes and ten seconds”可以表示为+ INTERVAL '2:10' MINUTE_SECOND。

  You can also combine intervals. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY is equivalent to “three weeks and two days from now”. Each portion of such a clause must begin with + INTERVAL. 您还可以合并间隔。例如，AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY等于“从现在算起的三周零两天”。这样的子句的每个部分必须以+ INTERVAL开头。

• To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means “every six weeks”. 若要定期重复操作，请使用EVERY子句。EVERY关键字后面跟着一个interval，正如前面讨论AT关键字时所描述的那样。(+ INTERVAL不用于EVERY。)例如，EVERY 6 WEEK意为“每六周”。

  Although + INTERVAL clauses are not permitted in an EVERY clause, you can use the same complex time units permitted in a + INTERVAL. 尽管在EVERY子句中不允许使用+ INTERVAL子句，但您可以使用与+ INTERVAL子句中相同的复杂时间单位。

  An EVERY clause may contain an optional STARTS clause. STARTS is followed by a timestamp value that indicates when the action should begin repeating, and may also use + INTERVAL interval to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means “every three months, beginning one week from now”. Similarly, you can express “every two weeks, beginning six hours and fifteen minutes from now” as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP—that is, the action specified for the event begins repeating immediately upon creation of the event. 每个子句可以包含一个可选的start子句。STARTS后面跟着一个时间戳值，指示动作何时开始重复，也可以使用+ INTERVAL INTERVAL来指定“从现在开始”的时间量。例如，每3个月开始CURRENT_TIMESTAMP + INTERVAL 1 WEEK表示“每三个月，从现在开始的一周开始”。类似地，你可以将“每两周，从现在开始的6小时15分钟”表示为每2周开始CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE。不指定STARTS与使用STARTS current_timestamp相同，也就是说，为事件指定的操作在事件创建后立即开始重复。

  An EVERY clause may contain an optional ENDS clause. The ENDS keyword is followed by a timestamp value that tells MySQL when the event should stop repeating. You may also use + INTERVAL interval with ENDS; for instance, EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK is equivalent to “every twelve hours, beginning thirty minutes from now, and ending four weeks from now”. Not using ENDS means that the event continues executing indefinitely. EVERY子句可以包含可选的ENDS子句。ENDS关键字后面跟着一个时间戳值，告诉MySQL事件应该在什么时候停止重复。你也可以使用+ INTERVAL INTERVAL与ENDS;例如，每12小时开始CURRENT_TIMESTAMP + INTERVAL 30分钟结束CURRENT_TIMESTAMP + INTERVAL 4周相当于“每12小时，从现在开始30分钟，从现在开始4周结束”。不使用ENDS意味着事件将无限期地继续执行。

  ENDS supports the same syntax for complex time units as STARTS does. 对于复杂时间单位，ENDS支持与start相同的语法。

  You may use STARTS, ENDS, both, or neither in an EVERY clause. 你可以在EVERY子句中使用start, ENDS, both或neither。

  If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking. 如果一个重复事件没有在其调度间隔内终止，结果可能是多个事件实例同时执行。如果不希望出现这种情况，您应该建立一种机制来防止并发实例。例如，可以使用GET_LOCK()函数或行或表锁。

The ON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp or interval values which it contains. You may not use stored functions or loadable functions in such expressions, nor may you use any table references; however, you may use SELECT FROM DUAL. This is true for both CREATE EVENT and ALTER EVENT statements. References to stored functions, loadable functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830).

ON SCHEDULE子句可以使用包含内置MySQL函数和用户变量的表达式来获取它所包含的时间戳或时间间隔值。您不能在此类表达式中使用存储函数或可加载函数，也不能使用任何表引用;但是，您可以使用SELECT FROM DUAL。对于CREATE EVENT和ALTER EVENT语句都是这样。在这种情况下，对存储函数、可加载函数和表的引用是不允许的，并且会以错误的形式失败(参见Bug #22830)。

Times in the ON SCHEDULE clause are interpreted using the current session time_zone value. This becomes the event time zone; that is, the time zone that is used for event scheduling and is in effect within the event as it executes. These times are converted to UTC and stored along with the event time zone internally. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. For additional information about representation of event times, see Section 25.4.4, “Event Metadata”. See also Section 13.7.7.18, “SHOW EVENTS Statement”, and Section 26.3.14, “The INFORMATION_SCHEMA EVENTS Table”.

ON SCHEDULE子句中的时间使用当前会话time_zone值解释。这就变成了事件时区;也就是说，用于事件调度并在事件执行时在事件中生效的时区。这些时间被转换为UTC，并在内部与事件时区一起存储。这使得事件执行可以按照定义继续进行，而不管后续对服务器时区或夏令时的任何更改。

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit.

通常，一旦事件过期，它将立即被删除。您可以通过指定ON COMPLETION PRESERVE来覆盖此行为。使用ON COMPLETION NOT PRESERVE仅仅使默认的非持久行为显式。

You can create an event but prevent it from being active using the DISABLE keyword. Alternatively, you can use ENABLE to make explicit the default status, which is active. This is most useful in conjunction with ALTER EVENT (see Section 13.1.3, “ALTER EVENT Statement”).

您可以创建一个事件，但使用DISABLE关键字阻止它处于活动状态。或者，您可以使用ENABLE显式设置默认状态，默认状态是活动的。这在与ALTER EVENT一起使用时最有用(参见13.1.3节“ALTER EVENT Statement”)。

A third value may also appear in place of ENABLE or DISABLE; DISABLE ON SLAVE is set for the status of an event on a replica to indicate that the event was created on the replication source server and replicated to the replica, but is not executed on the replica. See Section 17.5.1.16, “Replication of Invoked Features”.

第三个值也可以出现在ENABLE或DISABLE的位置;为副本上的事件设置DISABLE ON SLAVE状态，以指示该事件在复制源服务器上创建并复制到副本，但不在副本上执行。参见17.5.1.16节，“被调用特性的复制”。

You may supply a comment for an event using a COMMENT clause. comment may be any string of up to 64 characters that you wish to use for describing the event. The comment text, being a string literal, must be surrounded by quotation marks.

：您可以使用comment子句为事件提供注释。注释可以是您希望用于描述事件的任何最多64个字符的字符串。注释文本是字符串文字，必须用引号括起来。

The DO clause specifies an action carried by the event, and consists of an SQL statement. Nearly any valid MySQL statement that can be used in a stored routine can also be used as the action statement for a scheduled event. (See Section 25.8, “Restrictions on Stored Programs”.) For example, the following event e_hourly deletes all rows from the sessions table once per hour, where this table is part of the site_activity schema:

DO子句指定由事件携带的动作，由SQL语句组成。几乎所有可以在存储例程中使用的有效MySQL语句都可以用作计划事件的动作语句。(参见第25.8节“存储程序的限制”)例如，下面的事件e_hour每小时删除一次会话表中的所有行，其中该表是site_activity模式的一部分:

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

MySQL stores the sql_mode system variable setting in effect when an event is created or altered, and always executes the event with this setting in force, regardless of the current server SQL mode when the event begins executing.

MySQL存储sql_mode系统变量设置，当事件被创建或修改时生效，并且总是执行该设置生效的事件，而不管事件开始执行时当前服务器的SQL模式是什么。

A CREATE EVENT statement that contains an ALTER EVENT statement in its DO clause appears to succeed; however, when the server attempts to execute the resulting scheduled event, the execution fails with an error.

在DO子句中包含ALTER EVENT语句的CREATE EVENT语句似乎成功;但是，当服务器尝试执行产生的计划事件时，执行失败并出现错误。

Note

Statements such as SELECT or SHOW that merely return a result set have no effect when used in an event; the output from these is not sent to the MySQL Monitor, nor is it stored anywhere. However, you can use statements such as SELECT ... INTO and INSERT INTO ... SELECT that store a result. (See the next example in this section for an instance of the latter.)

仅返回结果集的SELECT或SHOW语句在事件中使用时没有效果;它们的输出不会发送到MySQL Monitor，也不会存储在任何地方。但是，您可以使用SELECT…INTO和INSERT INTO…选择存储结果的那个。(请参阅本节中的下一个示例，了解后者的实例。)

The schema to which an event belongs is the default schema for table references in the DO clause. Any references to tables in other schemas must be qualified with the proper schema name.

As with stored routines, you can use compound-statement syntax in the DO clause by using the BEGIN and END keywords, as shown here:

delimiter |

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

delimiter ;

This example uses the delimiter command to change the statement delimiter. See Section 25.1, “Defining Stored Programs”.

More complex compound statements, such as those used in stored routines, are possible in an event. This example uses local variables, an error handler, and a flow control construct:

delimiter | CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET v = 0; WHILE v < 5 DO INSERT INTO t1 VALUES (0); UPDATE t2 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END | delimiter ;

There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters within an event:

CREATE EVENT e_call_myproc
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO CALL myproc(5, 27);

If an event's definer has privileges sufficient to set global system variables (see Section 5.1.9.1, “System Variable Privileges”), the event can read and write global variables. As granting such privileges entails a potential for abuse, extreme care must be taken in doing so.

Generally, any statements that are valid in stored routines may be used for action statements executed by events. For more information about statements permissible within stored routines, see Section 25.4.2, “Event Scheduler Configuration”. You can create an event as part of a stored routine, but an event cannot be created by another event.