Introduction to MySQL event scheduler, mysql event Scheduler

Source: Internet
Author: User

Introduction to MySQL event scheduler, mysql event Scheduler

How to Use the MySQL event Scheduler


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_scheduler';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 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 ( Http://www.lai18.com/content/433742.html).

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 '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


Execute the task every day after 5 days and 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 '2012-04-01 00:00:00' ENDS '2100-01-01 00:00:00'ON COMPLETION PRESERVEENABLEDOupdate 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.