Since version 5.1.6, MySQL has supported events. They employ a natural language scheduling syntax, so that you can say: "I want the MySQL server to execute this SQL statement every day at 11:30am, until the end of the year". To help you write your event statements, MySQL provides excellent documentation on CREATE EVENT syntax. Despite all of this, getting a firm grasp of event scheduling can still take some trial and error.

There are some challenges inherent to events, such as making an event recur, and making it run at a given time. Moreover, rather than having an event that just runs once or forever, you can also schedule a recurring event that is valid only within a specific time period, using START and END clauses.

In today's blog, we'll explore the syntax to create events to run according to various schedules, from very simple, to more complex.

Scheduling Parameters

An event's "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.

The possibilities are:

• AT timestamp [+ interval integer_value time_keyword ]
• EVERY interval
• EVERY interval STARTS timestamp
• EVERY interval ENDS timestamp
• EVERY interval STARTS timestamp ENDS timestamp

Here are two examples using the "AT timestamp" clause.

This event makes the MySQL server drop a table exactly 5 days from now:

CREATE EVENT 'My event'
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
 DO DROP TABLE t;

This event makes the MySQL server drop a table on February 24, 2018 at exactly 12 o'clock:

CREATE EVENT The_Main_Event
 ON SCHEDULE AT TIMESTAMP '2018-02-24 12:00:00'
 DO DROP TABLE t;

EVERY interval means "Do this repeatedly". A recurring interval starts with EVERY, followed by a positive integer plus an INTERVAL interval, as we saw in the last blog.

For example, this event makes MySQL drop table t once each year, starting now:

CREATE EVENT e ON SCHEDULE EVERY 1 YEAR DO DROP TABLE t;

The STARTS and ENDS Clauses

An EVERY clause may contain an optional STARTS and/or ENDS clause.

STARTS is followed by a timestamp value that indicates when the action should begin repeating, and may also use + INTERVAL interval to specify an amount of time "from now". Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP so that the event begins repeating immediately upon creation of the event.

An EVERY clause may also contain an ENDS clause. The ENDS keyword is followed by a timestamp value that tells MySQL when the event should stop repeating. Not using ENDS means that the event continues executing indefinitely.

"EVERY interval [ STARTS timestamp1 ] [ ENDS timestamp2 ]" means "Do this repeatedly, starting at timestamp1 if it's specified, ending at timestamp2 if it's specified". For example, this event tells the database to drop a table once each year, starting exactly 3 days from now:

CREATE EVENT evt
 ON SCHEDULE EVERY 1 YEAR
   STARTS CURRENT_TIMESTAMP + INTERVAL 3 DAY
 DO DROP TABLE t;

This event would cause MySQL to drop a table once each year for five years, starting exactly 2 days from now:

CREATE EVENT e
 ON SCHEDULE EVERY 1 YEAR
   STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
   ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR
 DO DROP TABLE t;

Now that we've gained an understanding of scheduling events, in the next blog we'll create some events using Navicat Premium.