CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON {REPLICA | 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 27.5.2, “Event Scheduler Configuration”.

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 27.7, “Stored Object Access Control”.

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

This is an example of a minimal CREATE 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 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.

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 11.2, “Schema Object Names”.

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.

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 27.7, “Stored Object Access Control”. Also see that section for additional information about event security.

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.

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 8.2.23, “SQL-Based Account Activity Auditing”.

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

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:

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

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 27.5.4, “Event Metadata”. See also Section 15.7.7.20, “SHOW EVENTS Statement”, and Section 28.3.14, “The INFORMATION_SCHEMA EVENTS Table”.

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.

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 15.1.3, “ALTER EVENT Statement”).

A third value may also appear in place of ENABLE or DISABLE; 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. See Section 19.5.1.16, “Replication of Invoked Features”.

DISABLE ON REPLICA replaces DISABLE 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 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.

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 27.9, “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:

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.

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.

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 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.3, a CREATE 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 the CREATE 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 table t in database d 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 EVERY interval 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 for interval, 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 invoking sp with the argument value 5, as shown, and waiting 2 minutes until event e has completed its run, we can see that table t was updated every 5 seconds. Since e was created with ON COMPLETION PRESERVE, we can see it in Information Schema EVENTS 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: d
EVENT_DEFINITION: INSERT INTO d.t VALUES ROW(NULL, NOW(), FLOOR(RAND()*100))
      EVENT_TYPE: RECURRING
1 row in set (0.00 sec)

If an event's definer has privileges sufficient to set global system variables (see Section 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, see Section 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.