Mysql Scheduled Task (Event Scheduler) Event Scheduler Introduction _mysql

To see if the event Scheduler is currently open, you can perform the following sql:

Show VARIABLES like ' Event_scheduler ';
Or

SELECT @ @event_scheduler;
Or

Show Processlist;
If the display:

+-----------------+-------+
| 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 directly add "–event_scheduler=1" to the start command, for example:

Mysqld ...--event_scheduler=1

In the My.ini or my.cnf
[Mysqld]
Add Event_scheduler=on

Creating events (Create Event)
Let's take a 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, let's look at a simple example to illustrate inserting a record into a datasheet every second.

Use test;
CREATE TABLE aaa (Timeline TIMESTAMP);
CREATE EVENT E_test_insert
On SCHEDULE EVERY 1 SECOND
Do inserts into TEST.AAA VALUES (current_timestamp);
Wait 3 seconds before executing the query successfully.

2) empty test table after 5 days:

CREATE EVENT E_test
On SCHEDULE at 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 at regular intervals every day:

CREATE EVENT E_test
On SCHEDULE EVERY 1 day
Do TRUNCATE TABLE test.aaa;

5) 5 days after the opening of the daily timed empty test table:

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) 5 days after the opening of the daily time to clear the test table, one months after the stop execution:

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 persistently, and the default is not PRESERVE.

8 Clear the test table at regular intervals (only once, and terminate the event after 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 if the state is turned on or off after the event is created, the default is enable.
[COMMENT ' COMMENT '] can annotate 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 events

ALTER EVENT e_test DISABLE;

2) Open Event

ALTER EVENT e_test ENABLE;
3 The daily empty test table to 5 days to empty once:

ALTER EVENT E_test
On SCHEDULE EVERY 5 day;

Delete events (drop event)
The syntax is simple, as follows:

DROP EVENT [IF EXISTS] Event_Name
For example, delete the E_test event created earlier

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;