Working with MySQL Events

Core points

• MySQL events, introduced since MySQL 5.1.6, are a time trigger that can be scheduled for one-time or periodic execution, providing an alternative to scheduled tasks and cron jobs. They can be used to create backups, delete obsolete records, or summarize report data, etc.
• MySQL event scheduler is a background process that constantly searches for events to be executed. It can be started by issuing the command SET GLOBAL event_scheduler = ON; and it can be closed by using SET GLOBAL event_scheduler = OFF;. Its status can be viewed in the MySQL process list.
• When creating an event, please note that events can only perform operations that the MySQL user who created the event has execution permissions. Event name length is limited to 64 characters and should be case-insensitive and unique. Events cannot be created, changed, or deleted by another event, and stored functions or user-defined functions cannot be referenced when setting up event schedules.
• MySQL events can be used in real scenarios, such as planning to publish a blog post. By using MySQL events that are triggered when you want to publish an article, you can effectively manage the publication of a blog post without the need for inefficient cron scripts. Events can be created when adding a blog entry to the database, and if you edit a blog post later, you can delete the event and re-add the event with a new scheduled time.

MySQL events were added in MySQL 5.1.6 and provide an alternative to scheduled tasks and cron jobs. Events can be used to create backups, delete obsolete records, summarize report data, and more. Unlike standard triggers that are executed according to specific conditions, an event is an object triggered by time lapse, sometimes called a time trigger . You can schedule events to be executed one-time or periodically when server traffic is low. In this article, I will explain what you need to know to start using events: Start the event scheduler, add one-time or multiple run events, view existing events, and change events. I'll also share how to use a planned blog post as a practical example to use MySQL events.

Start event scheduler

The MySQL event scheduler is a process that runs in the background and constantly looks for events to be executed. Before creating or scheduling an event, you need to open the scheduler first by issuing the following command:

mysql> SET GLOBAL event_scheduler = ON;

Similarly, to close all events, you can use:

mysql> SET GLOBAL event_scheduler = OFF;

After starting the event scheduler, you can view its status in the MySQL process list.

mysql> SHOW PROCESSLIST
...
     Id: 79
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 12
  State: Waiting on empty queue
   Info: NULL

Usage Events

It should be noted that when creating an event, it can only perform operations that the MySQL user who created the event has execution permissions. Some other limitations include:

• Event name length is limited to 64 characters.
• Starting with MySQL 5.1.8, event names are case-insensitive; each event name should remain unique in case-insensitive.
• Events cannot be created, changed, or deleted by another event.

When setting event schedules, stored functions or user-defined functions cannot be referenced.

Create event

The following example creates an event:

mysql> SET GLOBAL event_scheduler = ON;

This event will run once, one hour after the time of the event being created. The BEGIN and END statements revolve around one or more queries that will be executed at a specified time. Since a semicolon is needed to terminate the UPDATE statement, if you operate through the client, you need to switch the separator before issuing the CREATE EVENT statement and then switch back. You can use SHOW EVENTS to view a list of all existing events.

mysql> SET GLOBAL event_scheduler = OFF;

Events will be automatically deleted after an expiration, unless you explicitly declare other situations using the ON COMPLETION clause, for example:

mysql> SHOW PROCESSLIST
...
     Id: 79
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 12
  State: Waiting on empty queue
   Info: NULL

In this example, it will remain in the database even if the event has expired, which will allow you to change and run it again later, or you may just want to keep it for reference. To permanently delete events yourself, you can use DROP EVENT:

DELIMITER |

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      END |

DELIMITER ;

To specify a periodic event, you can use the EVERY clause:

mysql> SHOW EVENTS
********************** 1. row **********************
                  Db: mysql
                Name: myevent
             Definer: dbuser@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute At: 2011-10-26 20:24:19
      Interval Value: NULL
      Interval Field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci

Instead of having the event run only once or forever, you can also schedule a periodic event that is only valid for a specific period of time, using the START and END clauses:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    ON COMPLETION PRESERVE
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      END |

In this example, a periodic event will begin tomorrow and run for one year every hour. Regarding timing, the specified interval can be YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. Remember that keywords are given in singular form; writing something like INTERVAL 5 MINUTE may seem odd to you, but it is completely correct for MySQL.

Update Event

If you want to change the behavior of an existing event instead of deleting it and recreating it, you can use ALTER EVENT. For example, to change the schedule of a previous event to run once a month, starting at 1 a.m. on a future date, you can use the following command:

DROP EVENT myevent;

To update events with different query sets, you can use:

CREATE EVENT myevent
    ON SCHEDULE EVERY 1 HOUR
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      END |

To rename an event, you can use the RENAME clause:

CREATE EVENT myevent
    ON SCHEDULE EVERY 1 HOUR
    STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
    ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      END |

Blog Post Plan

To show you a practical example, suppose you have a blog and you want to have the option to schedule posts to be published at some time in the future. One way to achieve this is to add a timestamp and published flag to the database record. The cron script will be executed once a minute to check the timestamp and toggle the flag for any articles that should be published. But this seems to be inefficient. Another way to do this is to use the MySQL event that is triggered when you want to publish an article. Your blog entry form may have a checkbox, and selecting it means this is a planned post. Additionally, the form will have input fields for you to enter the date and time of the post you should post. The receiving script will be responsible for adding the blog entry to the database and managing the event to schedule it (if it is not a post posted immediately). The relevant code is as follows:

mysql> SET GLOBAL event_scheduler = ON;

When storing an article to the database, it will be saved in a suspended state. This gives you the opportunity to schedule an event (if it is a planned post), otherwise you can update the status to published immediately. If you want to edit an article at a later time, you can delete the event using DROP EVENT IF EXISTS and re-add it with the new scheduled time.

Summary

You should now have a solid understanding of what MySQL events are and how to create and manage your own events. Although events cannot replace cron jobs or scheduled tasks, because events cannot execute external code (such as PHP scripts), they are useful alternatives to time-related tasks specific to MySQL databases. As always, be sure to read the official documentation if you are interested in learning more. Pictures from Garsya / Shutterstock

FAQs about using MySQL events (FAQ)

(The FAQ part is omitted here because the article is too long and does not match the pseudo-original goal. The content of the FAQ part is highly repetitive with the original text, pseudo-original is difficult and has low value.)

The above is the detailed content of Working with MySQL Events. For more information, please follow other related articles on the PHP Chinese website!