MySQL事件调度器

MySQL 5.1 中新增了事件调度器这一个功能。可以实现类似于SQL Server的Job功能。

1.语法

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule 
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ‘comment‘]
DO sql_statement;
View Code

其中schedule的语句可以表示为:

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

interval的单位可以有如下几种:

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.开启、关闭事件调度器

在使用这个功能之前必须确保event_scheduler已开启.

/*开启*/
SET GLOBAL event_scheduler = 1;
--
SET GLOBAL event_scheduler = ON;

/*关闭*/
SET GLOBAL event_scheduler = 0;
--
SET GLOBAL event_scheduler = OFF; 
View Code

3.示例

创建测试数据库:

CREATE TABLE tst_event (createtime DATETIME);
View Code

创建测试用存储过程:

/*
创建测试用存储过程
*/
CREATE PROCEDURE msp_TestEvent()
BEGIN
    INSERT INTO tst_event VALUES (CURRENT_TIMESTAMP);
END
View Code

创建事件调度器:

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

修改事件调度器:

/*
修改EVENT
*/ 
ALTER EVENT me_TestEvent
    ON SCHEDULE EVERY 10 SECOND 
    STARTS 2014-02-27 16:45:00 ENDS DATE_ADD(2014-02-27 16:46:00,INTERVAL 1 SECOND)
    DO INSERT INTO tst_event VALUES (CURRENT_TIMESTAMP);
View Code

查看事件调度器:

/*
查看EVENT
*/   
show events;
--
select * from information_schema.events 
View Code

4.不同时间间隔的调度: 

一天后执行调度器:

/*
一天后执行调度器T
*/    
CREATE EVENT e_TestEvent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO CALL msp_TestEvent();
View Code

2014-02-28日执行调度器:

/*
2014-02-28日执行调度器
*/    
CREATE EVENT e_TestEvent
ON SCHEDULE AT TIMESTAMP 2014-02-28 00:00:00
DO CALL msp_TestEvent();
View Code

每天执行调度器:

/*
每天执行调度器
*/    
CREATE EVENT e_TestEvent
ON SCHEDULE EVERY 1 DAY
DO CALL msp_TestEvent();
View Code

一天后每天执行调度器:

/*
一天后每天执行调度器
*/    
CREATE EVENT e_TestEvent
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
DO CALL msp_TestEvent(); 
View Code

每天执行调度器,10天后停止:

/*
每天执行调度器,10天后停止
*/    
CREATE EVENT e_TestEvent
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 10 DAY
DO CALL msp_TestEvent(); 
View Code

一天后开始,每天执行调度器,10天后停止:

/*
一天后开始,每天执行调度器,10天后停止
*/    
CREATE EVENT e_TestEvent
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 10 MONTH
DO CALL msp_TestEvent();
View Code

每天执行调度器,只执行一次:

/*
每天执行调度器,只执行一次
*/    
CREATE EVENT e_TestEvent
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO CALL msp_TestEvent(); 
View Code

5.删除调度器

DROP EVENT [IF EXISTS] event_name
View Code

MySQL事件调度器,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。