MySQL Event Scheduler Usage Introduction

MySQL Event Scheduler Usage Introduction

Since MySQL5.1.0, a very distinctive feature has been added – Event Scheduler (Scheduler), which can be used to perform certain tasks on a timed basis and can be seen as a time-based trigger.

First, open

Event scheduling is turned off by default, enabling the executable

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 if the event Scheduler is turned on by following the commands below.

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

Second, create

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 execute at a certain time, and the second is to be executed at intervals from one time to the next.

INTERVAL: Time interval, can be accurate to the second ( http://www.lai18.com/content/433742.html)。

On completion [NOT] PRESERVE: Save after end, not save by default, once executed, the event is deleted, so it is strongly recommended that this parameter be set to on completion PRESERVE.

On SCHEDULE @ current_timestamp + INTERVAL 5 Day

is to be executed from now onwards after 5th

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

Execute at a specific time

On SCHEDULE every 1 day starts Current_timestamp + INTERVAL 5 dayends current_timestamp + INTERVAL 1 MONTH

5 days after the start of daily execution, one months after the end

Current_timestamp can be replaced at specific times, e.g. ' 2012-03-06 18:00:00 '

CREATE EVENT ' newevent ' on SCHEDULE every 1 MONTH starts ' 2012-04-01 00:00:00 ' ENDS ' 2100-01-01 00:00:00 ' on completion PRES Erveenabledoupdate tb_test set amount=100 where id=2;;

This is a complete example.

Third, modify

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

ALTER EVENT e_test DISABLE;

Closes the E_test event.

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

Iv. Deletion

DROP EVENT [IF EXISTS] Event_Name

MySQL Event Scheduler Usage Introduction