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

Detailed tutorial on jdk installation and environment variable configuration under Win10

Table of contents Preface 1. Preparation 2. Insta...

Steps to package and release the Vue project

Table of contents 1. Transition from development ...

How to modify the initial password of MySQL on MAC

Problem description: I bought a Mac and installed...

Detailed explanation of MySQL cluster: one master and multiple slaves architecture implementation

Experimental environment: 1. Three CentOS 7 serve...

The most commonly used HTML tags to create web pages

1. Optimization of commonly used HTML tags HTML s...

Detailed explanation of count(), group by, order by in MySQL

I recently encountered a problem when doing IM, a...

Solution for multiple Docker containers not having the same port number

Background In Docker, four containers are created...

Design Theory: A Method to Understand People's Hearts

<br />Once, Foyin and Mr. Dongpo were chatti...

TypeScript problem with iterating over object properties

Table of contents 1. Problem 2. Solution 1. Decla...

How to set up automatic daily database backup in Linux

This article takes Centos7.6 system and Oracle11g...

MySQL derived table (Derived Table) simple usage example analysis

This article uses an example to describe the simp...

Use of Linux tr command

1. Introduction tr is used to convert or delete a...

MySQL learning record: bloody incident caused by KEY partition

Demand background Part of the data in the busines...

MySQL 5.7.21 winx64 green version installation and configuration method graphic tutorial

This article records the installation and configu...

mysql replace part of the field content and mysql replace function replace()

[mysql] replace usage (replace part of the conten...