Mysql Event Scheduler Usage Introduction _mysql

Since MySQL5.1.0, a feature has been added – Event scheduler Scheduler, which can be used to perform certain tasks on a scheduled basis and as a time based trigger.

First, open
Event scheduling is turned off by default and opens executable

Copy Code code as follows:

SET GLOBAL event_scheduler=1;
SET GLOBAL Event_scheduler=on;

Or add event_scheduler=1 to the My.ini file.
or add "-event_scheduler=1" after starting the command
You can see whether the event scheduler is turned on by using the following command.

Copy Code code as follows:

Show VARIABLES like ' Event_scheduler ';
SELECT @ @event_scheduler;

Second, create

Copy Code code as follows:

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

Event_Name: Is the event name you want to create
Schedule: Is the execution plan, there are two options, the first is to perform at a certain moment, the second is from a certain time to a certain period at intervals to execute.
INTERVAL: Time interval, can be accurate to seconds.
On completion [NOT] PRESERVE: Save after the end, default not saved, once executed, the event is deleted, so it is strongly recommended that this parameter be set to on completion PRESERVE.

Copy Code code as follows:

On SCHEDULE at Current_timestamp + INTERVAL 5 Day

is from now on 5th after the execution

Copy Code code as follows:

On SCHEDULE at TIMESTAMP ' 2012-03-07 12:00:00 '

Execute at a specific time

Copy Code code as follows:

On SCHEDULE EVERY 1 day
Starts Current_timestamp + INTERVAL 5 Day
ENDS Current_timestamp + INTERVAL 1 MONTH

Start daily after 5 days, end one months later
Current_timestamp can be replaced with specific time, such as ' 2012-03-06 18:00:00 '

Copy Code code as follows:

CREATE EVENT ' newevent '
On SCHEDULE EVERY 1 MONTH starts ' 2012-04-01 00:00:00 ' ENDS ' 2100-01-01 00:00:00 '
On completion PRESERVE
ENABLE
Todo
Update tb_test set amount=100 where id=2;;

This is a complete example.

Third, modify

Copy Code code as follows:

ALTER EVENT Event_Name
[on SCHEDULE SCHEDULE]
[RENAME to New_event_name]
[on completion [NOT] PRESERVE]
[COMMENT ' COMMENT ']
[ENABLE | DISABLE] [do sql_statement]

Copy Code code as follows:

ALTER EVENT e_test DISABLE;

Closes the E_test event.
Note that once MySQL restarts, the Disable events will all disappear.

IV. Delete

Copy Code code as follows:

DROP EVENT [IF EXISTS] Event_Name