Working with MySQL Scheduled Event
Summary: in this tutorial, you will learn about MySQL event scheduler and how to create events to automate repetitive database tasks.
MySQL Events are tasks that execute according to a specified schedule. Therefore, sometimes MySQL events are referred to as scheduled events.
MySQL Events are named object which contains one or more SQL statement. They are stored in the database and executed at one or more intervals.
For example, you can create an event that optimizes all tables in the database that runs at 1:00 AM every Sunday.
MySQL Events are also known as “temporal triggers” because they are triggered by time, not by DML events like normal triggers. MySQL events are similar to a cronjob on Linux or a task scheduler on Windows.
MySQL Event Scheduler manages the schedule and execution of Events.
MySQL Events can be very useful in many cases such as optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak time.
MySQL event scheduler configuration
MySQL uses a special thread called event scheduler thread to execute all scheduled events. You can view the status of the event scheduler thread by executing the SHOW PROCESSLIST command:
SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)
If the event scheduler is not enabled, you can set the event_scheduler system variable to enable and start it:
SET GLOBAL event_scheduler = ON;Code language: PHP (php)
Execute the SHOW PROCESSLIST command again to see the status of event scheduler thread:
SHOW PROCESSLIST;
To disable and stop the event scheduler thread, you set event_scheduler system variable to OFF:
SET GLOBAL event_scheduler = OFF;Code language: PHP (php)
Creating new MySQL events
The CREATE EVENT statement creates a new event. Here is the basic syntax of the CREATE EVENT statement:
CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
DO
event_bodyCode language: SQL (Structured Query Language) (sql)
In this syntax:
First, specify the name of the event that you want to create the CREATE EVENT keywords. The event names must be unique within the same database.
Second, specify a schedule after the ON SCHEDULE keywords.
If the event is a one-time event, use the syntax:
AT timestamp [+ INTERVAL]Code language: SQL (Structured Query Language) (sql)
If the event is a recurring event, use the EVERY clause:
EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]Code language: SQL (Structured Query Language) (sql)
Third, place SQL statements after the DO keyword. And you can call a stored procedure inside the body of an event. In case you have compound statements, you can wrap them in a BEGIN END block.
MySQL CREATE EVENT examples
Let’s take some examples of creating new events.
A) Creating a one-time event example
The following example creates an on-time event that inserts a new row into a table.
First, create a new table named messages:
CREATE TABLE messages (
id INT PRIMARY KEY AUTO_INCREMENT,
message VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL
);Code language: SQL (Structured Query Language) (sql)
Second, create an event using the CREATE EVENT statement:
CREATE EVENT IF NOT EXISTS test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL Event 1',NOW());Code language: SQL (Structured Query Language) (sql)
Third, check the messages table and you will see one row. It means that the event was executed when it is created.
SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)
To shows all events in the database, you use the following statement:
SHOW EVENTS FROM classicmodels;Code language: SQL (Structured Query Language) (sql)
The output shows no row because the event is automatically dropped when it is expired. In this case, it is a one-time event and expired when its execution completed.
To keep the event after it is expired, you use the ON COMPLETION PRESERVE clause.
The following statement creates another one-time event that is executed after its creation time 1 minute and not dropped after execution.
CREATE EVENT test_event_02
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL Event 2',NOW());Code language: SQL (Structured Query Language) (sql)
Wait for 1 minute, check the messages table, another record was added:
SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)
If you execute the SHOW EVENTS statement again, you will see that the event is there because of the effect of the ON COMPLETION PRESERVE clause:
SHOW EVENTS FROM classicmodels;Code language: SQL (Structured Query Language) (sql)
Creating a recurring event example
The following statement creates a recurring event that executes every minute and is expired in 1 hour from its creation time:
CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL recurring Event',NOW());Code language: SQL (Structured Query Language) (sql)
Notice that we used STARTS and ENDS clauses to define the expiration period for the event. You can test this recurring event by waiting for a few minutes and check the messages table.
SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)
MySQL DROP EVENT Statement
To remove an existing event, you use the DROP EVENT statement as follows:
DROP EVENT [IF EXIST] event_name;Code language: SQL (Structured Query Language) (sql)
For example, to drop the test_event_03 event, you use the following statement:
DROP EVENT IF EXIST test_event_03;Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned about MySQL events and how to create and delete events from a database.