Summary: In this tutorial, you will learn about the MySQL event scheduler and how to create events to automate repetitive database tasks.
MySQL events are tasks that run according to a specified schedule. Therefore, MySQL events are sometimes referred to as scheduled events.
MySQL events are called an object that contains one or more SQL statements. They are stored in the database and run at one or more intervals.
For example, you can create an event that optimizes all tables in the database that runs at 1:00 a.m. every Sunday.
MySQL events are also known as “temporary 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 scheduling and execution of events.
MySQL Events can be very useful in many cases, such as optimizing database tables, cleaning records, archiving data, or generating complex reports during off-peak hours.
MySQL Event
Scheduler Configuration
MySQL uses a special thread called the Event Scheduler thread to execute all scheduled events. You can view the status of the Event Scheduler thread by running the command SHOW PROCESSLIST: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)Run the SHOW PROCESSLIST
command again to view the status of the event scheduler thread:SHOW PROCESSLIST;
To disable and stop the Event Scheduler thread, 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. This is the basic syntax for
the CREATE EVENT:CREATE EVENT [IF NOT EXIST] statement event_name ON SCHEDULE DO event_bodyCode language: SQL (Structured Query Language) (sql)
In this syntax:
First, specify the name of the event in which you want to create the CREATE EVENT keywords. Event names must be unique within the same database.
Second, specify a schedule after the ON SCHEDULE keywords.
If the event
is a single event, use the syntax:
AT timestamp [+ INTERVAL]Code language: SQL (Structured Query Language) (sql)If the event
is a recurring event, use the EVERY interval clause
STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]Code language: SQL (Structured Query Language) (sql)
Third, place the SQL statements after the DO keyword. And you can call a stored procedure within the body of an event. In case you have compound instructions, 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 Single Event Example
The following example creates a point-in-time event that inserts a new row into
a table. First,
create a new table called 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 statement CREATE EVENT: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 message table and you will see a row. It means that the event was executed when it was created.
SELECT * FROM messages; Code language: SQL (Structured Query Language) (sql)
To display all events in the database, use the following statement:
SHOW EVENTS FROM classicmodels; Code language: SQL (Structured Query Language) (sql)
The output does not display any rows because the event is automatically deleted when it expires. In this case, it is a one-time event and expired when its execution was completed.
To preserve the event after it has expired, use the ON COMPLETION PRESERVE clause.
The following statement creates another single event that runs after its 1-minute creation time and is not deleted after execution.
CREATE test_event_02 OF EVENTS AS SCHEDULED AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE AT THE END PRESERVE INSERT IN messages(message,created_at) VALUES(‘Test MySQL Event 2’,NOW()); Code language: SQL (Structured Query Language) (sql)
Wait 1 minute, check the message table, added another record:
SELECT * FROM messages; Code language: SQL (Structured Query Language) (sql)If you run the SHOW EVENTS
statement again, you will see that the event is there due to the effect of the clause ON COMPLETION PRESERVE:SHOW EVENTS
FROM classicmodels; Code language: SQL (Structured Query Language) (sql)
Creating a recurring event example
The following statement creates a recurring event that runs every minute and expires in 1 hour from its creation time:
CREATE EVENT test_event_03 AS SCHEDULED EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR INSERT IN messages(message, created_at) VALUES(‘Test MySQL recurring Event’,NOW()); Code language: SQL (Structured Query Language) (sql)
Note that we use STARTS and ENDS clauses to define the expiration period of the event. You can test this recurring event by waiting a few minutes and reviewing the message table.
SELECT * FROM messages; Code language: SQL (Structured Query Language) (sql)
MySQL
DROP EVENT Statement
To remove an existing event, use the DROP EVENT statement as follows:DROP EVENT
[IF EXIST] event_name; Code language: SQL (Structured Query Language) (sql)
For example, to delete the test_event_03 event, use the following statement:
DROP EVENT IF EXIST test_event_03; Code language: SQL (Structured Query Language) (sql)
In this tutorial, you learned about MySQL events and how to create and delete events from a database.
