MySQL Event scheduled task

A new feature is introduced in the mysql5.1.x release, which, as the name implies, is an event, timed task mechanism that performs specific tasks within a specified time unit, so that some future data-timing operations no longer rely on external programs, and directly use the functionality provided by the database itself .

11. Check if your MySQL has this function

SHOW VARIABLES like ' Event_scheduler ';

Or

SELECT @ @event_scheduler;

if shown:

+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| Event_scheduler | OFF |
+-----------------+-------+
you can perform

SET GLOBAL event_scheduler = 1;

Or

SET GLOBAL event_scheduler = on;

To open, you can also add "–event_scheduler=1" directly to the start command, for example:

Mysqld ...--event_scheduler=1

My.ini or my.cnf in the [Mysqld]

Add Event_scheduler=on

Ii. Creation of events

/* Standard creation statement eventsevents

CREATE Event Event Task Name on SCHEDULE at time stamp/every any non-empty integer time unit [starts timestamp] [ends timestamp]
[on completion [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ' Time task note ']
Do
BEGIN
The SQL statement to execute
END

On schedule There are two ways to set up a scheduled task:
At timestamp: single-time scheduled task
Every any non-empty integer time unit [starts timestamp] [ends timestamp]: Recurring Scheduled Task

Set [starts time stamp] [ends timestamp] means that only the scheduled task is repeated within that time period
The time stamp can be any of the timestamp and datetime data types.
The requirement is for a future time (greater than current_timestamp) and less than the last time of the Unix time (equal to or less than ' 2037-12-31 23:59:59 ').
Time units are keywords: year,month,day,hour,minute or second.
Hint: Other time units are also legal such as: QUARTER, WEEK, Year_month,day_hour, Day_minute, Day_second, Hour_minute,hour_second, Minute_second, But they are non-standard and not practical and do not need.

[on completion [NOT] PRESERVE]:
The default is on completion not PRESERVE, which means that when the event does not recur, the event task is dropped when a single scheduled task is completed or when a recurring scheduled task is executed to the ends stage.
On completion PRESERVE not be dropped when the event task ends
Tip: You don't need to try this statement basically.

[ENABLE | DISABLE]
Do not write by default is enable
Set the state of the event, if enabled, then the system will start the event, disable does not start
You can use the Modify command to turn the event on or off and modify the event task to change the keyword create to alter.

*/

Example:

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 does insert into TEST.AAA VALUES (current_timestamp);

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) December 22, 2014 12 O'Clock full empty test table:

CREATE EVENT e_test on SCHEDULE @ TIMESTAMP ' 2014-12-22 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.AA

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 TAB LE 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) 5 days after the start of the daily emptying test table , stop execution after one months:

CREATE EVENT e_test on SCHEDULE every 1 day starts Current_timestamp + INTERVAL 5 day ENDS Current_ti        Mestamp + 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] Preserv E] [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)

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;

This article is from the "linux+php" blog, make sure to keep this source http://weichyou.blog.51cto.com/6207160/1592725

MySQL Event scheduled task