Working with MySQL Scheduled Event

 Working with MySQL Scheduled Event

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 execute according to a specified schedule. Therefore, sometimes MySQL events are referred to as scheduled events.

MySQL Events are named object that 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 the 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;

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;

Execute the  SHOW PROCESSLIST command again to see the status of the 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;

Creating new MySQL events

The CREATE EVENT the 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_body

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]

If the event is a recurring event, use the EVERY clause:

EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]

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
);

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());

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;

To shows all events in the database, you use the following statement:

SHOW EVENTS FROM classicmodels;

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 is 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());

Wait for 1 minute, check the messages table, another record was added:

SELECT * FROM messages;

If you execute the  SHOW EVENTS the statement again, you will see that the event is there because of the effect of the  ON COMPLETION PRESERVE clause:

SHOW EVENTS FROM classicmodels;

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());

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 checking the messages table.

SELECT * FROM messages;

MySQL DROP EVENT Statement

To remove an existing event, you use the  DROP EVENT a statement as follows:

DROP EVENT [IF EXIST] event_name;

For example, to drop the  test_event_03 the event, you use the following statement:

DROP EVENT IF EXIST test_event_03;

In this tutorial, you have learned about MySQL events and how to create and delete events from a database.