Spec-Zone .ru
спецификации, руководства, описания, API

20.7. The INFORMATION_SCHEMA EVENTS Table

The EVENTS table provides information about scheduled events, which are discussed in Section 19.4, "Using the Event Scheduler". The SHOW Name values correspond to column names of the SHOW EVENTS statement.

INFORMATION_SCHEMA Name SHOW Name Remarks
EVENT_CATALOG def, MySQL extension
EVENT_SCHEMA Db MySQL extension
EVENT_NAME Name MySQL extension
DEFINER Definer MySQL extension
TIME_ZONE Time zone MySQL extension
EVENT_BODY MySQL extension
EVENT_DEFINITION MySQL extension
EVENT_TYPE Type MySQL extension
EXECUTE_AT Execute at MySQL extension
INTERVAL_VALUE Interval value MySQL extension
INTERVAL_FIELD Interval field MySQL extension
SQL_MODE MySQL extension
STARTS Starts MySQL extension
ENDS Ends MySQL extension
STATUS Status MySQL extension
ON_COMPLETION MySQL extension
CREATED MySQL extension
LAST_ALTERED MySQL extension
LAST_EXECUTED MySQL extension
EVENT_COMMENT MySQL extension
ORIGINATOR Originator MySQL extension
CHARACTER_SET_CLIENT character_set_client MySQL extension
COLLATION_CONNECTION collation_connection MySQL extension
DATABASE_COLLATION Database Collation MySQL extension

Notes:

Example: 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    ENABLED;

(Note that comments can span multiple lines.)

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: test          EVENT_NAME: e_daily             DEFINER: paul@localhost           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:              STARTS: 2008-09-03 12:13:39                ENDS: NULL              STATUS: ENABLED       ON_COMPLETION: NOT PRESERVE             CREATED: 2008-09-03 12:13:39        LAST_ALTERED: 2008-09-03 12:13:39       LAST_EXECUTED: NULL       EVENT_COMMENT: Saves total number of sessions then clears the                      table each day          ORIGINATOR: 1CHARACTER_SET_CLIENT: latin1COLLATION_CONNECTION: latin1_swedish_ci  DATABASE_COLLATION: latin1_swedish_ci

Times in the EVENTS table are displayed using the event time zone or the current session time zone, as described in Section 19.4.4, "Event Metadata".

See also Section 13.7.5.19, "SHOW EVENTS Syntax".