MySQL Scheduled Tasks

1. Check if your MySQL has this function

SHOW VARIABLES like ' Event_scheduler ';

I executed the discovery record as empty description my MySQL didn't open. OK solution

2. Open your MySQL's Scheduled tasks feature

Before using this feature, you must ensure that the Event_scheduler is turned on and executable

SET GLOBAL event_scheduler = 1;
or set GLOBAL Event_scheduler = on;
To open, you can also directly add "–event_scheduler=1" to the start command, for example: Mysqld ...--event_scheduler=1

My.ini or MY.CNF in the
[Mysqld]
Add Event_scheduler=on

Ii. Creating events (Create Event)
First look at its syntax:

CREATE EVENT [IF not EXISTS] Event_Name
On SCHEDULE SCHEDULE
[on completion [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ' COMMENT ']
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}

#单次计划任务: at timestamp
#重复的计划任务 the number of every time units (units) time unit [starts timestamp][ends timestamp]
In both scheduled tasks, the time stamp can be any of the TIMESTAMP and datetime data types, requiring a future time (greater than current_timestamp), and less than the last time of the Unix time (equal to or less than the ' 2037-12-31 23:59:59 ')
Time units are keywords: year,month,day,hour,minute or second
3.[on completion [NOT] PRESERVE] completion when a single scheduled task is completed or when a recurring scheduled task is performed to the ends stage. and declaring preserve's role is to make the event not be dropped after the execution is complete.
4.[enable| Desable] Enable Open event desable Shutdown event
5.COMMENT comments
SQL statements executed by 6.DO sql_statement

1) First look at a simple example to demonstrate inserting a record into a data table per second

Use test;
CREATE TABLE aaa (Timeline TIMESTAMP);
CREATE EVENT E_test_insert
On SCHEDULE every 1 SECOND
Do INSERT into Test.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:

CREATE EVENT E_test
On SCHEDULE @ current_timestamp + INTERVAL 5 Day
Do TRUNCATE TABLE test.aaa;
3) July 20, 2007 12 O'Clock full empty test table:

CREATE EVENT E_test
On SCHEDULE at TIMESTAMP ' 2007-07-20 12:00:00 '
Do TRUNCATE TABLE test.aaa;
4) Clear the test table periodically every day:

CREATE EVENT E_test
On SCHEDULE every 1 day
Do TRUNCATE TABLE test.aaa;
5) After 5 days, open the test table at regular intervals every day:

CREATE EVENT E_test
On SCHEDULE every 1 day
Starts Current_timestamp + INTERVAL 5 Day
Do TRUNCATE TABLE test.aaa;
6) Clear the test table every day and stop execution after 5 days:

CREATE EVENT E_test
On SCHEDULE every 1 day
ENDS Current_timestamp + INTERVAL 5 Day
Do TRUNCATE TABLE test.aaa;
7) After 5 days, open the test table periodically, and stop execution after one months:

CREATE EVENT E_test
On SCHEDULE every 1 day
Starts Current_timestamp + INTERVAL 5 Day
ENDS Current_timestamp + INTERVAL 1 MONTH
Do TRUNCATE TABLE test.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):

CREATE EVENT E_test
On SCHEDULE every 1 day
On completion not PRESERVE
Do TRUNCATE TABLE test.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)
ALTER EVENT Event_Name
[on SCHEDULE SCHEDULE]
[RENAME to New_event_name]
[on completion [NOT] PRESERVE]
[COMMENT ' COMMENT ']
[ENABLE | DISABLE]
[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;

Create Event Test
On SCHEDULE @ ' 2007-09-01 12:00:00 ' + INTERVAL 1 day
On completion not Preserve
Do insert into yyy values (' hhh ', ' UUU ');

Explanation: Starting from 2007-09-01, an insert operation was performed on the table yyy at 12:00:00 every day. and executes only once (on completion not preserve)

My Scheduled tasks are:

Create Event Sysplan
On SCHEDULE @ ' 2010-05-22 23:00:00 ' + INTERVAL 1 day
On completion not Preserve
Do truncate TABLE bjproj.ae_tmp;

Thirdly, we can dynamically control whether the event scheduler is enabled by setting the value of the global variable Event_scheduler.
See if Event_scheduler opens mysql> SHOW VARIABLES like '%event% ';
Set the mysql> set GLOBAL event_scheduler=on to open;
Iv. Examples:
Insert a log per minute: DELIMITER//CREATE EVENT ' user_log_event ' on SCHEDULE every 1 MINUTE starts ' 2010-12-27 00:00:00 ' on Completi On not PRESERVE ENABLE does BEGIN INSERT into log SET addtime=now (); END//
Call stored procedure: DELIMITER//CREATE EVENT ' user_log_event ' on SCHEDULE every 1 day starts ' 2010-00-00 00:00:00 ' on completion not PRESERVE ENABLE do BEGIN call User_log_prov (); END//Turn http://blog.chinaunix.net/u2/84280/article_100139.html

MySQL Scheduled Tasks