MySQL timed Task Event Scheduler usage Detailed

1. Open

The global variable Event_scheduler is used to set whether the server side runs the execution plan task, which has the following three kinds of values:
OFF: The scheduled task is in a stopped state and the event Scheduler thread is not running. is the default value of Event_scheduler (perform any one of the commands close);

The code is as follows	Copy Code
SET GLOBAL event_scheduler = off; SET @ @global. Event_scheduler = off; SET GLOBAL event_scheduler = 0; SET @ @global. Event_scheduler = 0;

On: The scheduled task is running, the event scheduler thread starts, and performs all the scheduled tasks (the execution of any command opens);

The code is as follows	Copy Code
SET GLOBAL event_scheduler = on; SET @ @global. Event_scheduler = on; SET GLOBAL event_scheduler = 1; SET @ @global. Event_scheduler = 1;

DISABLED: This value will cause the scheduled task to not run
Add parameters when running MySQL service

The code is as follows	Copy Code
--event-scheduler=disabled

or set in MY.CNF.

View Source code Printing Help
1 event_scheduler=disabled
MySQL's event scheduler is a database object that is based on a prearranged schedule, and can be considered a "time trigger." The event is actually executed by a special event scheduler thread, and if it is running, you can see it through the show processlist command.

2. Create

The basic syntax to create is:

code is as follows	copy code
1 CREATE event        (1)   2 [IF not exists]         (2)   3 event_name         (3)   4 on SCHEDULE schedule      (4)   5 [on completion [NOT] preserve]   (5) &NBSP 6 [ENABLE | disable]       (6)   7 [COMMENT ' COMMENT ']        (7)   8 do sql_statement        (8)

Introduction by article:
(1) Create an event, these two keywords of course not less
(2) Create a database object that does not have the same name
(3) The name of the scheduled task. As a database object, there is a unique name in this library to do the identification
(4) Plan the task, the focus is here. There are two keywords to set the task's execution plan: at and every:
At specifies a one-time schedule followed by a timestamp, and subsequent SQL statements are executed once at the specified time;
Every specifies a recurring schedule in which you can specify that the task is executed once every other period from a point to a point in time.
(5) Whether the scheduled Task object is also retained in the database after the complete execution of the task. Default does not hold
(6) valid or invalid. Default valid
(7) The statements that are actually to be executed

3. Example

1. Inserts a timestamp into the myschema.mytable table every 10 seconds:

The code is as follows	Copy Code
CREATE EVENT e_store_ts on SCHEDULE EVERY SECOND does INSERT into myschema.mytable VALUES (Unix_timestamp ());

2. The previous 10 records of the messages table were deleted every 1 hours during the year beginning in 2008-02-06 23:59:00:

The code is as follows	Copy Code
CREATE EVENT e_hourly On SCHEDULE EVERY 1 HOUR starts ' 2007-02-10 23:59:00′ends ' 2008-02-10 23:59:00′ Todo DELETE from messages limit 10;

4. View Event

The code is as follows

Copy Code

Mysql>select * from INFORMATION_SCHEMA. EVENTS > WHERE event_name= ' e_store_ts ' > and event_schema= ' MySchema ' G 1. Row *************************** Event_catalog:null Event_schema:myschema Event_name:e_store_ts Definer:jon@ghidora Event_body:sql Event_definition:insert into Myschema.mytable VALUES (Unix_timestamp ()) Event_type:recurring Execute_at:null Interval_value:5 Interval_field:second Sql_mode:null starts:0000-00-00 00:00:00 ends:0000-00-00 00:00:00 Status:enabled On_completion:not PRESERVE created:2006-02-09 22:36:06 last_altered:2006-02-09 22:36:06 Last_executed:null Event_comment: 1 row in Set (0.00 sec)

5. Delete Event

The code is as follows	Copy Code
DELETE from Mysql.event WHERE db = ' MySchema ' and Definer = ' Jon@ghidora ' and name = ' E_insert ';