Introduction to MySQL event scheduler, mysql event Scheduler
How to Use the MySQL event Scheduler
The Event sched feature has been added since MySQL5.1.0. It can be used for scheduled execution of certain tasks and can be seen as a time-based trigger.
1. Enable
By default, event scheduling is disabled and executable is enabled.
SET GLOBAL event_scheduler=1;SET GLOBAL event_scheduler=ON;
Or add event_scheduler = 1 to the my. ini file.
Or Add "-event_scheduler = 1" after the command is started"
Run the following command to check whether the event scheduler is Enabled.
SHOW VARIABLES LIKE 'event_scheduler';SELECT @@event_scheduler;
Ii. Create
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
Event_name: name of the event you want to create
Schedule: it is an execution plan. There are two options: first, execution at a certain time, and second, execution at intervals from a certain time to a certain time.
INTERVAL: INTERVAL, which can be accurate to seconds (
Http://www.lai18.com/content/433742.html).
On completion [NOT] PRESERVE: whether to save after execution. It is NOT saved by default. Once execution is complete, the event is deleted. Therefore, we strongly recommend that you set this parameter to on completion preserve.
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
It will be executed five days from now on
ON SCHEDULE AT TIMESTAMP '2012-03-07 12:00:00'
Execute at a specific time
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAYENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
Execute the task every day after 5 days and end after one month.
CURRENT_TIMESTAMP can be replaced by a specific time, for example, '2017-03-06 18:00:00'
CREATE EVENT `NewEvent`ON SCHEDULE EVERY 1 MONTH STARTS '2012-04-01 00:00:00' ENDS '2100-01-01 00:00:00'ON COMPLETION PRESERVEENABLEDOupdate tb_test set amount=100 where id=2;;
This is a complete example.
3. Modify
ALTER EVENT event_name [ON SCHEDULE schedule] [RENAME TO new_event_name] [ON COMPLETION [NOT] PRESERVE] [COMMENT 'comment'] [ENABLE | DISABLE] [DO sql_statement]
ALTER EVENT e_test DISABLE;
Disable the e_test event.
Note that once MySQL is restarted, all Disable events will disappear.
Iv. Delete
DROP EVENT [IF EXISTS] event_name