Detailed Explanation of mysql Timing Task of event Event Implementation

1. Brief introduction of events

Events (event) are procedural database objects called by MySQL at the corresponding time. An event can be called once or started periodically. It is managed by a specific thread, which is called "event scheduler".

Events are similar to triggers in that they are started when something happens. When a statement is started on the database, the trigger is started, and the event is started according to the scheduled event. Because they are similar to each other, events are also called temporary triggers.

Events replace the work that can only be executed by the scheduled tasks of the operating system, and the event scheduler of MySQL can execute one task per second, while the scheduled tasks of the operating system (such as CRON under Linux or Windows) can only be executed once per minute.

Advantages and disadvantages of 2 events

2.1 Advantages

1 Some data timing operations no longer rely on external programs, but directly use the functions provided by the database itself.
It can perform 1 task per second, which is very practical in 1 environment with high real-time requirements.

2.2 Disadvantages

Timed trigger, cannot be called.

3 Create Events

One create event statement creates one event. Each event consists of two main parts. The first part is event scheduling (eventschedule), which indicates when and at what frequency the event starts;

Part 2 is the event action (event action), which is the code executed when the event is started. The event action contains an SQL statement, which may be a simple insert or update statement, or a stored procedure or
benin... end statement blocks, both of which allow us to execute multiple SQL.

1 An event can be active (open), meaning that the event scheduler checks whether the event action must be invoked, or stopped (closed), meaning that the declaration of the event is stored in a directory, but the scheduler does not check whether it should be invoked. Once an event is created, it becomes active, and an active event can be executed one or more times.

3.1 Create the following syntax

  CREATE 
    [DEFINER = { user | CURRENT_USER }] 
    EVENT 
    [IF NOT EXISTS] 
    event_name 
    ON SCHEDULE schedule 
    [ON COMPLETION [NOT] PRESERVE] 
    [ENABLE | DISABLE | DISABLE ON SLAVE] 
    [COMMENT 'comment'] 
    DO event_body; 
    
  schedule: 
    AT timestamp [+ INTERVAL interval] ... 
   | EVERY interval 
    [STARTS timestamp [+ INTERVAL interval] ...] 
    [ENDS timestamp [+ INTERVAL interval] ...] 
    
  interval: 
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
         WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | 
         DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Explanation of nouns:

event_name: The name of the created event (only 1 is determined).
ON SCHEDULE: Scheduled tasks.
schedule: Determines the execution time and frequency of event (note that time 1 must be in the future, and there will be errors in the past time). There are two forms of AT and EVERY.
[ON COMPLETION [NOT] PRESERVE]: Optional, the default is ON COMPLETION NOT PRESERVE, that is, the event will be automatically drop after the scheduled task is executed; ON COMPLETION PRESERVE will not drop drop.
[COMMENT 'comment']: Optional, comment is used to describe event; Quite annotated, with a maximum length of 64 bytes.
[ENABLE DISABLE]: Set the state of event. By default, ENABLE indicates that the system is trying to execute this event. DISABLE: Turn off this event, which can be modified by alter
DO event_body: The sql statement that needs to be executed (it can be a compound statement). CREATE EVENT is legal when used in stored procedures.

3.2 Turn Event Scheduler on and off

3.2. 1 The MySQL event scheduler event_scheduler is responsible for invoking events, which is turned off by default. This scheduler constantly monitors whether an event is to be invoked, and to create an event, the scheduler must be turned on.

mysql> show variables like '%event_scheduler%'; 
+-----------------+-------+ 
| Variable_name  | Value | 
+-----------------+-------+ 
| event_scheduler | OFF  | 
+-----------------+-------+ 

3.2. 2 Turn on the event scheduler

Through the command line

You can use any one of the following command lines

SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1; 

Through the configuration file my. cnf

event_scheduler = 1 # or ON

View scheduler threads

mysql> show processlist; 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+ 
| Id | User      | Host   | db  | Command | Time | State         | Info       | 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+ 
| 2 | root      | localhost | NULL | Query  |  0 | NULL          | show processlist | 
| 3 | event_scheduler | localhost | NULL | Daemon |  6 | Waiting on empty queue | NULL| 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+ 

3.2. 3 Shutdown the event scheduler

Through the command line

You can use any one of the following command lines

SET GLOBAL event_scheduler = OFF; 
SET @@global.event_scheduler = OFF; 
SET GLOBAL event_scheduler = 0; 
SET @@global.event_scheduler = 0; 

Through the configuration file my. cnf

Add under [mysqld]

event_scheduler = 0 # or OFF, DISABLED

View scheduler threads

mysql> show processlist; 
+----+------+-----------+------+---------+------+-------+------------------+ 
| Id | User | Host   | db  | Command | Time | State | Info       | 
+----+------+-----------+------+---------+------+-------+------------------+ 
| 2 | root | localhost | NULL | Query  |  0 | NULL | show processlist | 
+----+------+-----------+------+---------+------+-------+------------------+

3.3 For example: Create a table to record the name and event stamp of each event schedule

3.3. 1 Create a test table

mysql> drop table if exists events_list; 
mysql> create table events_list(event_name varchar(20) not null, event_started timestamp not null);

3.3. 2 Create Event 1 (Start Event Now)

create event event_now 
on schedule 
at now() 
do insert into events_list values('event_now', now()); 

View event execution results

mysql> select * from events_list; 
+------------+---------------------+ 
| event_name | event_started    | 
+------------+---------------------+ 
| event_now | 2014-07-01 04:06:40 | 
+------------+---------------------+ 

3.3. 3 Create Event 2 (Start Events Every Minute)

create event test.event_minute 
on schedule 
every 1 minute  
do insert into events_list values('event_now', now()); 

View event execution results

mysql> show variables like '%event_scheduler%'; 
+-----------------+-------+ 
| Variable_name  | Value | 
+-----------------+-------+ 
| event_scheduler | OFF  | 
+-----------------+-------+ 

0

3.3. 3 Create Event 3 (Start Events Every Second)

mysql> show variables like '%event_scheduler%'; 
+-----------------+-------+ 
| Variable_name  | Value | 
+-----------------+-------+ 
| event_scheduler | OFF  | 
+-----------------+-------+ 

1

3.3. 4 Create Event 4 (Call Stored Procedure Every Second)

mysql> show variables like '%event_scheduler%'; 
+-----------------+-------+ 
| Variable_name  | Value | 
+-----------------+-------+ 
| event_scheduler | OFF  | 
+-----------------+-------+ 

2

3.4 Note:

The default creation event is stored in the current library, or the library in which the specified event was created can be displayed

Only events created in the current library can be viewed through show events

Events are released when they are executed. If the event is executed immediately, the event will be automatically deleted after execution, and the event can be viewed by calling the event many times or waiting for execution.

If two events need to be called at the same time, mysql determines the order in which they are called. If you want to specify the order, you need to ensure that one event executes at least one second after the other event

For recursively scheduled events, the end date cannot be before the start date.

select can be included in an event, but its result disappears as if it had not been executed.

4 View Events

View the events of the current library

mysql> show events;

View All Events

mysql> show variables like '%event_scheduler%'; 
+-----------------+-------+ 
| Variable_name  | Value | 
+-----------------+-------+ 
| event_scheduler | OFF  | 
+-----------------+-------+ 

4