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 explanation of CSS background and border tag examples

1. CSS background tag 1. Set the background color...

How to implement the Vue mouse wheel scrolling switching routing effect

A root routing component (the root routing compon...

Analysis and summary of the impact of MySQL transactions on efficiency

1. Database transactions will reduce database per...

Simple implementation method of two-way data binding in js project

Table of contents Preface Publish-Subscriber Patt...

How to use ECharts in WeChat Mini Programs using uniapp

Today, we use uniapp to integrate Echarts to disp...

Some simple implementation codes of the form element take registration as an example

Code implementation: Copy code The code is as fol...

Using front-end HTML+CSS+JS to develop a simple TODOLIST function (notepad)

Table of contents 1. Brief Introduction 2. Run sc...

How to use Flex layout to achieve scrolling of fixed content area in the head

The fixed layout of the page header was previousl...

Detailed explanation of Vue + Axios request interface method and parameter passing method

Table of contents 1. Get request: 2. Post request...

Docker realizes the connection with the same IP network segment

Recently, I solved the problem of Docker and the ...

How to write a Node.JS version of a game

Table of contents Overview Build Process Related ...

Understanding and example code of Vue default slot

Table of contents What is a slot Understanding of...

Mysql case analysis of transaction isolation level

Table of contents 1. Theory SERIALIZABLE REPEATAB...

How to remove MySQL from Ubuntu and reinstall it

First delete mysql: sudo apt-get remove mysql-* T...

Linux system (Centos6.5 and above) installation jdk tutorial analysis

Article Structure 1. Preparation 2. Install Java ...