The method of realizing timing Task by mysql

Since MySQL5.1.6, a very unique function-event Scheduler (Event Scheduler) has been added, which can be used to perform certain tasks on a regular basis (such as deleting records, summarizing data, backing up data, etc.) to replace work that can only be performed by scheduled tasks of the operating system.

What is more worth mentioning is that MySQL's event scheduler can be accurate to execute one task per second, while the operating system's scheduled tasks (such as tasks under Linux's cron or Windows) can only be executed exactly once per 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 the event scheduler performs certain tasks based on specific time period triggers, while triggers (Triggers) are triggered based on events generated by a table, and that's the difference.

First, check whether it is open or not.

> show variables like 'event_scheduler'

Open the event Scheduler

Set global event_scheduler = on

The settings here will be turned off automatically when mysql is restarted. If you need to enable it all the time, you need to configure it in my.ini as follows:

Event_scheduler = on

Third, create event syntax

CREATE EVENT [IF NOT EXISTS] event_nameON 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}

Event_name: event name with a maximum length of 64 characters.

Schedule: execution time.

[ON COMPLETION [NOT] PRESERVE]: whether events need to be reused.

[ENABLE | DISABLE]: the event is on or off.

IV. Shutdown event

ALTER EVENT event_name DISABLE

Fifth, open the event

ALTER EVENT event_name ENABLE

VI. Delete events

DROP EVENT [IF EXISTS] event_name

View all events

SHOW EVENTS

VIII. Examples of events

Let's first create a simple test table for testing

CREATE TABLE `test` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `now` datetime DEFAULT NULL COMMENT' time', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

There are two types of events, one is interval trigger, the other is specific time trigger.

We insert a record into the test table every other second:

DROP EVENT IF EXISTS event_test;CREATE EVENT event_testON SCHEDULE EVERY 1 SECOND STARTS '2017-08-22 11 15 ENDS' 2017-08-22 12:00:00'ON COMPLETION PRESERVEENABLECOMMENT 'insert records' DO INSERT INTO test VALUES (NULL, now ()) into the test table every other second

The result is shown in the figure:

These are the details of how to achieve scheduled tasks in mysql through examples. Please pay more attention to other related articles!