MySQL CREATE EVENT

Summary: in this tutorial, you will learn how to use the MySQL CREATE EVENT statement to create an event to automate repetitive database tasks.

Introduction to MySQL CREATE EVENT statement

Events are tasks that are executed according to a schedule. Therefore, events are often referred to as scheduled events.

To create a new event, you use The CREATE EVENT statement. Here’s 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 after 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, you use the syntax:

AT timestamp [+ INTERVAL]Code language: SQL (Structured Query Language) (sql)

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

EVERY interval 
STARTS timestamp [+INTERVAL] 
ENDS timestamp [+INTERVAL]Code language: SQL (Structured Query Language) (sql)

The STARTS specifies when the event starts repeating and the ENDS specifies when the event stops repeating.

Third, place an SQL statement to execute after the DO keyword.

If you have multiple statements, you can use the BEGIN...END block. Please note that you can call a stored procedure inside the body of an event.

MySQL CREATE EVENT statement examples

Let’s take some examples of creating new events.

1) 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 database called mydb and create a new table called messages inside the mydb database:

CREATE DATABASE IF NOT EXISTS mydb;

USE mydb;

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT NOW()
);Code language: SQL (Structured Query Language) (sql)

Second, create an event using the CREATE EVENT statement:

CREATE EVENT IF NOT EXISTS one_time_log
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
  INSERT INTO messages(message)
  VALUES('One-time event');Code language: SQL (Structured Query Language) (sql)

The one_time_log event is a one-time event that runs immediately when it is created. It inserts a new row into the messages table.

Third, retrieve the data from the messages table:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------------+---------------------+
| id | message        | created_at          |
+----+----------------+---------------------+
|  1 | One-time event | 2024-01-07 10:45:07 |
+----+----------------+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

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

SHOW EVENTS FROM mydb;Code language: SQL (Structured Query Language) (sql)

Output:

Empty set (0.00 sec)Code language: JavaScript (javascript)

The output shows no row because the event is automatically dropped when it expires. In this case, it is a one-time event and expires when its execution is completed.

To keep the event after it has expired, you use the ON COMPLETION PRESERVE clause.

The following statement creates another one-time event that is executed after its creation time of 1 minute and is not dropped after execution:

CREATE EVENT one_time_log
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
   INSERT INTO messages(message)
   VALUES('Preserved One-time event');Code language: SQL (Structured Query Language) (sql)

Wait for 1 minute and retrieve data from the messages table:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

Output:

+----+---------------------------+---------------------+
| id | message                   | created_at          |
+----+---------------------------+---------------------+
|  1 | One-time event            | 2024-01-07 10:45:07 |
|  2 | Preserved One-time event  | 2024-01-07 10:48:10 |
+----+---------------------------+---------------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

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

SHOW EVENTS FROM mydb;Code language: SQL (Structured Query Language) (sql)

Output:

+------+--------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| Db   | Name         | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------+--------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| mydb | one_time_log | root@localhost | SYSTEM    | ONE TIME | 2024-01-07 10:48:10 | NULL           | NULL           | NULL   | NULL | ENABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_0900_ai_ci |
+------+--------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)Code language: PHP (php)

2) Creating a recurring event example

The following statement creates a recurring event that executes every minute and expires within 1 hour from its creation time:

CREATE EVENT recurring_log
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO messages(message)
   VALUES(CONCAT('Running at ', 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 checking the messages table.

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

Output:

+----+--------------------------------+---------------------+
| id | message                        | created_at          |
+----+--------------------------------+---------------------+
|  1 | One-time event                 | 2024-01-07 10:45:07 |
|  2 | Preserved One-time event.      | 2024-01-07 10:48:10 |
|  3 | Running at 2024-01-07 10:49:47 | 2024-01-07 10:49:47 |
|  4 | Running at 2024-01-07 10:50:47 | 2024-01-07 10:50:47 |
|  5 | Running at 2024-01-07 10:51:47 | 2024-01-07 10:51:47 |
+----+--------------------------------+---------------------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

Summary

Use the CREATE EVENT statement to create a scheduled event.

Was this tutorial helpful?