How to Create and Schedule Events in MySQL

Automating tasks in MySQL, like backups, data cleaning, and updates, makes your work easier by removing the need for manual effort. A MySQL event is a task that runs at a specific time or on a set schedule. Each event can have one or more MySQL statements, which are saved in the database and executed when scheduled. 

In this article, we will provide simple, step-by-step instructions on how to use the CREATE EVENT statement to create and manage these tasks effectively.

Creating and Scheduling Events in MySQL

Events are like stored procedures, but they run automatically on a set/specified schedule. In MySQL database, the CREATE EVENT statement is used to create a new event.

MySQL Event Syntax

The basic syntax to create an event in MySQL is as follows:

CREATE EVENT eventName
ON SCHEDULE dateTime
DO eventBody;

In this syntax:

• eventName is the name of the event you want to create.
• dateTime is the scheduled time when the statements will run.
• eventBody is the group of statements that will be executed at the scheduled time.

Let’s learn how to create and schedule events in MySQL using examples.

Example 1: Basic Example of CREATE EVENT Statement

Let’s create a new table named employee_info using the CREATE TABLE statement as shown below:

CREATE TABLE employee_info (FullName VARCHAR(255), Age INT);

The table contains two columns FullName and Age:

Now run the CREATE EVENT statement to create an event named add_employee_event. This event is set to insert a record into the employee_info table one minute after execution:

CREATE EVENT add_employee_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO INSERT INTO employee_info VALUES('Anees Asghar', 28);

After one minute, you can verify the contents of the employee_info table using the SELECT statement as follows:

The output confirms that a new record has been successfully inserted into the employee_info table at the scheduled time.

Example 2: CREATE EVENT Statement With IF NOT EXISTS clause

You can use the IF NOT EXISTS clause with the CREATE EVENT statement to create a new event if it doesn’t already exist. If an event with the same name already exists, the query will just be ignored. 

For example, in the following query, we tried to create an event with the same name that already exists, as a result, we face the following error:

CREATE EVENT add_employee_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO INSERT INTO employee_info VALUES('Anees Asghar', 28);

To avoid this error, we run the CREATE EVENT statement with the IF NOT EXISTS clause as follows:

CREATE EVENT IF NOT EXISTS add_employee_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO INSERT INTO employee_info VALUES('Anees Asghar', 28);

This time, the query executed successfully without showing any error:

The 1 warning in the output indicates that the event with the name add_employee_event already existed and was not recreated, but the query itself did not fail.

Example 3: CREATE EVENT Statement With Comment clause

We can run the CREATE EVENT statement with a COMMENT clause to add a descriptive note to an event. For example, the following query schedules an event and includes a comment for documentation purposes:

CREATE EVENT IF NOT EXISTS event_truncate_after_2_days
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY
COMMENT 'This truncates the employee_info table after 2 days'
DO
   TRUNCATE TABLE employee_info;

This way, we can add a comment to indicate the purpose of the event, which, in this case, is to truncate the employee_info after 2 days.

Example 4: CREATE EVENT Statement with the EVERY Clause

We can use the CREATE EVENT statement with the EVERY clause to schedule events to run at regular intervals. For example, the following query truncates the employee_info table every 2 days:

CREATE EVENT IF NOT EXISTS event_truncate_2_days
ON SCHEDULE EVERY 2 DAY
COMMENT 'This truncates the employee_info table every 2 days'
DO
   TRUNCATE TABLE employee_info;

This event will execute the TRUNCATE TABLE command for employee_info every 2 days.

That’s all about creating and scheduling events in MySQL.

Conclusion

Events in MySQL help us make tasks easier by automating important jobs like backups, data cleaning, and updates. With the CREATE EVENT statement, you can set up tasks to run automatically at specific times or on a regular schedule. Moreover, we can use the CREATE EVENT statement with different clauses to handle common scenarios, such as avoiding errors, adding useful notes, scheduling tasks at regular intervals, etc. In this article, we covered different examples of creating and scheduling events in MySQL.

FAQ