In MySQL we can create scheduled events, which are basically tasks that run according to a specified schedule.

We create scheduled events with the CREATE EVENT statement.

When we do this, we specify the schedule as well as the task to run.

Example

Here’s an example of the code required to create an event:

CREATE EVENT Event1
    ON SCHEDULE
        EVERY 1 MINUTE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

This is a simple event that inserts a row into an existing table.

The event is called Event1 and it runs every minute. When it runs it inserts a row in a table with the current date/time. This assumes that the table exists and it has columns called EventTimestamp and InsertedBy.

When we run that statement, the event runs immediately. Then it runs every minute from then on. Whether it runs immediately will depend on the schedule though. For example, it won’t run immediately if we specify that it should only run at a specific time in the future.

The IF NOT EXISTS Clause

We can use an IF NOT EXISTS clause to create the event only if it doesn’t exist. In other words it won’t try to create the event if another event already exists with the same name.

So we can modify the above code as follows:

CREATE EVENT IF NOT EXISTS Event1
    ON SCHEDULE
        EVERY 1 MINUTE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

We can run that code multiple times without error. If the event doesn’t exist, it will be created. If it does exist, it won’t throw an error, but a warning will be issued.

Event Scheduling

We have various options for scheduling each event.

Here’s the syntax for scheduling events:

AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

This means we have the option of specifying a specific timestamp, a repeating interval, or a repeating interval within a given window of time.

The interval part can be any of the following:

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

The interval part is preceded by the quantity. For example INTERVAL 5 WEEK.

Creating a Disabled Event

By default events are enabled when we create them. But we also have the option of setting them to disabled when we create them. To disable an event we use the DISABLE keyword.

Example:

CREATE EVENT Event1
    ON SCHEDULE
        EVERY 1 MINUTE
    DISABLE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

This event runs every minute but not yet. It will only start running once it’s been enabled (which we can do with the ALTER EVENT statement).

Here’s an example of enabling the event:

ALTER EVENT Event1
    ON SCHEDULE
        EVERY 1 MINUTE
    ENABLE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

Commenting Events

We can use the COMMENT clause to provide comments in the event:

ALTER EVENT Event1
    ON SCHEDULE
        EVERY 1 HOUR
    DISABLE
    COMMENT 'Insert timestamp into table'
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

The comment is a string literal and must be surrounded in quotes. It can be up to 64 characters long.

Persisting Expired Events

By default events are automatically dropped once they’ve expired. What I mean is that once the event has completed its schedule it is automatically dropped from the system. This is mainly relevant for events that are scheduled to run once in the future or multiple times within a specified time window in the future.

However we can override this by specifying ON COMPLETION PRESERVE. 

Example:

CREATE EVENT Event1
    ON SCHEDULE
        AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
    ON COMPLETION PRESERVE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

Now when this event has run, it won’t be dropped. It will remain in the system in disabled status.

Considerations for the sql_mode System Variable

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 effect, regardless of the current server SQL mode when the event begins executing.