How to configure MySQL scheduled tasks (EVENT events) in detail

1. What is an event?

Since MySQL 5.1.6 , a very unique feature has been added -事件調度器Scheduler, which can be used to perform certain specific tasks (such as deleting records, data statistics reports, data backup, etc.) at a scheduled time to replace the work that could only be performed by the operating system's scheduled tasks.
It is worth mentioning that MySQL's event scheduler can execute a task accurately to every second, while the operating system's scheduled tasks (such as Linux's cron) can only be executed accurately to once a minute. It is very suitable for applications that have high requirements for real-time data (such as stocks, odds, scores, etc.).

Events are sometimes also called temporary triggers, because the event scheduler is triggered based on a specific time period to perform certain tasks, while triggers are triggered based on events generated by a certain table. This is the difference.

2. Enable the "Event" function

Before using the "Event" function, you must ensure that event_scheduler is enabled

1. Check whether the function is enabled:

-- Method 1 SELECT @@event_scheduler;
-- Method 2 SHOW VARIABLES LIKE 'event%';

If "ON" is displayed, it means the function has been turned on; as shown below:

mysql> SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)

2. Enable and disable function commands:

-- Enable function command:
SET GLOBAL event_scheduler = 1;
SET GLOBAL event_scheduler = ON;
-- Disable function command:
SET GLOBAL event_scheduler = 0;
SET GLOBAL event_scheduler = OFF;

Of course, it will be automatically closed when the database is restarted by opening the command;

持久化開啟方式: Write event_scheduler=1 to the my.cnf configuration file; as shown below:

Commonly used event operation commands:

• Turn off the specified event: ALTER EVENT event name ON COMPLETION PRESERVE DISABLE;
• Enable the specified event: ALTER EVENT event name ON COMPLETION PRESERVE ENABLE;
• View current events: SHOW EVENTS ;

3. Event - SQL syntax creation

Below is the EVENT event creation statement. At first glance, it is quite complicated. Let's break it down and interpret it.

CREATE EVENT [IFNOT EXISTS] event_name
   ON SCHEDULE schedule (scheduling time setting)
   [ON COMPLETION [NOT] PRESERVE]
   [ENABLE | DISABLE | DISABLE ON SLAVE]
   [COMMENT 'comment']
   DO sql_statement;

schedule time configuration syntax: Schedule time configuration includes AT and EVERY

AT timestamp [+ INTERVAL interval] ...
 | EVERY interval
 [STARTS timestamp [+ INTERVAL interval] ...]
 [ENDS timestamp [+ INTERVAL interval] ...]


