Events and scheduled tasks in Mysql

I. Introduction

Event is a procedural database object called by MySQL at the corresponding moment.It is a tool of MYSQL. An event can be called once or started periodically. It is managed by a specific thread, which is the so-called "event scheduler".

Second, create grammar

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 |

Glossary

event_name : The name of the created event (uniquely determined).
ON SCHEDULE: Scheduled tasks.
schedule: Determine the execution time and frequency of the event (note that the time must be in the future, the past time will be wrong), there are two forms AT and EVERY.
[ON COMPLETION [NOT] PRESERVE]: Optional, the default is ON COMPLETION NOT PRESERVE, that is, the event will be automatically dropped after the scheduled task is executed; ON COMPLETION PRESERVE will not drop it.
[COMMENT 'comment'] : optional, comment is used to describe the event; quite a comment, the maximum length is 64 bytes.
[ENABLE | DISABLE]: Set the state of the event, the default ENABLE: means that the system tries to execute the event, DISABLE: close the event, you can modify it with alter
DO event_body: the SQL statement to be executed (can be a compoundstatement).CREATE EVENT is legal when used in a stored procedure.

Three, open and close the event scheduler

1. Check if it is turned on

show variables like '%event_scheduler%';

The default is off, not enabled

2. Open the event scheduler

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

Or through the configuration file my.cnf

event_scheduler = 1 #or ON

View scheduler threads:

show processlist; 

3, close the event scheduler

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

4. View scheduler thread tasks

SELECT @@event_scheduler;SHOW PROCESSLIST;show events; #View the current schema scheduling tasksselect * from mysql.event;#View the entire database scheduling tasks

Four, create a test table

1. Simple omission

2. Create event 1 (start the event immediately)

create event event_nowon scheduleat now()do insert into events_list values('event_now', now());

3. Create event 2 (start event every minute)

create event test.event_minuteon scheduleevery 1 minutedo insert into events_list values('event_now', now());

4. Create event 3 (start event every second)

CREATE event event_nowON SCHEDULEEVERY 1 SECONDDO INSERT INTO event_test VALUES(1);

5. Create event 4 (call the stored procedure every second)

CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus`ON SCHEDULE EVERY 1 SECONDSTARTS '2017-11-21 00:12:44'ON COMPLETION PRESERVEENABLEDO call updateStatus()