Shulou(Shulou.com)06/01 Report--

This paper gives an example to describe the implementation and use of mysql timing task. Share with you for your reference, the details are as follows:

Mysql5.1.6 adds an event Scheduler (Event Scheduler), which can do scheduled tasks (scheduled deletion of records, scheduled data statistics) to replace the scheduled tasks of the previous system. The mysql event Scheduler can perform one task per second with precision.

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

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

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

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

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.