Mysql Job (Scheduler)

Introduction

A job is also called an event dispatch, which is actually a time trigger; it can define a point in time to execute the specified database command operation.

Grammar

CREATE    [definer = {User | Current_User}] ##### #定义创建人, the user who created the event by default is the person who defined the event and must have super privileges to specify another user. EVENT ##### #创建事件[IF not EXISTS] ##### #删除并判断是否有存在重名的作业event_name ##### #作业名 onSCHEDULE SCHEDULE ##### #ON SCHEDULE is a fixed syntax, SCHEDULE defines the time to execute[On completion [ not]PRESERVE][ENABLE | DISABLE | DISABLE on SLAVE] ##### #启用或者禁用或者在从库上禁用    [COMMENT ' COMMENT '] ##### #备注Do ##### #规定语法
Event_body; ##### #操作命令Schedule:       at timestamp [+ INTERVAL INTERVAL] ... # # # #定义执行的时间, the command executes once at a specified time, and INTERVAL is executed on the basis of timestamp () on a date_add () command, in the specified Time based on adding a time, where the interval parameter has a lot of specific time units specific reference below | Every interval # # # #EVERY是重复执行的命令和AT是冲突的[starts timestamp [+ INTERVAL INTERVAL] ...] # # # #定义开始时间 (can be ignored), here you can also add a time based on the specified start time [ENDS timestamp [+ INTERVAL in Terval] ...] # # # #定义结束时间 (can be ignored)interval: # # #时间参数Quantity { Year |QUARTER| MONTH |  Day |HOUR|MINUTE|WEEK|SECOND|Year_month|Day_hour|Day_minute|Day_second|Hour_minute|Hour_second|Minute_second}

Note: At and every are mutually exclusive operations; at: Executed only once at the specified time, every: Repeated execution.

The time format of the combination is only a combination of "date and time", no combination of week

Because MySQL syntax is very flexible, it supports a number of time formats, similar to the following:

1Add 2 minutes and 10 seconds on a given time basis.At '2016-06-01 22:00:00'+ INTERVAL '2:10'Minute_second is equivalent to
At ' 2016-06-01 22:00:00 ' + INTERVAL 2 MINUTE + INTERVAL SECOND2On the current time basis plus 3 weeks 2 days at Current_timestamp+ INTERVAL 3WEEK + INTERVAL2Day
3. Specify the start and end time, from the current time plus 30 minutes to the current time plus 4 weeks end every 12 hours to execute a command
EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

Note: Every does not support the "+ INTERVAL" format, for example "+ INTERVAL 2 MINUTE + INTERVAL" can only be replaced with "' SECOND ' 2:10"

View Job feature no enable

Show  global'%event_scheduler%'; Select @ @event_scheduler;

If you do not enable the need to modify MY.CNF, join

event_scheduler=1

Create Job

1. Execute commands at a specified point in time

' 2016-06-27 22:00:00 '
COMMENT ' at time ' 2016-06-27 22:00:00 ' EXEC INSERT tevent '
Doinsert into study. Tevent () VALUES (now ());

2. Delimiter definition terminator is also supported

Delimiter | CREATE EVENT e_daily    On SCHEDULE      1 " Saves total number of sessions then clears the table eachd     Ay'do      BEGIN        INSERT to Site_activity.totals (time, total);          SELECT Current_timestamp, COUNT (*) from site_activity.sessions;        DELETE from site_activity.sessions;      | delimiter;

3. Cycle

Delimiter | CREATE EVENT e    On SCHEDULE      5  SECOND    does      BEGIN        DECLARE v INTEGER;        DECLARE CONTINUE HANDLER for SQLEXCEPTION BEGIN END;         0 ;         5 do          INSERT into T1 VALUES (0);           1 ;           1 ;        END while;    | delimiter;

4. Define start and end times

4 Weekdoinsertinto study. Tevent () VALUES (now ());

View jobs created in the database

SELECT * from Information_schema.events;

Enable disabled jobs

1  schema. Event_Name ENABLE2schema. Event_Name DISABLE

Delete Job

Event schema. EventName

Official Document: Http://dev.mysql.com/doc/refman/5.6/en/create-event.html

Summary

The support for common MySQL syntax operations is supported by do, and all can execute a single command, can execute a command string, can also type functions and stored procedures to execute a looping statement, you can call a stored procedure directly, and so on.

Note: Each job has a database to which it belongs, and the job belongs to a specific database.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

Mysql Job (Scheduler)