mysql-定时任务

mysql 定时任务从5.1版本开始引入,又叫做临时触发器,但是与普通触发器不同的是,普通触发器是通过操作数据表的每行数据而触发的事件,而它是通过时间点或者时间周期触发

先来看一下它的语法:

DELIMITER $$

-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE    /*[DEFINER = { user | CURRENT_USER }]*/    EVENT   `event1`

ON SCHEDULE
     /* uncomment the example below you want to use */

    -- scheduleexample 1: run once

       --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }

    -- scheduleexample 2: run at intervals forever after creation

       -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

    -- scheduleexample 3: specified start time, end time and interval for execution
       /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]

       STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }

       ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */

/*[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']*/

DO
    BEGIN
        (sql_statements)
    END$$

DELIMITER ;

基本上通过上面的语法说明已经了解到如何使用了,下面针对每种方式可以再写个小栗子

1,在某个时间点触发(执行一次):

DELIMITER $$

CREATE     EVENT `bdhp`.`e_e1`

ON SCHEDULE
        /*只执行一次*/
        /*AT '2015-02-03 16:07.00'*//*某一时间点*/
    
       /* AT '2015-02-03 16:47.20' + INTERVAL 40 second*//*某一时间点 + 一段时间*/
        
         AT CURRENT_TIMESTAMP+ INTERVAL 40 SECOND/*当前时间点 + 一段时间*/
        
DO
    BEGIN
        INSERT INTO t_sys_log(sn)VALUES('55555');
    END$$

DELIMITER ;

2,按周期执行,无限次数

DELIMITER $$

CREATE     EVENT `e_e2`

ON SCHEDULE
    
       EVERY 1 MINUTE
DO
    BEGIN
        INSERT INTO t_sys_log(sn)VALUES('55555');
    END$$

DELIMITER ;

3,在某一时间段内,按周期执行(ends 不写则表示从某一时间点开始执行)

DELIMITER $$

CREATE     EVENT `e_e3`

ON SCHEDULE
    EVERY 1  SECOND

       STARTS CURRENT_TIMESTAMP

       ENDS '2015-02-03 16:15.00'

DO
    BEGIN
        INSERT INTO t_sys_log(sn,USER_NAME)VALUES('test','ttt-55');
    END$$

DELIMITER ;

 

posted @ 2015-02-03 16:59  china2k  阅读(221)  评论(0编辑  收藏  举报