MySQL Scheduled Task (Event Scheduler) (Scheduler) [Go]

Original link: http://www.cnblogs.com/c840136/articles/2388512.html

　　

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

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

Add Event_scheduler=on to [mysqld] in My.ini or my.cnf

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}

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 before executing the query successfully.

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.

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

The Delete events (drop event) 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;

MySQL Scheduled Task (Event Scheduler) (Scheduler) [Go]