CREATE EVENT

Use this MySQL statement to schedule the execution of an SQL statement at a specific time and date.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [DEFINER = {'user'@'host'|CURRENT_USER}] EVENT
[IF NOT EXISTS] event
ON SCHEDULE
AT timestamp [+ INTERVAL count interval] |
EVERY count interval
   [STARTS timestamp [+ INTERVAL count interval]]
   [ENDS timestamp [+ INTERVAL count interval]]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE|DISABLE|DISABLE ON SLAVE]
[COMMENT 'comment']
DO statement

Explanation

Use this statement to schedule the execution of an SQL statement at a specific time and date. Events may also be recurring. Although there are many options, the basic syntax is:

CREATE EVENT event ON SCHEDULE AT timestamp DO statement

The event name you give may be any non-reserved word and is case-insensitive. The DO clause can include any SQL statement to be executed. A stored procedure can be passed here to conveniently execute a set of SQL statements.

With the DEFINER clause, you can specify the MySQL user and host to be used by MySQL for the event. This means that the event may be created by a user with SUPER privilege, but executed by another user account in which privileges are limited for security reasons. The IF NOT EXISTS clause may be given to prevent errors from being returned if the event has already been created.

For the required ON SCHEDULE AT clause, include a specific time and date in the timestamp format (yyyy-mm-dd hh:mm:ss). The time given can be a string, a time function, or just CURRENT_TIMESTAMP. You can also specify to a time relative to the timestamp given by adding a plus sign followed by the keyword INTERVAL, the number of intervals (e.g., 1) and then the interval increment (e.g., HOUR). For interval, use one of the allowable intervals: SECOND, MINUTE, MINUTE_SECOND, HOUR, HOUR_SECOND, HOUR_MINUTE, DAY, DAY_SECOND, DAY_MINUTE, DAY_HOUR, WEEK, MONTH, QUARTER, YEAR, or YEAR_MONTH.

To make the event a recurring one, add the EVERY clause, using the same syntax and format. You can also give starting and ending times for a repeating event with the STARTS and ENDS clauses.

Once an event is completed, it will be dropped automatically. However, you can drop it manually before completion with the DROP EVENT statement. You can also keep the server from dropping an event by adding the ON COMPLETION clause with the PRESERVE keyword. The NOT PRESERVE keyword instructs the server not to retain the event when completed, and is the server's default behavior.

When creating an event, you may want to create it with the DISABLE parameter so that it won't begin to execute until you enable it. Then use the ALTER EVENT statement to enable it later. The DISABLE ON SLAVE keyword will disable the event from running on slave servers. By default, an event runs on both the master and all slaves.

With the COMMENT clause you can add a comment describing the event for future reference. This comment is displayed only when SHOW CREATE EVENT is executed for the event.

Examples

An example using this statement follows. It schedules a procedure that is created in the example under the CREATE PROCEDURE statement:

CREATE EVENT students_copy
ON SCHEDULE EVERY 1 DAY
STARTS '2007-11-27 02:30:00'
ON COMPLETION PRESERVE
COMMENT 'Daily copy of students table to students_backup'
DO CALL students_copy_proc();

In this example, the event will be run once a day starting from the time given and then every day afterwards at the same time (2:30 A.M.). It's set to be recurring, but in case someone ever changes that aspect of it, MySQL will preserve the event upon completion. We've added a comment to explain the purpose of the event. Use ALTER EVENT to change an event and SHOW EVENTS to get a list of events.