Detailed Description: MySQL uses Event Scheduler to create a scheduled task, mysqlscheduler
Preface
The event scheduler is equivalent to scheduled tasks in the operating system (for example, scheduled tasks in cron and Window in Linux), but the MySql event scheduler can be precise to seconds, it is very useful for data processing with high real-time requirements.
1. Create/modify an EVENT)
In MySql, CREATE a new scheduler to use the create event. The syntax rules are as follows:
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;
The preceding statement contains the following parameters:
1,event_name
-Event name, which can be any MySql identifier and cannot exceed 64 characters.
When creating an event, you can specifySchema
, Syntax structure:schema_name.event_name
2,schedule
-Scheduling rules: Specify the event execution time and execution rules. Is a substatement that can contain the following values:
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}
3,event_body
-The event body can be a single-line SQL syntax or a BEGIN ...... END statement Block
You can useSHOW
Language Name:
SHOW EVENTS;
Event Modification
For an existing event scheduler, you can useALTER
Statement. The syntax structure is as follows:
ALTER [DEFINER = { user | CURRENT_USER }] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] [DO event_body]
The essence of enabling and disabling an event is to useALTER
Statement to modify the created event. For example,Close an event:
ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;
Enable an event:
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;
Some event examples
In the simplest examplemyschema.mytable
Tablemycol
Column, auto-increment by 1 per hour:
CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1;
In this way, we createmyevent
Is executed every hour after the event is created. The execution rules are equivalent:
CREATE EVENT myevent ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP DO UPDATE myschema.mytable SET mycol = mycol + 1;
If you need to enable the transaction at a certain interval, for example, enable the transaction after 1 day:
CREATE EVENT myevent ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY DO UPDATE myschema.mytable SET mycol = mycol + 1;
DO
The executed SQL statement can be a statement block, such:
DELIMITER // CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET v = 0; WHILE v < 5 DO INSERT INTO t1 VALUES (0); UPDATE t2 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END // DELIMITER ;
2. Configure the event Scheduler
2.1 event scheduler status
To ensure that the created event can be executed properly, enable the event scheduler. You can view the scheduler status in the following three ways:
SHOW VARIABLES LIKE 'event_scheduler';SELECT @@event_scheduler;SHOW PROCESSLIST;
View the execution status of an event:
SELECT * FROM information_schema.EVENTS;
The above will output when offSchema
All event information, you can firstDESC information_schema.EVENTS;
View the output field, and then view the required information. For example, I only want to view the event name and the last execution time:
SELECT EVENT_NAME, LAST_EXECUTED FROM information_schema.EVENTS;
2.2 enable/disable the event Scheduler
If the event scheduler is not enabled, you can enable it in the following four ways:
SET GLOBAL event_scheduler = 1;SET @@global.event_scheduler = 1;SET GLOBAL event_scheduler = ON;SET @@global.event_scheduler = ON;
1 or ON indicates that it is enabled. Similarly, if you want to disable it, you only need to set the value to 0 or OFF.
Summary
The above is all about using Event Scheduler in MySQL to create a scheduled task. I hope this article will help you learn how to use MySQL.