文档首页
MySQL 8.4 参考手册
8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 67%

28.3.14 INFORMATION_SCHEMA EVENTS 表格28.3.14 The INFORMATION_SCHEMA EVENTS Table

EVENTS 表提供有关事件管理器事件的信息，这些内容在 第 27.4 节，“使用事件调度器” 中进行了讨论。The EVENTS table provides information about Event Manager events, which are discussed in Section 27.4, “Using the Event Scheduler”.

EVENTS 表具有以下列：The EVENTS table has these columns:

  • EVENT_CATALOG

    事件所属目录的名称。该值始终为 def。The name of the catalog to which the event belongs. This value is always def.

  • EVENT_SCHEMA

    事件所属模式（数据库）的名称。The name of the schema (database) to which the event belongs.

  • EVENT_NAME

    事件的名称。The name of the event.

  • DEFINERDEFINER

    DEFINER 子句中指定的账户（通常是创建事件的用户），格式为 'user_name'@'host_name'。The account named in the DEFINER clause (often the user who created the event), in 'user_name'@'host_name' format.

  • TIME_ZONE

    事件的时区，用于安排事件的时间，并在事件执行期间生效。默认值为 SYSTEM。The event time zone, which is the time zone used for scheduling the event and that is in effect within the event as it executes. The default value is SYSTEM.

  • EVENT_BODY

    事件 DO 子句中语句使用的语言。该值始终为 SQL。The language used for the statements in the event's DO clause. The value is always SQL.

  • EVENT_DEFINITION

    构成事件的 DO 子句的 SQL 语句文本；换句话说，该事件执行的语句。The text of the SQL statement making up the event's DO clause; in other words, the statement executed by this event.

  • EVENT_TYPE

    事件重复类型，可以是 ONE TIME（一次性）或 RECURRING（重复）。The event repetition type, either ONE TIME (transient) or RECURRING (repeating).

  • EXECUTE_AT

    对于一次性事件，这是在创建事件所使用的 CREATE EVENT 语句的 AT 子句中指定的 DATETIME 值，或最近修改该事件的 ALTER EVENT 语句中的值。本列显示的值会反映事件 AT 子句中包含的任何 INTERVAL 值的加减。例如，如果事件使用 ON SCHEDULE AT CURRENT_TIMESTAMP + '1:6' DAY_HOUR 创建，并且事件创建时间为 2018-02-09 14:05:30，则本列显示的值为 '2018-02-10 20:05:30'。如果事件的时间由 EVERY 子句而非 AT 子句确定（即事件是重复事件），则本列的值为 NULL。For a one-time event, this is the DATETIME value specified in the AT clause of the CREATE EVENT statement used to create the event, or of the last ALTER EVENT statement that modified the event. The value shown in this column reflects the addition or subtraction of any INTERVAL value included in the event's AT clause. For example, if an event is created using ON SCHEDULE AT CURRENT_TIMESTAMP + '1:6' DAY_HOUR, and the event was created at 2018-02-09 14:05:30, the value shown in this column would be '2018-02-10 20:05:30'. If the event's timing is determined by an EVERY clause instead of an AT clause (that is, if the event is recurring), the value of this column is NULL.

  • INTERVAL_VALUE

    对于重复事件，表示在事件执行之间需要等待的间隔数量。对于一次性事件，该值始终为NULL。For a recurring event, the number of intervals to wait between event executions. For a transient event, the value is always NULL.

  • INTERVAL_FIELD

    重复事件在重复之前等待的时间单位。对于一次性事件，该值始终为NULL。The time units used for the interval which a recurring event waits before repeating. For a transient event, the value is always NULL.

  • SQL_MODE

    事件创建或修改时生效的 SQL 模式，以及事件执行时所使用的模式。有关允许的值，请参见第 7.1.11 节，“服务器 SQL 模式”。The SQL mode in effect when the event was created or altered, and under which the event executes. For the permitted values, see Section 7.1.11, “Server SQL Modes”.

  • STARTSSTARTS

    循环事件的开始日期和时间。该值显示为DATETIME值，如果事件未定义开始日期和时间，则为NULL。对于瞬时事件，此列始终为NULL。对于定义中包含STARTS子句的循环事件，此列包含相应的DATETIME值。与EXECUTE_AT列一样，该值会解析所使用的任何表达式。如果没有影响事件时间的STARTS子句，此列为NULLThe start date and time for a recurring event. This is displayed as a DATETIME value, and is NULL if no start date and time are defined for the event. For a transient event, this column is always NULL. For a recurring event whose definition includes a STARTS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used. If there is no STARTS clause affecting the timing of the event, this column is NULL

  • ENDSENDS

    对于定义中包含 ENDS 子句的重复事件，此列包含相应的 DATETIME 值。与 EXECUTE_AT 列一样，此值会解析所使用的任何表达式。如果没有 ENDS 子句影响事件的时间，则此列为 NULL。For a recurring event whose definition includes a ENDS clause, this column contains the corresponding DATETIME value. As with the EXECUTE_AT column, this value resolves any expressions used. If there is no ENDS clause affecting the timing of the event, this column is NULL.

  • STATUSSTATUS

    事件状态。取值为 ENABLED、DISABLED 或 REPLICA_SIDE_DISABLED（在 MySQL 8.2.0 之前，此值为 SLAVESIDE_DISABLED，现已弃用）。REPLICA_SIDE_DISABLED 表示该事件是在作为复制源的另一台 MySQL 服务器上创建并复制到当前作为副本的 MySQL 服务器的，但该事件目前未在副本上执行。有关更多信息，请参见 第 19.5.1.16 节，“调用功能的复制”。The event status. One of ENABLED, DISABLED, or REPLICA_SIDE_DISABLED (prior to MySQL 8.2.0, this was SLAVESIDE_DISABLED, now deprecated). REPLICA_SIDE_DISABLED indicates that the creation of the event occurred on another MySQL server acting as a replication source and replicated to the current MySQL server which is acting as a replica, but the event is not presently being executed on the replica. For more information, see Section 19.5.1.16, “Replication of Invoked Features”. information.

  • ON_COMPLETION

    可取值为 PRESERVE 或 NOT PRESERVE 其中之一。One of the two values PRESERVE or NOT PRESERVE.

  • CREATED

    事件创建的日期和时间。这是一个 TIMESTAMP 值。The date and time when the event was created. This is a TIMESTAMP value.

  • LAST_ALTERED

    事件最后修改的日期和时间。这是一个 TIMESTAMP 值。如果事件自创建以来未被修改，则此值与 CREATED 值相同。The date and time when the event was last modified. This is a TIMESTAMP value. If the event has not been modified since its creation, this value is the same as the CREATED value.

  • LAST_EXECUTED

    事件最后执行的日期和时间。这是一个 DATETIME 值。如果事件从未执行过，则此列为 NULL。The date and time when the event last executed. This is a DATETIME value. If the event has never executed, this column is NULL.

    LAST_EXECUTED 表示事件的开始时间。因此，ENDS 列的值永远不会小于 LAST_EXECUTED。LAST_EXECUTED indicates when the event started. As a result, the ENDS column is never less than LAST_EXECUTED.

  • EVENT_COMMENT

    事件的注释文本（如果有）。如果没有，则该值为空。The text of the comment, if the event has one. If not, this value is empty.

  • ORIGINATOR

    创建事件的 MySQL 服务器的服务器 ID；用于复制。如果在复制源上执行 ALTER EVENT 语句，这个值可能会更新为执行该语句的服务器的服务器 ID。默认值为 0。The server ID of the MySQL server on which the event was created; used in replication. This value may be updated by ALTER EVENT to the server ID of the server on which that statement occurs, if executed on a replication source. The default value is 0.

  • CHARACTER_SET_CLIENT

    创建事件时 character_set_client 系统变量的会话值。The session value of the character_set_client system variable when the event was created.

  • COLLATION_CONNECTION

    创建事件时 collation_connection 系统变量的会话值。The session value of the collation_connection system variable when the event was created.

  • DATABASE_COLLATION

    事件关联的数据库的排序规则。The collation of the database with which the event is associated.

注意事项Notes

示例Example

假设用户 'jon'@'ghidora' 创建了一个名为 e_daily 的事件，然后在几分钟后使用 ALTER EVENT 语句对其进行修改，如下所示：Suppose that the user 'jon'@'ghidora' creates an event named e_daily, and then modifies it a few minutes later using an ALTER EVENT statement, 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 ;

ALTER EVENT e_daily
    ENABLE;

（注意，注释可以跨多行。）(Note that comments can span multiple lines.)

然后，该用户可以运行以下 SELECT 语句，并获得如下输出：This user can then run the following SELECT statement, and obtain the output shown:

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
       WHERE EVENT_NAME = 'e_daily'
       AND EVENT_SCHEMA = 'myschema'\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: myschema
          EVENT_NAME: e_daily
             DEFINER: jon@ghidora
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: DAY
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_ENGINE_SUBSTITUTION
              STARTS: 2018-08-08 11:06:34
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2018-08-08 11:06:34
        LAST_ALTERED: 2018-08-08 11:06:34
       LAST_EXECUTED: 2018-08-08 16:06:34
       EVENT_COMMENT: Saves total number of sessions then clears the
                      table each day
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  DATABASE_COLLATION: utf8mb4_0900_ai_ci

事件信息也可以通过 SHOW EVENTS 语句获取。参见 第 15.7.7.19 节，“SHOW EVENTS 语句”。以下语句是等效的：Event information is also available from the SHOW EVENTS statement. See Section 15.7.7.19, “SHOW EVENTS Statement”. The following statements are equivalent:

SELECT
    EVENT_SCHEMA, EVENT_NAME, DEFINER, TIME_ZONE, EVENT_TYPE, EXECUTE_AT,
    INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ORIGINATOR,
    CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE table_schema = 'db_name'
  [AND column_name LIKE 'wild']

SHOW EVENTS
  [FROM db_name]
  [LIKE 'wild']
首页   上一级   下一节
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 261.9Kb
Man Pages (Zip) - 367.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb