First, preface
Since MySQL5.1.6, a feature has been added – Event Scheduler (Scheduler), which can be used to perform certain tasks on a timed basis (for example, deleting records, summarizing data, and so on) to replace work that was previously only performed by the operating system's scheduled tasks. It is also worth mentioning that the MySQL event Scheduler can perform one task per second, while the operating system's scheduled tasks (such as Cron under Linux or Scheduled tasks under Windows) can only be performed once per minute. For some applications that require a higher level of real-time data (e.g., stocks, odds, scores, etc.), it is very suitable.
The event scheduler can sometimes also be called a temporary trigger (temporal triggers) because the event scheduler performs certain tasks based on a specific time period trigger, and the trigger (triggers) is triggered based on events generated by a table, and the difference is here.
Before you can use this feature, you must ensure that Event_scheduler is turned on and executable
SET GLOBAL event_scheduler = 1;
Or
SET GLOBAL event_scheduler = on;
To open, you can also add "–event_scheduler=1" directly to the start command, for example:
Mysqld ...--event_scheduler=1
To see if the event Scheduler is currently turned on, perform the following sql:
SHOW VARIABLES like ' Event_scheduler ';
Or
SELECT @ @event_scheduler;
Or
SHOW processlist;
Ii. Creating events (Create Event)
First look at its syntax:
CREATE EVENT [IF not EXISTS] Event_Name
On SCHEDULE SCHEDULE
[on completion [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ' COMMENT ']
Do sql_statement;
Schedule
at TIMESTAMP [+ INTERVAL INTERVAL]
| Every INTERVAL [starts TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
Quantity {Year | QUARTER | MONTH | Day | HOUR | MINUTE |
WEEK | SECOND | Year_month | Day_hour | Day_minute |
Day_second | Hour_minute | Hour_second | Minute_second}
1) First look at a simple example to demonstrate inserting a record into a data table per second
Use test;
CREATE TABLE aaa (Timeline TIMESTAMP);
CREATE EVENT E_test_insert
Do INSERT into Test.aaa VALUES (current_timestamp);
Wait 3 seconds, then execute the query to see:
Mysql> SELECT * from AAA;
| Timeline
+---------------------+
2) Empty the test table after 5 days:
CREATE EVENT E_test
On SCHEDULE @ current_timestamp + INTERVAL 5 Day
Do TRUNCATE TABLE test.aaa;
3) July 20, 2007 12 O'Clock full empty test table:
CREATE EVENT E_test
On SCHEDULE at TIMESTAMP ' 2007-07-20 12:00:00 '
Do TRUNCATE TABLE test.aaa;
4) Clear the test table periodically every day:
CREATE EVENT E_test
On SCHEDULE every 1 day
Do TRUNCATE TABLE test.aaa;
5) After 5 days, open the test table at regular intervals every day:
CREATE EVENT E_test
On SCHEDULE every 1 day
Starts Current_timestamp + INTERVAL 5 Day
Do TRUNCATE TABLE test.aaa;
6) Clear the test table every day and stop execution after 5 days:
CREATE EVENT E_test
On SCHEDULE every 1 day
ENDS Current_timestamp + INTERVAL 5 Day
Do TRUNCATE TABLE test.aaa;
7) After 5 days, open the test table periodically, and stop execution after one months:
CREATE EVENT E_test
On SCHEDULE every 1 day
Starts Current_timestamp + INTERVAL 5 Day
ENDS Current_timestamp + INTERVAL 1 MONTH
Do TRUNCATE TABLE test.aaa;
[on completion [NOT] PRESERVE] can set whether this event is executed once or persisted, default to not PRESERVE.
8) Clear the test table every day (only once and terminate the event when the task is completed):
CREATE EVENT E_test
On SCHEDULE every 1 day
On completion not PRESERVE
Do TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE] But sets whether the state is turned on or off after the event is created, and the default is enable.
[COMMENT ' COMMENT '] can add comments to the event.
III. modifying events (Alter event)
ALTER EVENT Event_Name
[on SCHEDULE SCHEDULE]
[RENAME to New_event_name]
[on completion [NOT] PRESERVE]
[COMMENT ' COMMENT ']
[ENABLE | DISABLE]
[Do sql_statement]
1) Temporary Shutdown event
ALTER EVENT e_test DISABLE;
2) Turn on event
ALTER EVENT e_test ENABLE;
3) Change the daily empty test table to 5 days to clear:
ALTER EVENT E_test
On SCHEDULE every 5 day;
Iv. Delete events (drop event)
The syntax is simple, as follows:
DROP EVENT [IF EXISTS] Event_Name
For example, delete the previously created E_test event
DROP EVENT e_test;
Of course the premise is that this event exists, otherwise it will generate error 1513 (HY000): Unknown event error, so it is best to add if EXISTS
DROP EVENT IF EXISTS e_test;
Temporary shutdown events
ALTER EVENT test_event DISABLE;
Turn on events
ALTER EVENT test_event ENABLE;
Delete a scheduled task (drop EVENT)
DROP EVENT [IF EXISTS] Event_Name
If an error 1513 (HY000) is generated: Unknown event error, it is best to add if EXISTS
Real-world scenarios: Events + stored Procedures
The usual scenario is to call the stored procedure periodically through events, and here's a simple example:
Create a stored procedure that adds cardinality 2 to each row of the Id2 field of the test table, with the following stored procedure Code:
12345678910111213141516171819 |
DROP PROCEDURE IF EXISTS test_add;
DELIMITER
CREATE PROCEDURE test_add()
BEGIN
DECLARE 1_id
INT DEFAULT 1;
DECLARE 1_id2
INT DEFAULT 0;
DECLARE error_status
INT DEFAULT 0;
DECLARE datas
CURSOR FOR SELECT id
FROM test;
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET error_status=1;
OPEN datas;
FETCH datas
INTO 1_id;
REPEAT
SET 1_id2=1_id2+2;
UPDATE test
SET id2=1_id2
WHERE id=1_id;
FETCH datas
INTO 1_id;
UNTIL error_status
END REPEAT;
CLOSE datas;
END
|
Event Settings 2012-08-22 00:00:00 start running, every 1 calls to the stored procedure, 40 days after the end, the code is as follows:
123456 |
view sourceprint? CREATE EVENT test ON SCHEDULE EVERY 1 DAY STARTS ‘2012-08-22 00:00:00‘ ENDS ‘2012-08-22 00:00:00‘ +INTERVAL 40 DAY ON COMPLETION PRESERVE DO CALL test_add(); |
MySQL Event Scheduler feature