Movatterモバイル変換

MySQL 9.4 Reference Manual

15.1.15 CREATE EVENT Statement

CREATE    [DEFINER =user]    EVENT    [IF NOT EXISTS]event_name    ON SCHEDULEschedule    [ON COMPLETION [NOT] PRESERVE]    [ENABLE | DISABLE | DISABLE ON {REPLICA | SLAVE}]    [COMMENT 'string']    DOevent_bodyschedule: {    ATtimestamp [+ INTERVALinterval] ...  | EVERYinterval    [STARTStimestamp [+ INTERVALinterval] ...]    [ENDStimestamp [+ INTERVALinterval] ...]}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, seeSection 27.5.2, “Event Scheduler Configuration”.

CREATE EVENT requires theEVENT privilege for the schema in which the event is to be created. If theDEFINER clause is present, the privileges required depend on theuser value, as discussed inSection 27.8, “Stored Object Access Control”.

The minimum requirements for a validCREATE EVENT statement are as follows:

The keywordsCREATE EVENT plus an event name, which uniquely identifies the event in a database schema.
AnON SCHEDULE clause, which determines when and how often the event executes.
ADO clause, which contains the SQL statement to be executed by an event.

This is an example of a minimalCREATE EVENT statement:

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 namedmyevent. This event executes once—one hour following its creation—by running an SQL statement that increments the value of themyschema.mytable table'smycol column by 1.

Theevent_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 namedmyevent andMyEvent in the same schema. In general, the rules governing event names are the same as those for names of stored routines. SeeSection 11.2, “Schema Object Names”.

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

TheDEFINER clause specifies the MySQL account to be used when checking access privileges at event execution time. If theDEFINER clause is present, theuser value should be a MySQL account specified as'user_name'@'host_name',CURRENT_USER, orCURRENT_USER(). The permitteduser values depend on the privileges you hold, as discussed inSection 27.8, “Stored Object Access Control”. Also see that section for additional information about event security.

If theDEFINER clause is omitted, the default definer is the user who executes theCREATE EVENT statement. This is the same as specifyingDEFINER = CURRENT_USER explicitly.

Within an event body, theCURRENT_USER function returns the account used to check privileges at event execution time, which is theDEFINER user. For information about user auditing within events, seeSection 8.2.23, “SQL-Based Account Activity Auditing”.

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

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

ATtimestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given bytimestamp, 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 theDATETIME orTIMESTAMP type for this purpose. If the date is in the past, a warning occurs, as shown here:
```
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: 1588Message: 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 useCURRENT_TIMESTAMP to specify the current date and time. In such a case, the event acts as soon as it is created.
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+ INTERVALinterval. Theinterval portion consists of two parts, a quantity and a unit of time, and follows the syntax rules described inTemporal 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.
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.
To repeat actions at a regular interval, use anEVERY clause. TheEVERY keyword is followed by aninterval as described in the previous discussion of theAT keyword. (+ INTERVAL isnot used withEVERY.) For example,EVERY 6 WEEK means“every six weeks”.
Although+ INTERVAL clauses are not permitted in anEVERY clause, you can use the same complex time units permitted in a+ INTERVAL.
AnEVERY clause may contain an optionalSTARTS clause.STARTS is followed by atimestamp value that indicates when the action should begin repeating, and may also use+ INTERVALinterval 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” asEVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE. Not specifyingSTARTS is the same as usingSTARTS CURRENT_TIMESTAMP—that is, the action specified for the event begins repeating immediately upon creation of the event.
AnEVERY clause may contain an optionalENDS clause. TheENDS keyword is followed by atimestamp value that tells MySQL when the event should stop repeating. You may also use+ INTERVALinterval withENDS; 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 usingENDS means that the event continues executing indefinitely.
ENDS supports the same syntax for complex time units asSTARTS does.
You may useSTARTS,ENDS, both, or neither in anEVERY clause.
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 theGET_LOCK() function, or row or table locking.

TheON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of thetimestamp orinterval 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 useSELECT FROM DUAL. This is true for bothCREATE EVENT andALTER 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).

Times in theON SCHEDULE clause are interpreted using the current sessiontime_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, seeSection 27.5.4, “Event Metadata”. See alsoSection 15.7.7.20, “SHOW EVENTS Statement”, andSection 28.3.14, “The INFORMATION_SCHEMA EVENTS Table”.

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

You can create an event but prevent it from being active using theDISABLE keyword. Alternatively, you can useENABLE to make explicit the default status, which is active. This is most useful in conjunction withALTER EVENT (seeSection 15.1.3, “ALTER EVENT Statement”).

A third value may also appear in place ofENABLE orDISABLE;DISABLE ON REPLICA 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. SeeSection 19.5.1.16, “Replication of Invoked Features”.

DISABLE ON REPLICA replacesDISABLE ON SLAVE, which is deprecated, and thus subject to removal in a future version of MySQL.

You may supply a comment for an event using aCOMMENT 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.

TheDO 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. (SeeSection 27.10, “Restrictions on Stored Programs”.) For example, the following evente_hourly deletes all rows from thesessions table once per hour, where this table is part of thesite_activity schema:

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

MySQL stores thesql_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.

ACREATE EVENT statement that contains anALTER EVENT statement in itsDO clause appears to succeed; however, when the server attempts to execute the resulting scheduled event, the execution fails with an error.

Note

Statements such asSELECT orSHOW 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 asSELECT ... INTO andINSERT INTO ... SELECT that store a result. (See the next example in this section for an instance of the latter.)

The schema to which an event belongs is the default schema for table references in theDO 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 theDO clause by using theBEGIN andEND 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 thedelimiter command to change the statement delimiter. SeeSection 27.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);

In MySQL 9.4, aCREATE EVENT statement can be prepared, but the statement text must not contain any placeholders (?). One way to get around this restriction is to assemble the text of the statement, prepare it, and execute it within a stored procedure; variable parts of theCREATE EVENT statement can be passed into the stored procedure as parameters. We demonstrate this in the following example, which assumes that there already exists a tablet in databased created as shown here:

USE d;CREATE TABLE t (  c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  c2 VARCHAR(20),  c3 INT);

We wish to create an event that inserts rows into this table at intervals determined at creation time, similar to the event defined by the statement shown here:

CREATE EVENT e   ON SCHEDULE EVERYinterval SECOND  STARTS CURRENT_TIMESTAMP + INTERVAL 10 SECOND  ENDS CURRENT_TIMESTAMP + INTERVAL 2 MINUTE  ON COMPLETION PRESERVE  DO    INSERT INTO d.t1 VALUES ROW(NULL, NOW(), FLOOR(RAND()*100));

We cannot use? as a placeholder forinterval, but we can pass a parameter value to a stored procedure like this one:

delimiter |CREATE PROCEDURE sp(n INT)BEGIN  SET @s1 = "CREATE EVENT e ON SCHEDULE EVERY ";  SET @s2 = " SECOND       STARTS CURRENT_TIMESTAMP + INTERVAL 10 SECOND       ENDS CURRENT_TIMESTAMP + INTERVAL 2 MINUTE       ON COMPLETION PRESERVE       DO       INSERT INTO d.t VALUES ROW(NULL, NOW(), FLOOR(RAND()*100))";    SET @s = CONCAT(@s1, n, @s2);  PREPARE ps FROM @s;  EXECUTE ps;  DEALLOCATE PREPARE ps;END |delimiter ;

mysql> TABLE t;Empty set (0.00 sec)mysql> CALL sp(5);Query OK, 0 rows affected (0.01 sec)# Wait 2 minutes...mysql> TABLE t;+----+---------------------+------+| c1 | c2                  | c3   |+----+---------------------+------+|  1 | 2024-06-12 15:53:36 |   41 ||  2 | 2024-06-12 15:53:41 |   84 ||  3 | 2024-06-12 15:53:46 |   71 ||  4 | 2024-06-12 15:53:51 |   78 ||  5 | 2024-06-12 15:53:56 |   53 ||  6 | 2024-06-12 15:54:01 |    6 ||  7 | 2024-06-12 15:54:06 |   48 ||  8 | 2024-06-12 15:54:11 |   98 ||  9 | 2024-06-12 15:54:16 |   22 || 10 | 2024-06-12 15:54:21 |   88 || 11 | 2024-06-12 15:54:26 |   53 || 12 | 2024-06-12 15:54:31 |   75 || 13 | 2024-06-12 15:54:36 |   93 || 14 | 2024-06-12 15:54:41 |   13 || 15 | 2024-06-12 15:54:46 |   62 || 16 | 2024-06-12 15:54:51 |   47 || 17 | 2024-06-12 15:54:56 |   22 || 18 | 2024-06-12 15:55:01 |   47 || 19 | 2024-06-12 15:55:06 |   43 || 20 | 2024-06-12 15:55:11 |   50 || 21 | 2024-06-12 15:55:16 |   98 || 22 | 2024-06-12 15:55:21 |   15 || 23 | 2024-06-12 15:55:26 |   56 |+----+---------------------+------+23 rows in set (0.00 sec)

After invokingsp with the argument value5, as shown, and waiting 2 minutes until evente has completed its run, we can see that tablet was updated every 5 seconds. Sincee was created withON COMPLETION PRESERVE, we can see it in Information SchemaEVENTS table and verify that it was created as expected:

mysql> SELECT EVENT_NAME, EVENT_SCHEMA, EVENT_DEFINITION, EVENT_TYPE      > FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='e'\G*************************** 1. row ***************************      EVENT_NAME: e    EVENT_SCHEMA: dEVENT_DEFINITION: INSERT INTO d.t VALUES ROW(NULL, NOW(), FLOOR(RAND()*100))      EVENT_TYPE: RECURRING1 row in set (0.00 sec)

If an event's definer has privileges sufficient to set global system variables (seeSection 7.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, seeSection 27.2.1, “Stored Routine Syntax”. It is not possible to create an event as part of a stored routine or to create an event by another event.

PREV HOME UP NEXT