Case Analysis of scheduled tasks and event scheduling in mysql 03/26 Update SLTechnology News&Howtos

Case Analysis of scheduled tasks and event scheduling in mysql

2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

This paper gives an example to describe the planning task and event scheduling of mysql. Share with you for your reference, the details are as follows:

The mysql event is a task that runs based on a predefined schedule, so it is sometimes referred to as a scheduled event. The mysql event is also called a "time trigger" because it is triggered by time, not by updating the table like a trigger. The mysql event is similar to a cron job in UNIX or a task scheduler in Windows. We can use mysql events when optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak hours.

Mysql uses a special thread called the event scheduling thread to execute all scheduled events. We can view the status of the event scheduler thread by executing the following command:

SHOW PROCESSLIST

Execute the above query statement and get the following results:

By default, the event scheduler thread is not enabled. To enable and start the event scheduler thread, you need to execute the following command:

SET GLOBAL event_scheduler = ON

Now seeing the state of the event scheduler thread, execute the SHOW PROCESSLIST command again, and the result is as follows:

To disable and stop the event scheduler thread, set the value of event_scheduler to OFF by executing the SET GLOBAL command:

SET GLOBAL event_scheduler = OFF

We know that an event is a named object that contains SQL statements, and that creating an event is similar to creating other database objects (such as stored procedures or triggers), however, stored procedures are executed only when called directly; triggers can be triggered at one or more rule intervals when events associated with a table (such as insert, update, or delete) occur. So, what about the incident? Let's try to use the CREATE EVENT statement to create an event. Let's take a look at the syntax structure:

CREATE EVENT [IF NOT EXIST] event_nameON SCHEDULE scheduleDOevent_body

Let's take a closer look at the meaning of the parameters in the above sql:

First, specify the event name after the CREATE EVENT clause. The event name must be unique in the database schema.

Second, add a table after the ON SCHEDULE clause. If the event is an one-time event, use the syntax: AT timestamp [+ INTERVAL], and if the event is a circular event, use the EVERY clause: EVERY interval STARTS timestamp [+ INTERVAL] ENDS timestamp [+ INTERVAL]

Third, put the DO statement after the DO keyword. Note that stored procedures can be called within the event body. If you have compound SQL statements, you can put them in BEGIN END blocks.

Let's first create a messages table to demonstrate:

CREATE TABLE IF NOT EXISTS messages (id INT PRIMARY KEY AUTO_INCREMENT, message VARCHAR (255) NOT NULL, created_at DATETIME NOT NULL)

When we're done, let's use the CREATE EVENT statement to create an event:

CREATE EVENT IF NOT EXISTS test_event_01ON SCHEDULE AT CURRENT_TIMESTAMPDO INSERT INTO messages (message,created_at) VALUES ('Test MySQL Event 1 minute now ())

Check the messages table; you will see that there is 1 record, which means that the event was executed when it was created:

Mysql > SELECT * FROM messages +-+ | id | message | created_at | +-+ | 1 | Test MySQL Event 1 | 2017-08- 03 04:23:11 | +-+ 1 row in set

To display all events for the database (testdb), use the following statement:

SHOW EVENTS FROM testdb

Executing the above query does not see any row return, because the event is automatically deleted when it expires. In our example, it is an one-time event that expires when execution is complete. To change this behavior, you can use the ON COMPLETION PRESERVE clause. The following statement creates another one-time event, which is executed 1 minute after its creation time, and will not be deleted after execution:

CREATE EVENT test_event_02ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTEON COMPLETION PRESERVEDO INSERT INTO messages (message,created_at) VALUES ('Test MySQL Event 2) now ()

After waiting for 1 minute, look at the messages table and add another record:

Mysql > SELECT * FROM messages +-+ | id | message | created_at | +-+ | 1 | Test MySQL Event 1 | 2017-08- 03 04:23:11 | | 2 | Test MySQL Event 2 | 2017-08-03 04:24:48 | +-+ 2 rows in set

If you execute the SHOW EVENTS statement again, you will see that the event is due to the influence of the ON COMPLETION PRESERVE clause:

Let's create a looping event that executes every minute and expires within 1 hour of its creation time:

CREATE EVENT test_event_03ON SCHEDULE EVERY 1 MINUTESTARTS CURRENT_TIMESTAMPENDS CURRENT_TIMESTAMP + INTERVAL 1 HOURDO INSERT INTO messages (message,created_at) VALUES ('Test MySQL recurring Event',NOW ())

Note that we use the STARTS and ENDS clauses to define the validity period of the event. Wait five minutes before viewing the messages table data to test and verify the execution of this circular event:

Mysql > SELECT * FROM messages +-+-- +-+ | id | message | created_at | +-+-- -+ | 1 | Test MySQL Event 1 | 2017-08-03 04:23:11 | | 2 | Test MySQL Event 2 | 2017-08-03 04:24:48 | 3 | Test MySQL recurring Event | 2017-08-03 04:25:20 | | 4 | Test MySQL recurring Event | 2017-08-03 04:26:20 | 5 | Test MySQL recurring Event | 2017-08-03 04:27:20 | + -+-+ 5 rows in set

When we are done, we can use the DROP EVENT statement to delete the event and look at the syntax structure:

DROP EVENT [IF EXISTS] event_name

To delete an event for test_event_03, we can use the following sql:

DROP EVENT IF EXISTS test_event_03

All right, that's all for this record.

Readers who are interested in MySQL-related content can check out this site's special topics: "Summary of MySQL Index Operation skills", "Summary of MySQL Common functions", "Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.