Since MySQL 5.1.6, a very distinctive feature has been added - Event Scheduler, which can be used to perform certain specific tasks on a scheduled basis (for example: deleting records, Data aggregation, data backup, etc.) to replace the work that could only be performed by scheduled tasks of the operating system.
What’s more worth mentioning is that MySQL’s event scheduler can be accurate to execute one task every second, while the operating system’s scheduled tasks (such as Linux’s cron or task schedule under Windows) can only be accurate to Executed every minute. It is very suitable for some applications that require high real-time data (such as stocks, odds, scores, etc.).
Event schedulers can sometimes be called temporary triggers (temporal triggers), because event schedulers are triggered based on specific time periods to perform certain tasks, while triggers (Triggers) are triggered based on a certain table. The generated event is triggered, and that's where the difference lies.
1. Check whether it is enabled
> show variables like 'event_scheduler';
2. Enable the event scheduler
set global event_scheduler = on;
The settings here, when mysql restarts Afterwards, it will automatically close again. If you need to enable it all the time, you need to configure it in my.ini as follows:
(Recommended learning video tutorial:mysql video tutorial)
event_scheduler = on
三, Create event syntax
CREATE EVENT [IF NOT EXISTS ] event_name ON SCHEDULE schedule [ ON COMPLETION [ NOT ] PRESERVE ] [ ENABLE | DISABLE ] [ COMMENT '注释' ] DO SQL语句; 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 }
event_name: event name, maximum length 64 characters.
schedule: execution time.
[ ON COMPLETION [ NOT ] PRESERVE ]: Whether the event needs to be reused.
[ ENABLE | DISABLE ]: The event is turned on or off.
4. Close event
ALTER EVENT event_name DISABLE;
5. Open event
ALTER EVENT event_name ENABLE;
6. Delete event
DROP EVENT [IF EXISTS ] event_name;
7. View all events
SHOW EVENTS;
8. Event examples
Let’s first create a simple The test table is used for testing
CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `now` datetime DEFAULT NULL COMMENT '时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
There are two types of events, one is interval triggering and the other is triggered at a specific time.
We insert a record into the test table every second:
DROP EVENT IF EXISTS event_test; CREATE EVENT event_test ON SCHEDULE EVERY 1 SECOND STARTS '2017-08-22 11:57:00' ENDS '2017-08-22 12:00:00' ON COMPLETION PRESERVE ENABLE COMMENT '每隔一秒向test表插入记录' DO INSERT INTO test VALUES(NULL, now());
The result is as shown in the figure:
Related article tutorials Recommended:mysql tutorial
The above is the detailed content of Explain how mysql implements scheduled tasks through examples. For more information, please follow other related articles on the PHP Chinese website!