MySQL in a Nutshell, 2nd Edition

Name

CREATE EVENT

Synopsis

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

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 nonreserved 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 privileges 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 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; this 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 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.

Here is an example using this statement. It schedules a procedure that is created in the example under the CREATE PROCEDURE statement later in this chapter:

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

Previous Chapter

Next Chapter

CREATE FUNCTION

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition