MySQL CREATE EVENT

The MySQL CREATE EVENT statement is used to create and schedule events that will be executed at specified intervals or specific points in time. Events in MySQL are similar to scheduled tasks or cron jobs in other systems. They allow you to automate recurring tasks such as database maintenance, data updates, or any other operations that need to be performed on a regular basis.

Syntax

Here is the basic syntax for the CREATE EVENT statement:

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
DO
event_body;

Let’s break down the components of the CREATE EVENT statement:

event_name: This is the name of the event. It must be unique within the database.

IF NOT EXISTS: An optional clause that ensures that the event is created only if an event with the same name does not already exist.

ON SCHEDULE schedule: This specifies when the event should be executed. The schedule can be defined using various options like EVERY, AT, and more, allowing you to set up daily, weekly, or custom intervals.

ON COMPLETION [NOT] PRESERVE: This clause determines whether the event should be preserved or dropped after it completes. If PRESERVE is specified, the event is kept; otherwise, it is dropped.

ENABLE | DISABLE: This specifies whether the event is enabled or disabled. An enabled event will be executed as scheduled, while a disabled event will not.

DO event_body: This is the body of the event, where you define the actions or statements that should be executed when the event is triggered.

Example

Here is a simple example of a CREATE EVENT statement:

CREATE EVENT my_daily_event
ON SCHEDULE EVERY 1 DAY
DO
  BEGIN
    -- Your SQL statements or procedures here
    UPDATE my_table SET column1 = column1 + 1;
  END;

In this example, the event named my_daily_event is scheduled to run every day, and it will execute the specified SQL statement to update a column in the my_table table.

It’s important to note that the event scheduler must be running for events to be executed. You can enable it globally or for a specific session using the following statements:

-- To enable the event scheduler globally
SET GLOBAL event_scheduler = ON;

-- To enable the event scheduler for the current session
SET @@event_scheduler = ON;

The MySQL event scheduler runs periodically and executes events based on their schedules. The information about events, including their schedules and status, can be queried from the information_schema.events table.

The CREATE EVENT statement is a powerful tool for automating tasks in MySQL. It can be used to schedule one-time or recurring events, and it can also be used to call stored procedures and triggers.