Navicat Blog

Starting and Stopping MySQL 8 Events (Part 4)

Jul 17, 2018 by Robert Gravelle

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:

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: