1、距离当前 多长时间 执行

DELIMITER $$

ALTER DEFINER=`admin`@`%` EVENT `epnrupdatestatusagain` ON SCHEDULE EVERY 1 DAY STARTS '2015-07-24 14:45:00' ON COMPLETION PRESERVE ENABLE DO BEGIN

UPDATE diyfare.`pnr`

SET diyfare.`pnr`.`Status`=2

WHERE diyfare.`pnr`.`Status` != 2

AND NOW() BETWEEN DATE_SUB(diyfare.`pnr`.`Deadline`, INTERVAL 1 DAY) AND diyfare.`pnr`.`Deadline`;

END$$

DELIMITER ;

DATE_SUB  前

DATE_ADD 后

获取当前时间  SELECT NOW();

2、EVENT

--查看是否开启定时器

show variables like ’%sche%’;

--开启定时器

set global event_scheduler =1;

--改变分隔符

mysql> delimiter //

--创建存储过程

mysql> create procedure PRO_lqy_test_insert_23()

    -> begin

    -> insert into lqy_test(title,content,createtime) values('title titles','con

tent contents',now());

    -> end//

Query OK, 0 rows affected (0.00 sec)

--创建事件，每天15:15:00执行

mysql> create event EVENT_lqy_test_insert_23

    -> on schedule

    -> every 1 day starts '2012-04-24 15:15:00'

    -> do call PRO_lqy_test_insert_23()//

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

--启动事件

mysql> alter event EVENT_lqy_test_insert_23 on

    -> completion preserve enable;

Query OK, 0 rows affected (0.00 sec)

--关闭事件

mysql> alter event EVENT_lqy_test_insert_23 on

    -> completion preserve disable;

Query OK, 0 rows affected (0.00 sec)