MySQL Event Scheduler

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

[SQL]View Plaincopy

1. SET GLOBAL event_scheduler=1;
2. 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.

[SQL]View Plaincopy

1. SHOW VARIABLES like ' Event_scheduler ';
2. SELECT @ @event_scheduler;

Second, create

[SQL]View Plaincopy

2. CREATE EVENT [IF not EXISTS] Event_Name
4. On SCHEDULE SCHEDULE
6. [oncompletion [not] PRESERVE]
8. [ENABLE | DISABLE]
10. [COMMENT ' COMMENT ']
12. Do sql_statement;
16. Schedule
18. at TIMESTAMP [+ INTERVAL INTERVAL]
20. | Every INTERVAL [starts TIMESTAMP] [ENDS TIMESTAMP]
24. INTERVAL:
26. Quantity {Year| QUARTER | MONTH | Day | HOUR |  MINUTE |
28. 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 seconds.

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.

[SQL]View Plaincopy

1. On SCHEDULE @ current_timestamp + INTERVAL 5 Day

is to be executed from now onwards after 5th

[SQL]View Plaincopy

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

Execute at a specific time

[SQL]View Plaincopy

1. On SCHEDULE every 1 day
2. Starts Current_timestamp + INTERVAL 5 Day
3. ENDS 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 '

[SQL]View Plaincopy

1. CREATE EVENT ' newevent '
2. On SCHEDULE every 1 MONTH starts ' 2012-04-01 00:00:00 ' ENDS ' 2100-01-01 00:00:00 '
3. On completion PRESERVE
4. ENABLE
5. Do
6. Update Tb_test set amount=100 where id=2;;

This is a complete example.

Third, modify

[SQL]View Plaincopy

1. ALTER EVENT Event_Name
2. [onSCHEDULE SCHEDULE]
3. [RENAME to New_event_name]
4. [oncompletion [not] PRESERVE]
5. [COMMENT ' COMMENT ']
6. [ENABLE | DISABLE] [do sql_statement]

[SQL]View Plaincopy

1. ALTER EVENT e_test DISABLE;

Closes the E_test event.

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

Iv. deletion

[SQL]View Plaincopy

1. DROP EVENT [IF EXISTS] Event_Name

MySQL Event Scheduler