How to Use the MySQL event Scheduler

Since MySQL5.1.0, a special feature (ndash) has been added. The event scheduler (EventScheduler) can be used to regularly execute certain tasks.

Since MySQL5.1.0, a special feature ndash has been added. The Event sched can be used to regularly execute certain tasks.

The Event sched feature has been added since MySQL5.1.0. It can be used for scheduled execution of certain tasks and can be seen as a time-based trigger.

1. Enable

By default, event scheduling is disabled and executable is enabled.

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 the command is started"

Run the following command to check whether the event scheduler is Enabled.

Show variables like 'event _ schedount ';

SELECT @ event_scheduler;

Ii. 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]

| Every interval [starts timestamp] [ends timestamp]

INTERVAL:

Quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH

Event_name: name of the event you want to create

Schedule: it is an execution plan. There are two options: first, execution at a certain time, and second, execution at intervals from a certain time to a certain time.

INTERVAL: INTERVAL, which can be accurate to seconds ().

On completion [NOT] PRESERVE: whether to save after execution. It is NOT saved by default. Once execution is complete, the event is deleted. Therefore, we strongly recommend that you set this parameter to on completion preserve.

On schedule at CURRENT_TIMESTAMP + INTERVAL 5 DAY

It will be executed five days from now on

On schedule at timestamp '2017-03-07 12:00:00'

Execute at a specific time

On schedule every 1 DAY

STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY

ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH

Execute the task every day after 5 days. The task will end after one month.

CURRENT_TIMESTAMP can be replaced by a specific time, for example, '2017-03-06 18:00:00'

Create event 'newevent'

On schedule every 1 month starts '2017-04-01 00:00:00 'ENDS '2017-01-01 00:00:00'

ON COMPLETION PRESERVE

ENABLE

DO

Updated tb_test set amount = 100 where id = 2 ;;

This is a complete example.

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

Disable the e_test event.

Note that once MySQL is restarted, all Disable events will disappear.

Iv. Delete

Drop event [if exists] event_name

This article permanently updates the link address: