MySQL5.1 adds the Event Scheduler function [scheduled job]

Originally, LAMP programmers had to use the help of the operating system to complete some periodic database operation tasks, such as Windows scheduled tasks or Linux contab. MySQL5.1 has finally added the Event Scheduler function, which allows you to implement scheduled jobs directly in the background. But I don't know why. In the reference manual of the English version, the Event Scheduler in Chapter 20th between the trigger and view is removed from the Chinese version, the other part of the Manual does not mention it at all, as if it does not exist at all, so it is not very well known. For more information, see http://dev.mysql.com/doc/refman/5.1/en/events.html.

1. Enable
MySQL Event sched is a database object that performs database operations according to a pre-scheduled schedule. It can be seen as a "time trigger ". Event is actually executed by a special event scheduler thread. If it is running, you can see it through the show processlist command. The global variable event_scheduler is used to set whether the server runs the execution plan task. The variable has the following three values:
OFF: the scheduled task is stopped and the event scheduler thread is not running. Is the default value of event_scheduler;
ON: the scheduled task is running. The event scheduler thread starts and executes all the scheduled tasks;
DISABLED: This value will make the scheduled task unavailable

SET GLOBAL event_scheduler = ON;SET @@global.event_scheduler = ON;SET GLOBAL event_scheduler = 1;SET @@global.event_scheduler = 1; 

CREATE Event sched as a database object. The DDL statements for adding, deleting, and modifying Event sched are similar to those for other objects, except CREATE, ALTER, and DROP. The basic syntax for creating Event sched is as follows:

CREATE EVENT （1）[IF NOT EXISTS] （2）event_name （3）ON SCHEDULE schedule （4）[ON COMPLETION [NOT] PRESERVE] （5）[ENABLE | DISABLE] （6）[COMMENT 'comment'] （7）DO sql_statement （8）

(1) Create an Event. These two keywords cannot be missing;
(2) create a database object with the same name if it does not exist;
(3) Name of the scheduled task. As a database object, each database has a unique name for identification.
(4) Plan the task here. There are two keywords to set the task execution plan: AT and EVERY:
AT specifies a one-time plan followed by a timestamp. Subsequent SQL statements will be executed once AT the specified time;
EVERY specifies a periodic plan. In this clause, you can specify that the task is executed EVERY cycle from a certain time point to a certain time point.
(5) Whether the scheduled task object is retained in the database after the task is completed. Not retained by default
(6) valid or invalid. Valid by default
(7) An instance of the statement to be executed is an example of a one-time scheduled task. The task inserts a record into the messages table at the specified time point 23:59:00:

Create event e_totalsON schedule at '2017-02-10 23:59:00 'doinsert into messages VALUES (null, 'admin',' New Year! ',' I wish you all a fortune !!! ', '2014. 0.0.1', NOW ());

The following is an example of a periodic scheduled task. In the year starting from 23:59:00 on January 6, the task deletes the earlier 10 records of the messages table every one hour:

CREATE EVENT e_hourlyON SCHEDULEEVERY 1 HOUR STARTS '2007-02-10 23:59:00' ENDS '2008-02-10 23:59:00'DODELETE FROM messages limit 10;

The scheduled task is an object on the database server. After the task is created, you only need to enable event_scheduler to automatically trigger the task. No intervention is required on the foreground. Of course, you must remember that only MySQL and later versions support this object.