MySQL event scheduler _ MySQL

MySQL event scheduler bitsCN.com

The event scheduler is added to MySQL 5.1. Similar to the Job function of SQL Server.

1.Syntax:

CREATE EVENT [IF NOT EXISTS] event_nameON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE][ENABLE | DISABLE][COMMENT 'comment']DO sql_statement;

View Code

The schedule statement can be expressed:

AT TIMESTAMP [+ INTERVAL]| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

View Code

The interval unit can be as follows:

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

View Code

2. enable and disable the event scheduler:

Before using this function, make sure that event_scheduler is enabled.

/* Enable */set global event_scheduler = 1; -- or set global event_scheduler = ON;/* disable */set global event_scheduler = 0; -- or set global event_scheduler = OFF;

View Code

3. example:

Create a test database:

CREATE TABLE tst_event (createtime DATETIME);

View Code

Create a test stored procedure:

/* CREATE a test Stored PROCEDURE */create procedure msp_TestEvent () begin insert into tst_event VALUES (CURRENT_TIMESTAMP); END

View Code

Create an event scheduler:

CREATE EVENT IF NOT EXISTS me_TestEvent    ON SCHEDULE EVERY 10 SECOND     STARTS '2014-02-28 16:45:00' ENDS DATE_ADD('2014-02-28 16:46:00',INTERVAL 1 SECOND)    DO CALL msp_TestEvent();

View Code

Modify the event scheduler:

/* Modify EVENT */alter event me_TestEvent on schedule every 10 second starts '2017-02-27 16:45:00 'ENDS DATE_ADD ('2017-02-27 16:46:00', INTERVAL 1 SECOND) do insert into tst_event VALUES (CURRENT_TIMESTAMP );

View Code

View the event scheduler:

/* View EVENT */show events; -- or select * from information_schema.events

View Code

4. scheduling at different intervals:

Run the scheduler one day later:

/* Run the scheduler T */create event e_testeven ton schedule at CURRENT_TIMESTAMP + INTERVAL 1 daydo call msp_TestEvent () one day later ();

View Code

Run the scheduler on February 28:

/* Run the scheduler on February 28, 2014 */create event e_testeven ton schedule at timestamp '2017-02-28 00:00:00 'do call msp_TestEvent ();

View Code

Daily scheduler execution:

/* Run the daily scheduler */create event e_testeven ton schedule every 1 daydo call msp_TestEvent ();

View Code

Run the scheduler every day one day:

/* Run the scheduler EVERY day after one day */create event e_testeven ton schedule every 1 DAYSTARTS CURRENT_TIMESTAMP + INTERVAL 2 daydo call msp_TestEvent ();

View Code

Run the scheduler every day and stop it after 10 days:

/* Run the scheduler EVERY day and stop the scheduler after 10 days */create event e_testeven ton schedule every 1 DAYENDS CURRENT_TIMESTAMP + INTERVAL 10 daydo call msp_TestEvent ();

View Code

Start one day, run the scheduler every day, and stop after 10 days:

/* Start one day and run the scheduler EVERY day. after 10 days, stop */create event e_testeven ton schedule every 1 DAYSTARTS CURRENT_TIMESTAMP + INTERVAL 1 DAYENDS CURRENT_TIMESTAMP + INTERVAL 10 monthdo call msp_TestEvent ();

View Code

Run the scheduler once a day:

/* Run the scheduler only once a day */create event e_testeven ton schedule every 1 dayon completion not preservedo call msp_TestEvent ();

View Code

5. delete the scheduler:

DROP EVENT [IF EXISTS] event_name

View CodebitsCN.com