MySQL timed Task Event Scheduler usage Detailed

Source: Internet
Author: User

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 ';

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.