Explain how mysql implements scheduled tasks through examples

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!