MySQL Create timed tasks

First, preface
since MySQL5.1.6, has added a very distinctive feature – event scheduler, which can be used to perform certain tasks on a timed basis (for example: Deleting records, summarizing data, etc.). To replace work that was previously only performed by the operating system's scheduled tasks. It is also worth mentioning that the MySQL event Scheduler can perform one task per second, while the operating system's scheduled tasks (such as Cron under Linux or Scheduled tasks under Windows) can only be performed once per minute. For some applications that require a higher level of real-time data (e.g., stocks, odds, scores, etc.), it is very suitable. The
Event Scheduler can sometimes also be called a temporary trigger (temporal triggers) because the event scheduler performs certain tasks based on a specific time period trigger, and the trigger (triggers) is triggered based on events generated by a table, and the difference is here.　　
Before using this feature, you must ensure that Event_scheduler is turned on, and you can execute

SET GLOBAL event_scheduler = 1;
　　---or we can add Event_scheduler = 1

or

SET GLOBAL event_scheduler = On in the configuration my.cnf file;

to open, or you can add "--event_scheduler=1" directly to the start command, for example:

mysqld ...--event_scheduler=1

to see if the event Scheduler is currently turned on, perform the following　　SQL:

SHOW VARIABLES like ' Event_scheduler ';
　　
or

SELECT @ @event_scheduler;
　　
or

SHOW processlist;

Ii. Create event
First look at its syntax:

1	CREATEEVENT [IFNOTEXISTS] event_name

2	ONSCHEDULE schedule

3	[ONCOMPLETION [NOT] PRESERVE]

4	[ENABLE | DISABLE]

5	[COMMENT ‘comment‘]

6	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}
　　
1) First look at a simple example to demonstrate inserting a record into a data table per second
　　
　　

1

USE test;

2	CREATETABLEaaa (timeline TIMESTAMP);

3	CREATEEVENT e_test_insert

4	ONSCHEDULE EVERY 1SECOND

5	DO INSERTINTOtest.aaa VALUES(CURRENT_TIMESTAMP);

　　
Wait 3 seconds, then execute the query to see:
　　
Mysql> SELECT * from AAA;
+---------------------+
| Timeline |
+---------------------+
| 2007-07-18 20:44:26 |
| 2007-07-18 20:44:27 |
| 2007-07-18 20:44:28 |
+---------------------+
　　
2) Empty the test table after 5 days:
　　
　　

1	CREATEEVENT e_test

2	ONSCHEDULEATCURRENT_TIMESTAMP+ INTERVAL 5 DAY

3	DO TRUNCATETABLEtest.aaa;

　　
3) July 20, 2007 12 O'Clock full empty test table:
　　
　　

1	CREATEEVENT e_test

2	ONSCHEDULEATTIMESTAMP‘2007-07-20 12:00:00‘

3	DO TRUNCATETABLEtest.aaa;

　　
4) Clear the test table periodically every day:
　　
　　

1	CREATEEVENT e_test

2	ONSCHEDULE EVERY 1DAY

3	DO TRUNCATETABLEtest.aaa;

　　
5) After 5 days, open the test table at regular intervals every day:
　　
　　

1	CREATEEVENT e_test

2	ONSCHEDULE EVERY 1DAY

3	STARTS CURRENT_TIMESTAMP+ INTERVAL 5DAY

4	DO TRUNCATETABLEtest.aaa;

　　
6) Clear the test table every day and stop execution after 5 days:
　　
　　

1	CREATEEVENT e_test

2	ONSCHEDULE EVERY 1DAY

3	ENDS CURRENT_TIMESTAMP+ INTERVAL 5DAY

4	DO TRUNCATETABLEtest.aaa;

　　
7) After 5 days, open the test table periodically, and stop execution after one months:
　　
　　

1	CREATEEVENT e_test

2	ONSCHEDULE EVERY 1DAY

3	STARTS CURRENT_TIMESTAMP+ INTERVAL 5DAY

4	ENDS CURRENT_TIMESTAMP+ INTERVAL 1MONTH

5	DO TRUNCATETABLEtest.aaa;

　　
[on completion [NOT] PRESERVE] can set whether this event is executed once or persisted, default to not PRESERVE.
8) Clear the test table every day (only once and terminate the event when the task is completed):
　　
　　

1	CREATEEVENT e_test

2	ONSCHEDULE EVERY 1DAY

3	ONCOMPLETIONNOTPRESERVE

4	DO TRUNCATETABLEtest.aaa;

　　
[ENABLE | DISABLE] But sets whether the state is turned on or off after the event is created, and the default is enable.
[COMMENT ' COMMENT '] can add comments to the event.
III. modifying events (Alter event)
　　
　　

1	ALTEREVENT event_name

2	[ONSCHEDULE schedule]

3	[RENAME TOnew_event_name]

4	[ONCOMPLETION [NOT] PRESERVE]

5	[COMMENT ‘comment‘]

6	[ENABLE | DISABLE]

7	[DO sql_statement]

　　
1) Temporary Shutdown event
　　
ALTER EVENT e_test DISABLE;
　　
2) Turn on event
　　
ALTER EVENT e_test ENABLE;
　　
3) Change the daily empty test table to 5 days to clear:
　　
ALTER EVENT E_test
On SCHEDULE every 5 day;
　　
Iv. Delete events (drop event)
The syntax is simple, as follows:
　　
DROP EVENT [IF EXISTS] Event_Name
　　
For example, delete the previously created E_test event
　　
DROP EVENT e_test;
　　
Of course the premise is that this event exists, otherwise it will generate error 1513 (HY000): Unknown event error, so it is best to add if EXISTS
　　
DROP EVENT IF EXISTS e_test;
Note: If you have performed the event, alter event event_name disable. Then when you restart the MySQL service
The event will be deleted (Beta version: 5.1.30)
　　
Application case
In this case, the event scheduler is used to invoke a stored procedure every second to determine if the SLAVE is functioning properly, if SLAVE is turned off, 0 errors are ignored, and SLAVE is restarted.
　　
* Create a stored procedure first
　　
　　

01	delimiter //

02	createprocedure`Slave_Monitor`()

03

begin

04	SELECTVARIABLE_VALUEINTO@SLAVE_STATUS

05	FROMinformation_schema.GLOBAL_STATUS

06	WHEREVARIABLE_NAME=‘SLAVE_RUNNING‘;

07	IF (‘ON‘!= @SLAVE_STATUS)THEN

08	SETGLOBALSQL_SLAVE_SKIP_COUNTER=0;

09	SLAVE START;

10

ENDIF;

11

end; //

12	delimiter ;

　　
Because statements like SHOW SLAVE STATUS cannot be called in a stored procedure, exact replication error messages and error codes cannot be obtained and further processing of SLAVE stops is not possible.
* Next, create a task
　　
　　

1	CREATEEVENT IFNOTEXISTS `Slave_Monitor`

2	ONSCHEDULE EVERY 5SECOND

3	ONCOMPLETION PRESERVE

4

DO

5	CALL Slave_Monitor();

　　
　　
A task is created, executed every 5 seconds, and the task is retained after the task is completed, rather than deleted. Of course, the task in this example will not end unless it is manually disabled.
*
If you want to temporarily close a task while running, execute the ALTER EVENT statement:
　　
(root:localhost:) test> alter event ' Slave_monitor ' on
Completion PRESERVE DISABLE;
(root:localhost:) test> alter event ' Slave_monitor ' on
Completion PRESERVE ENABLE;

[Original from: http://blog.csdn.net/mer1234567/article/details/7514855]