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)

mysql的Event 及 時間 操作