Analysis of MySQL's planned tasks and event scheduling examples

Analysis of MySQL's planned tasks and event scheduling examples

This article uses examples to describe MySQL's planned tasks and event scheduling. Share with you for your reference, the details are as follows:

MySQL events are tasks that run based on a predefined schedule, so sometimes they are called scheduled events. MySQL events are also called "time triggers" because they are triggered by time rather than updating a table like a trigger. MySQL events are similar to cron jobs in UNIX or the task scheduler in Windows. We can use MySQL events when optimizing database tables, cleaning logs, archiving data or generating complex reports during off-peak hours.

MySQL uses a special thread called the event dispatching 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:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL | Sleep | 1966 | | NULL |
| 3 | root | localhost:50406 | yiibaidb | Sleep | 1964 | | NULL |
| 4 | root | localhost:50407 | yiibaidb | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

By default, the event dispatcher thread is not enabled. To enable and start the event dispatcher thread, the following commands need to be executed:

SET GLOBAL event_scheduler = ON;

Now to see the status of the event scheduler thread, execute the SHOW PROCESSLIST command again, the result is as follows:

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| 2 | root | localhost:50405 | NULL | Sleep | 1986 | | NULL |
| 3 | root | localhost:50406 | luyaran | Sleep | 1984 | | NULL |
| 4 | root | localhost:50407 | luyaran | Query | 0 | starting | SHOW PROCESSLIST |
| 5 | event_scheduler | localhost | NULL | Daemon | 6 | Waiting on empty queue | NULL |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
4 rows in set

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;

As we know, an event is a named object containing SQL statements. Creating an event is similar to creating other database objects (such as stored procedures or triggers). However, stored procedures are only executed when called directly; triggers are associated with an event (such as insert, update, or delete) on a table. When the event occurs, the event can be executed at one or more regular intervals. So, what about events? Next, we will 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_name
ON SCHEDULE schedule
DO
event_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. Event names must be unique within the database schema.

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

Third, place 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 a BEGIN END block.

Let's create a messages table for demonstration:

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

Now let's create an event using the CREATE EVENT statement:

CREATE EVENT IF NOT EXISTS test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
 INSERT INTO messages(message,created_at)
 VALUES('Test MySQL Event 1',NOW());

Check the messages table; you will see that there is 1 record, which means 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 will not see any rows returned because events are automatically deleted when they expire. In our case, it is a one-time event that expires when the execution is complete. To change this behavior, you can use the ON COMPLETION PRESERVE clause. The following statement creates another one-time event that is executed 1 minute after its creation time and is not deleted after execution:

CREATE EVENT test_event_02
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test MySQL Event 2',NOW());

After waiting for 1 minute, check the messages table and you will see another record added:

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 events are due to the ON COMPLETION PRESERVE clause:

mysql> SHOW EVENTS FROM testdb;
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| testdb | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2017-08-03 04:24:48 | NULL | NULL | NULL | NULL | DISABLED | 0 | utf8 | utf8_general_ci | utf8_general_ci |
+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set

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

CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test MySQL recurring Event',NOW());

We should note that we use the STARTS and ENDS clauses to define the validity period of the event. Wait for 3 to 5 minutes and then check the messages table data to test and verify the execution of this loop 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

After that, we can use the DROP EVENT statement to delete the event. Look at the syntax structure:

DROP EVENT [IF EXISTS] event_name;

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

DROP EVENT IF EXISTS test_event_03;

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • Getting Started Guide for MySQL Stored Procedures, Triggers, and Event Schedulers
  • MySQL database triggers from beginner to proficient
  • Detailed explanation of MySQL trigger trigger example
  • Introduction to the use and advantages and disadvantages of MySQL triggers
  • Detailed explanation of the idea of ​​MySQL trigger detecting a statement in real time for backup and deletion
  • MySQL uses events to complete scheduled tasks
  • How to configure MySQL scheduled tasks (EVENT events) in detail
  • Take you to understand the event scheduler EVENT in MySQL
  • MySQL events and triggers topic refinement

<<:  uniapp project optimization methods and suggestions

>>:  Introduction to ufw firewall in Linux

Recommend

Analysis of Hyper-V installation CentOS 8 problem

CentOS 8 has been released for a long time. As so...

Docker builds CMS on-demand system with player function

Table of contents text 1. Prepare the machine 2. ...

Detailed explanation of the difference and usage of quotes and backticks in MySQL

Preface So I wrote this blog. This blog also reco...

Usage and principles of provide and inject in Vue3

Preface: When passing data between parent and chi...

How to match the size of text in web design: small text, big experience

With the rise of mobile terminals such as iPad, p...

Nexus private server construction principle and tutorial analysis

one. Why build a Nexus private server? All develo...

An example of how to use nginx to configure multiple laravel projects with one domain name

background As the company's sub-projects incr...

Vant+postcss-pxtorem implements browser adaptation function

Rem layout adaptation The styles in Vant use px a...

Detailed graphic explanation of sqlmap injection

Table of contents 1. We found that this website m...

Sample code for cool breathing effect using CSS3+JavaScript

A simple cool effect achieved with CSS3 animation...

How to check and organize website files using Dreamweaver8

What is the purpose of creating your own website u...

Implementation of setting fixed IP when starting docker container

Network type after docker installation [root@insu...

jQuery realizes the full function of shopping cart

This article shares the specific code of jQuery t...

Introduction to the use of data custom attributes in HTML and plug-in applications

You may often see some HTML with data attributes. ...

Detailed explanation of the interaction between React Native and IOS

Table of contents Prerequisites RN passes value t...