mysql的Event 及 时间 操作

  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)