Creating MySQL Scheduled Events - A Quick Reference

In this tutorial, we will learn to create scheduled events in MySQL with some simple and effective examples. If you are new to the MySQL events topic and don’t know anything about it, we have already created an article on introduction to MySQL events where we have covered everything you should know before creating the events.

In this article, we will create different types of events in MySQL.

Introduction and Prerequisites

In MySQL, scheduled events are the tasks that execute at the specified time. These events can be one-time or recurring and you can schedule them to start in the future or as soon as you create that event.

Also, the event scheduler of your MySQL server must be turned on in order to execute the events. You can read about the events and event scheduler in our introduction to events article.

Syntax of MySQL Scheduled Events

You can easily create an event in MySQL using the CREATE EVENT statement. Following is the brief syntax of creating an event.

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_bodyCode language: SQL (Structured Query Language) (sql)

Where,

CREATE EVENT – Statement to create an event in the database.
DEFINER – The user in MySQL.
IF NOT EXISTS – Avoids warning if you try to create a event that already exists and stops further execution.
event_name – Any name for the event. No two events of the same name can reside in the same database.
schedule – The period/time you want the event to execute/envoke. You can choose the event to execute only once or over and over again.
DO event_body – The set of SQL statements

If we simplify the above syntax, it would look like –

CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
DO
event_bodyCode language: SQL (Structured Query Language) (sql)

For the one-time events, the schedule can be-

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

Whereas, for the recurring events, the schedule can be-

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

Enough theory, let’s create events now.

MySQL Scheduled Events Examples

We will create a one-time event that will insert a new row every minute in a table.

Before creating an event, let’s create a table to store the UUIDs.

CREATE TABLE uuids(
id INT PRIMARY KEY AUTO_INCREMENT,
uuid VARCHAR(36),
created_at DATETIME
);Code language: SQL (Structured Query Language) (sql)

Perfect! Let’s create an event now.

Creating One-Time Events

The one-time events are executed only once for the specified schedule. You can start the event immediately using the current DateTime or schedule it in the future using the intervals.

Example 1 –

If you create an event that executes immediately at the time of creation, you won’t find it on the event list when the SHOW EVENTS command is entered because the event is finished/expired and therefore it gets removed from the event queue.

CREATE EVENT insert_uuid
ON SCHEDULE AT NOW()
DO INSERT INTO uuids(uuid,created_at)
VALUES(uuid(), now());Code language: SQL (Structured Query Language) (sql)

The event is created successfully. The new row should be added to the table uuids. Let’s display the table.

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

If you enter the command SHOW EVENTS, you will find the empty result.

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

The event information is not available because it got expired as soon as it finished its execution.

Example 2 –

If you schedule the event for execution in the future, you will find it in the event list. For this, let’s schedule an event for the future.

CREATE EVENT insert_uuid_future
ON SCHEDULE AT NOW() + INTERVAL 2 MINUTE
DO INSERT INTO uuids(uuid,created_at)
VALUES(uuid(), now());Code language: SQL (Structured Query Language) (sql)

The event is created. Let’s check if it is shown in the event list.

SHOW EVENTS FROM  journaldev \G;Code language: SQL (Structured Query Language) (sql)

In the event description, you can see the event is scheduled at 2022-02-17 21:04:59. According to it, the new row must be inserted into the table at the same time. Let’s check the table.

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

Uuids Table Data After Future One Time Event

As you can see, the new row is inserted at the exact same time.

Creating Recurring Events

The recurring events, unlike the one-time events, execute multiple times till the ENDS time. If you don’t specify the STARTS and ENDS time while creating the event, the event will start immediately and run as long as the server is up.

If you only specify the start time, the event will start at the scheduled time and run as long as your MySQL server is running.

Let’s create a simple recurring event.

Example 1 –

We will create an event that runs every minute and inserts a new row in the table uuids.

CREATE EVENT rec_event
ON SCHEDULE EVERY 1 MINUTE
DO INSERT INTO uuids(uuid,created_at)
VALUES(uuid(),now());Code language: SQL (Structured Query Language) (sql)

The event is created that runs every minute and insert a new row. Let’s display the uuids table to see if the new rows are getting inserted every minute.

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

As you can see, two rows are inserted into the table in 1 minute of interval.

Example 2 –

Now let’s create an event that starts immediately and expires after 6 months.

CREATE EVENT rec_event_6months
ON SCHEDULE EVERY 1 MINUTE
STARTS NOW()
ENDS NOW() + INTERVAL 6 MONTH
DO INSERT INTO uuids(uuid,created_at)
VALUES(uuid(),now());Code language: SQL (Structured Query Language) (sql)

The event is created. Let’s see the information about this event from the event list.

SHOW EVENTS FROM JOURNALDEV \G;Code language: SQL (Structured Query Language) (sql)

As you can see, the event starts on 2022-02-18 at 12:00:30 and expires on 2022-08-18 at 12:00:30 which is after the six months starting date.

Let’s display the table UUIDs to check if the event is working fine.

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

As you can see, the highlighted rows are inserted by this newly created event.

Deleting the Events

You can remove the events from the system using the DROP EVENT statement. The syntax to remove the event is-

DROP EVENT [IF EXIST] event_name;Code language: SQL (Structured Query Language) (sql)

The IF EXISTS clause prevents the query from getting an error.

Let’s delete the newly created event.

DROP EVENT IF EXISTS rec_event_6months;Code language: SQL (Structured Query Language) (sql)

The event is deleted successfully!

Conclusion

I hope you have learned to create events in MySQL effectively. We have tried to cover as many types as we can of the scheduled events. You can create such events to run in the background to achieve good performance. These events are very helpful as you grow your app/website for a larger audience/user base. In the next tutorial, we will see how can we modify the existing events.

References

MySQL official documentation about scheduled events.