-- The time units included in INTERVAL are as follows:
{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
 WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
 DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

1. Create - Single scheduled execution event

AT TIMESTAMP time string [+ INTERVAL INTERVAL]

AT TIMESTAMP means that the event is executed only once. TIMESTAMP represents a specific time point, which can be followed by a time interval to indicate that the event occurs after this time interval. [+ INTERVAL INTERVAL] indicates the delay trigger time;

It should be noted that TIMESTAMP is used together with a specific string. If it is not a specific string (such as CURRENT_TIMESTAMP to get the current time, etc.), TIMESTAMP is not added;

Example 1: Insert a row of data into the demo_1119 table. Execution time: 2020-11-20 00:00:00

CREATE EVENT demo_event2 
ON SCHEDULE AT TIMESTAMP '2020-11-20 00:00:00' 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陈哈哈', NOW())

Result query:

mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id | name | createTime |
+-----+-----------+---------------------+
| 145 | Chen Haha | 2020-11-20 00:00:00 |
+-----+-----------+---------------------+
9 rows in set (0.00 sec)

Example 2: Insert a row of data into the demo_1119 table, execution time: 5 hours after the current time;

CREATE EVENT demo_event2 
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 HOUR
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陈哈哈', NOW())

2. Create - Loop Timed Execution Event

EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

EVERY表示循環執行該事件, where the STARTS clause is used to specify the start time; ENDS clause is used to specify the end time.

Example 1: From now on, insert a row of data into the demo_1119 table every 10 seconds

CREATE EVENT demo_event3 
ON SCHEDULE EVERY 10 SECOND 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陈哈哈', NOW())

mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id | name | createTime |
+-----+-----------+---------------------+
| 145 | Chen Haha | 2020-11-19 11:10:39 |
| 146 | Chen Haha | 2020-11-19 11:10:49 |
| 147 | Chen Haha | 2020-11-20 11:10:59 |
| 148 | Chen Haha | 2020-11-20 11:11:09 |
| 149 | Chen Haha | 2020-11-20 11:11:19 |
| 150 | Chen Haha | 2020-11-20 11:11:29 |
| 151 | Chen Haha | 2020-11-20 11:11:39 |
+-----+-----------+---------------------+
9 rows in set (0.00 sec)

Example 2: Starting from 2020-11-20 12:00:00, insert a row of data into the demo_1119 table every 10 minutes

CREATE EVENT demo_event4 
ON SCHEDULE EVERY 10 MINUTE STARTS '2020-11-20 12:00:00' 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陈哈哈', NOW())

mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id | name | createTime |
+-----+-----------+---------------------+
| 152 | Chen Haha | 2020-11-20 12:00:00 |
| 153 | Chen Haha | 2020-11-20 12:10:00 |
| 154 | Chen Haha | 2020-11-20 12:20:00 |
| 155 | Chen Haha | 2020-11-20 12:30:00 |
| 156 | Chen Haha | 2020-11-20 12:40:00 |
+-----+-----------+---------------------+
5 rows in set (0.00 sec)

Example 3: Starting one hour after the current time, insert a row of data into the demo_1119 table every 10 minutes; it has been tested and is available, so I will not post redundant query data~

CREATE EVENT demo_event5 
ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP+INTERVAL 1 HOUR 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陈哈哈', NOW())

Example 4: Starting one day from the current time, insert a row of data into the demo_1119 table every hour, and end after three days

CREATE EVENT demo_event5 
ON SCHEDULE EVERY 1 HOUR 
STARTS CURRENT_TIMESTAMP+INTERVAL 1 DAY 
ENDS CURRENT_TIMESTAMP+INTERVAL 3 DAY 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陈哈哈', NOW())

Example 5: Clear the data in the demo_1119 table at 0:00 every day

CREATE EVENT demo_event5 
ON SCHEDULE EVERY 1 DAY STARTS '2020-11-20 00:00:00' 
ON COMPLETION PRESERVE 
DO TRUNCATE table `demo_1119`

3. Modify events

The modify event statement is exactly the same as the create statement, the syntax is as follows:

ALTER EVENT event_name
   [ONSCHEDULE schedule]
   [old_NAME TO new_NAME]
   [ON COMPLETION [NOT] PRESERVE]
   [COMMENT 'comment']
   [ENABLE | DISABLE]
   [DO sql_statement]

Turn off the event task: ALTER EVENT event name ON COMPLETION PRESERVE DISABLE;

Enable event task: ALTER EVENT event name ON COMPLETION PRESERVE ENABLE;

4. Delete events

DROP EVENT [IF EXISTS] event_name

4. Events - Create with Navicat (Recommended)

Many people like to type SQL statements on the command line, which gives them a sense of professionalism, but they may also be morally kidnapped by their leaders~~

Just like some leaders think that it is cool and impressive for their employees to use Google, but it is low to use Baidu to search for csdn. But as a newbie, I still like to use my Navicat gadget and Baidu. Okay, without further ado, let's take a look at how Navicat creates EVENT events, GO!

As shown below, right click to create a new event

定義column in the create event is used to write the execution SQL, which can include one or more SQL statements, stored procedures, etc.計劃column is used to define the event trigger time. As shown below, I defined an insert statement during execution.

Of course, you can also write multiple SQL statements at the same time with semicolons in between. Start with BEGIN and end with END.

Open the plan column, doesn’t it feel familiar? We have seen this in the previous module. In order to help our friends have a deeper impression, let’s review it.

Parameter Description:

AT : Indicates that the event is executed only once. You can set a specific time, or you can use CURRENT_TIMESTAMP to represent the current time, followed by a time interval , which indicates how long after this time the event will occur, indicating the delayed trigger time.

• EVERY : Execute the event repeatedly, where STARTS clause is used to specify the start time;
• The ENDS clause is used to specify the end time.
• interval :表示從現在開始時間延遲多久以后的一個時間點. Its value consists of a number and a unit. For example, use "4 WEEK" to indicate 4 weeks from now; use "'1:10' HOUR_MINUTE" to indicate 1 hour and 10 minutes from now. The interval distance is determined by the DATE_ADD() function.

The time units included in INTERVAL are as follows:

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND |
YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

Okay, let's use some examples to make it more memorable: Example 1: Insert a row of data into the demo_1119 table, execution time: 2020-11-20 00:00:00

Example

2: Insert a row of data into the demo_1119 table, execution time: 5 hours after the current time;

Example 3: From now on, insert a row of data into the demo_1119 table every 10 seconds

Example 4: Starting from 2020-11-20 12:00:00, insert a row of data into the demo_1119 table every 10 minutes

Example 5: Starting one hour after the current time, insert a row of data into the demo_1119 table every 10 minutes;

Example 6: Starting one day from the current time, insert a row of data into the demo_1119 table every hour, and end after three days

Example 7: Clear the data in the demo_1119 table at 0:00 every day

This is the end of this article on how to configure MySQL scheduled tasks (EVENT events). For more relevant MySQL scheduled tasks, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!