MySQL scheduled task implementation and usage examples

This article uses examples to illustrate the implementation and use of MySQL scheduled tasks. Share with you for your reference, the details are as follows:

MySQL 5.1.6 adds an event scheduler that can perform scheduled tasks (scheduled deletion of records, scheduled data statistics), replacing the previous system's scheduled tasks. The MySQL event scheduler can execute a task accurately per second.

The difference between event scheduler and trigger: event scheduler triggers the execution of certain tasks based on a specific time period, while trigger is triggered based on the events generated by a certain table.

1. Check whether it is turned on

> show variables like 'event_scheduler';

2. Enable the event scheduler

set global event_scheduler = on;

The settings here will be automatically closed when MySQL is restarted. If you need to keep it turned on, you need to configure it in my.ini as follows:

event_scheduler = on

3. Create event 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 }

event_name: event name, with a maximum length of 64 characters.

schedule: execution time.

[ON COMPLETION [NOT] PRESERVE]: Whether the event needs to be reused.

[ ENABLE | DISABLE ] : Enable or disable the event.

4. Closing Events

ALTER EVENT event_name DISABLE;

5. Opening events

ALTER EVENT event_name ENABLE;

6. Deleting events

DROP EVENT [IF EXISTS ] event_name;

7. View all events

SHOW EVENTS;

8. Event Examples

Let's 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 triggering and the other is specific time triggering.

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 'Insert records into the test table every second'
DO INSERT INTO test VALUES(NULL, now());

We specify the time to insert a record into the test table:

DROP EVENT IF EXISTS event_test2;
CREATE EVENT event_test2
ON SCHEDULE AT '2017-08-22 12:01:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Insert records into the test table at specified time'
DO INSERT INTO test VALUES(999999, now());

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.