Key Takeaways
- MySQL events, introduced in MySQL 5.1.6, are temporal triggers that can be scheduled to run either once or at a recurring interval, offering an alternative to scheduled tasks and cron jobs. They can be used for tasks such as creating backups, deleting stale records, or aggregating data for reports.
- The MySQL event scheduler is a background process that constantly looks for events to execute. It can be started by issuing the command ‘SET GLOBAL event_scheduler = ON;’ and turned off with ‘SET GLOBAL event_scheduler = OFF;’. Its status can be viewed in MySQL’s process list.
- When creating an event, it’s important to note that the event can only perform actions for which the MySQL user that created the event has privileges to perform. Event names are restricted to a length of 64 characters and should be unique regardless of case. Events cannot be created, altered, or dropped by another event, and a stored function or user-defined function cannot be referenced when setting the event schedule.
- MySQL events can be used in practical scenarios like scheduling blog posts. By using MySQL events that will fire when the post is to be published, one can efficiently manage the publication of blog posts without the need for inefficient cron scripts. The event can be created at the time of adding the blog entry to the database, and if the blog post is edited later, the event can be deleted and re-added with the new scheduled time.
Starting the Event Scheduler
The MySQL event scheduler is a process that runs in the background and constantly looks for events to execute. Before you can create or schedule an event, you need to first turn on the scheduler, which is done by issuing the following command:mysql> SET GLOBAL event_scheduler = ON;Likewise, to turn all events off you would use:
mysql> SET GLOBAL event_scheduler = OFF;Once the event scheduler is started, you can view its status in MySQL’s process list.
mysql> SHOW PROCESSLISTG ... Id: 79 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 12 State: Waiting on empty queue Info: NULL
Working with Events
It’s important to note that when an event is created it can only perform actions for which the MySQL user that created the event has privileges to perform. Some additional restrictions include:- Event names are restricted to a length of 64 characters.
- As of MySQL 5.1.8, event names are not case-sensitive; each event name should be unique regardless of case.
- Events cannot be created, altered, or dropped by another event.
Creating Events
The following example creates an event:DELIMITER |
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
BEGIN
UPDATE mytable SET mycol = mycol + 1;
END |
DELIMITER ;
This event will run once, one hour from the time it was created. The BEGIN
and END
statements surround one or multiple queries which will be executed at the specified time. Because the semicolon is needed to terminate the UPDATE
statement, you’ll need to switch delimiters before you issue the CREATE EVENT
statement and then switch back afterwards if you’re working through a client.
You can view a list of all existing events with SHOW EVENTS
.
mysql> SHOW EVENTSG ********************** 1. row ********************** Db: mysql Name: myevent Definer: dbuser@localhost Time zone: SYSTEM Type: ONE TIME Execute At: 2011-10-26 20:24:19 Interval Value: NULL Interval Field: NULL Starts: NULL Ends: NULL Status: ENABLED Originator: 0 character_set_client: utf8 collation_connection: utf8_general_ciAfter an event has expired it will be automatically deleted unless you explicitly stated otherwise with an
ON COMPLETION
clause, for example:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ON COMPLETION PRESERVE
DO
BEGIN
UPDATE mytable SET mycol = mycol + 1;
END |
In this example, even though the event has expired it will still be retained in the database which will allow you to alter and run it again later, or perhaps you’d just like to keep it for reference.
To permanently delete an event yourself, you can use DROP EVENT
:
DROP EVENT myevent;
To specify a recurring event, you would use the EVERY
clause:
CREATE EVENT myevent
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
UPDATE mytable SET mycol = mycol + 1;
END |
And rather than having an event that just runs once or forever, you can also schedule a reoccurring event that is valid only within a specific time period, using START
and END
clauses:
CREATE EVENT myevent
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR
DO
BEGIN
UPDATE mytable SET mycol = mycol + 1;
END |
In this example, the reoccurring event would start tomorrow and continue to run every hour for a full year.
With regard to timing, the interval specified can be YEAR
, MONTH
, WEEK
, DAY
, HOUR
, MINUTE
, or SECOND
. Keep in mind that keywords are given as singular forms; writing something like INTERVAL 5 MINUTE
may seem awkward to you, but it is perfectly correct to MySQL.
Updating Events
If you want to change an existing event’s behavior rather than deleting it and recreating it, you can useALTER EVENT
. For example, to change the schedule of the previous event to run every month, starting at some date in the future at 1 o’clock in the morning, you would use the following:
ALTER EVENT myevent
ON SCHEDULE EVERY 1 MONTH
STARTS '2011-12-01 01:00:00' |
To update the event with a different set of queries, you would use:
ALTER EVENT myevent
DO
BEGIN
INSERT INTO mystats (total)
SELECT COUNT(*) FROM sessions;
TRUNCATE sessions;
END |
To rename an event, you would specify a RENAME
clause:
ALTER EVENT myevent
RENAME TO yourevent;
Blog Post Scheduling
So that I can show you a practical example, let’s say you have a blog and you want the option to schedule posts to be published at some time in the future. One way to achieve this is to add a timestamp and published flag to the database records. A cron script would execute once every minute to check the timestamps and flip the flag for any posts that should be published. But this doesn’t seem very efficient. Another way to achieve this is by using MySQL events that will fire when you want publish the post. Your blog entry form might have a checkbox that, when checked, indicates this is a scheduled post. Additionally, the form would have input fields for you to enter the date and time of when the post should be published. The receiving script would be responsible for adding the blog entry to the database and managing the events to schedule it if it’s not an immediate post. The relevant code looks like the following:<?php
// establish database connection and filter incoming data
// ...
// insert blog post with pending status, get id assigned to post
$query = "INSERT INTO blog_posts (id, title, post_text, status)
VALUES (NULL, :title, :postText, 'pending')";
$stm = $db->prepare($query);
$stm->execute(array(":title" => $title, ":postText" => $text));
$id = $db->lastInsertId();
// is this a future post?
if (isset($_POST["schedule"], $_POST["time"])) {
$scheduleDate = strtotime($_POST["time"]);
$query = "CREATE EVENT publish_:id
ON SCHEDULE AT FROM_UNIXTIME(:scheduleDate)
DO
BEGIN
UPDATE blog_posts SET status = 'published' WHERE id = :id;
END";
$stm = $db->prepare($query);
$stm->execute(array(":id" => $id, ":scheduleDate" => $scheduleDate));
}
// this is not a future post, publish now
else {
$query = "UPDATE blog_posts SET status = 'published' WHERE id = :id";
$stm = $db->prepare($query);
$stm->execute(array(":id" => $id));
}
When the post is stored in the database it is saved with a pending status. This gives you the chance to schedule the event if it’s a scheduled post, otherwise the status can be immediately updated to published.
If you were to edit the post at a later time, you can delete the event with DROP EVENT IF EXISTS
and re-add it with the new scheduled time.
Summary
You should now have a solid understanding of what MySQL events are, as well as how to create and manage events of your own. While events are not a replacement for cron jobs or scheduled tasks, as events cannot execute external code such as PHP scripts, they are a useful alternative for time-dependent tasks specific to the MySQL database. As always, if you’re interested in learning more, be sure to read the official documentation. Image via Garsya / ShutterstockFrequently Asked Questions (FAQs) about Working with MySQL Events
What is the purpose of MySQL events?
MySQL events are tasks that run according to a schedule. They are similar to the concept of cron jobs in Unix. MySQL events are useful for performing database maintenance tasks automatically at regular intervals. For example, you can use an event to automatically delete or archive records that are older than a certain date, or to gather and store database statistics at a specific time each day.
How do I create a MySQL event?
To create a MySQL event, you use the CREATE EVENT statement. This statement includes the name of the event, the schedule on which it runs, and the SQL statement that is executed when the event runs. Here is an example:CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
This event, named my_event
, runs every hour and deletes records from my_table
that are more than one day old.
How do I modify an existing MySQL event?
To modify an existing MySQL event, you use the ALTER EVENT statement. This statement allows you to change the schedule or the SQL statement of the event. For example, to change the schedule of my_event
to run every 30 minutes, you would use the following statement:ALTER EVENT my_event
ON SCHEDULE EVERY 30 MINUTE;
How do I delete a MySQL event?
To delete a MySQL event, you use the DROP EVENT statement. For example, to delete my_event
, you would use the following statement:DROP EVENT my_event;
How do I view the details of a MySQL event?
To view the details of a MySQL event, you can query the EVENTS
table in the information_schema
database. This table contains information such as the name of the event, the database in which it is defined, the SQL statement it executes, and its schedule. Here is an example query:SELECT * FROM information_schema.EVENTS WHERE EVENT_NAME = 'my_event';
How do I enable or disable the MySQL event scheduler?
The MySQL event scheduler is a process that runs in the background and executes events. To enable or disable it, you use the SET GLOBAL statement. For example, to enable the event scheduler, you would use the following statement:SET GLOBAL event_scheduler = ON;
To disable the event scheduler, you would use the following statement:SET GLOBAL event_scheduler = OFF;
Can I create a MySQL event that runs at a specific time each day?
Yes, you can create a MySQL event that runs at a specific time each day by using the EVERY DAY keyword and the STARTS clause in the CREATE EVENT statement. Here is an example:CREATE EVENT my_event
ON SCHEDULE EVERY DAY STARTS '2022-01-01 00:00:00'
DO
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
This event runs every day at midnight, starting from January 1, 2022.
Can I create a MySQL event that runs only once?
Yes, you can create a MySQL event that runs only once by using the AT keyword in the CREATE EVENT statement. Here is an example:CREATE EVENT my_event
ON SCHEDULE AT '2022-01-01 00:00:00'
DO
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
This event runs once at midnight on January 1, 2022.
Can I create a MySQL event that runs on specific days of the week?
MySQL does not directly support scheduling events on specific days of the week. However, you can achieve this by creating an event that runs every day and then using a conditional statement in the SQL statement of the event to check the day of the week. Here is an example:CREATE EVENT my_event
ON SCHEDULE EVERY DAY
DO
BEGIN
IF DAYOFWEEK(NOW()) IN (2, 3, 4) THEN
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
END IF;
END;
This event runs every day, but the DELETE statement is executed only on Monday, Tuesday, and Wednesday (where Sunday is day 1 and Saturday is day 7).
Can I create a MySQL event that runs on specific dates of the month?
MySQL does not directly support scheduling events on specific dates of the month. However, you can achieve this by creating an event that runs every day and then using a conditional statement in the SQL statement of the event to check the date of the month. Here is an example:CREATE EVENT my_event
ON SCHEDULE EVERY DAY
DO
BEGIN
IF DAYOFMONTH(NOW()) IN (1, 15) THEN
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
END IF;
END;
This event runs every day, but the DELETE statement is executed only on the 1st and 15th of the month.
Martin E. Psinas is a self-taught web developer, published author, and is currently studying Japanese. For more information, visit his website.