Rechercher dans le manuel MySQL

25.10 The INFORMATION_SCHEMA EVENTS Table

Table des matières

The EVENTS table provides information about Event Manager events, which are discussed in Section 24.4, “Using the Event Scheduler”.

The EVENTS table has these columns:

  • EVENT_CATALOG

    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.

  • DEFINER

    The account of the user who created the event, in 'user_name'@'host_name' format.

  • TIME_ZONE

    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

    The language used for the statements in the event's DO clause. The value is always SQL.

  • EVENT_DEFINITION

    The text of the SQL statement making up the event's DO clause; in other words, the statement executed by this event.

  • EVENT_TYPE

    The event repetition type, either ONE TIME (transient) or RECURRING (repeating).

  • EXECUTE_AT

    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

    For a recurring event, the number of intervals to wait between event executions. For a transient event, the value is always NULL.

  • INTERVAL_FIELD

    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

    The SQL mode in effect when the event was created or altered, and under which the event executes. For the permitted values, see Section 5.1.11, “Server SQL Modes”.

  • STARTS

    The 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

  • ENDS

    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.

  • STATUS

    The event status. One of ENABLED, DISABLED, or SLAVESIDE_DISABLED. SLAVESIDE_DISABLED indicates that the creation of the event occurred on another MySQL server acting as a replication master and replicated to the current MySQL server which is acting as a slave, but the event is not presently being executed on the slave. For more information, see Section 17.4.1.16, “Replication of Invoked Features”. information.

  • ON_COMPLETION

    One of the two values PRESERVE or NOT PRESERVE.

  • CREATED

    The date and time when the event was created. This is a TIMESTAMP value.

  • LAST_ALTERED

    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

    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 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

    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 master server. The default value is 0.

  • CHARACTER_SET_CLIENT

    The session value of the character_set_client system variable when the event was created.

  • 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

Table des matières Haut

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:

  1. DELIMITER |
  2.  
  3. CREATE EVENT e_daily
  4.     ON SCHEDULE
  5.       EVERY 1 DAY
  6.     COMMENT 'Saves total number of sessions then clears the table each day'
  7.     DO
  8.       BEGIN
  9.         INSERT INTO site_activity.totals (time, total)
  10.           SELECT CURRENT_TIMESTAMP, COUNT(*)
  11.             FROM site_activity.sessions;
  12.         DELETE FROM site_activity.sessions;
  13.       END |
  14.  
  15. DELIMITER ;
  16.  
  17. ALTER EVENT e_daily
  18.     ENABLE;

(Note that comments can span multiple lines.)

This user can then run the following SELECT statement, and obtain the output shown:

  1. mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
  2.        WHERE EVENT_NAME = 'e_daily'
  3.        AND EVENT_SCHEMA = 'myschema'\G
  4. *************************** 1. row ***************************
  5.        EVENT_CATALOG: def
  6.         EVENT_SCHEMA: myschema
  7.           EVENT_NAME: e_daily
  8.              DEFINER: jon@ghidora
  9.            TIME_ZONE: SYSTEM
  10.           EVENT_BODY: SQL
  11.     EVENT_DEFINITION: BEGIN
  12.         INSERT INTO site_activity.totals (time, total)
  13.           SELECT CURRENT_TIMESTAMP, COUNT(*)
  14.             FROM site_activity.sessions;
  15.         DELETE FROM site_activity.sessions;
  16.       END
  17.           EVENT_TYPE: RECURRING
  18.           EXECUTE_AT: NULL
  19.       INTERVAL_VALUE: 1
  20.       INTERVAL_FIELD: DAY
  21.             SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
  22.                       NO_ZERO_IN_DATE,NO_ZERO_DATE,
  23.                       ERROR_FOR_DIVISION_BY_ZERO,
  24.                       NO_ENGINE_SUBSTITUTION
  25.               STARTS: 2018-08-08 11:06:34
  26.                 ENDS: NULL
  27.               STATUS: ENABLED
  28.        ON_COMPLETION: NOT PRESERVE
  29.              CREATED: 2018-08-08 11:06:34
  30.         LAST_ALTERED: 2018-08-08 11:06:34
  31.        LAST_EXECUTED: 2018-08-08 16:06:34
  32.        EVENT_COMMENT: Saves total number of sessions then clears the
  33.                       table each day
  34.           ORIGINATOR: 1
  35. CHARACTER_SET_CLIENT: utf8mb4
  36. COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  37.   DATABASE_COLLATION: utf8mb4_0900_ai_ci

Event information is also available from the SHOW EVENTS statement. See Section 13.7.6.18, “SHOW EVENTS Syntax”. The following statements are equivalent:

  1. SELECT
  2.     EVENT_SCHEMA, EVENT_NAME, DEFINER, TIME_ZONE, EVENT_TYPE, EXECUTE_AT,
  3.     INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ORIGINATOR,
  4.     CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION
  5.   FROM INFORMATION_SCHEMA.EVENTS
  6.   WHERE table_schema = 'db_name'
  7.   [AND column_name LIKE 'wild']
  8.  
  9. SHOW EVENTS
  10.   [FROM db_name]
  11.   [LIKE 'wild']

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-events-table.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut