考虑使用MysqL的Create Event策略,该策略可以免去执行cron作业的麻烦。
DELIMITER $$
CREATE EVENT monthlyAddFlexDaysEvent
ON SCHEDULE EVERY '1' MONTH
STARTS '2015-09-01 00:00:00'
DO
BEGIN
update empAccrued set daysAccrued=daysAccrued+2.5;
END$$
DELIMITER ;
DELIMITER $$
CREATE EVENT annualThingEvent
ON SCHEDULE EVERY '1' YEAR
STARTS '2016-01-01 00:00:00'
DO
BEGIN
-- perform some annual thing
END$$
DELIMITER ;
几乎在任何情况下,您都可以做一些今年初就不会想到的事情。例如每周一次根据员工周年纪念日处理更新的事件。
在CREATE EVENT的“手册”页面上,
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND
适当地设置事件甚至发生并监视它们很重要。
show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
糟糕,事件调度程序未打开。
好吧,我可以整天等待,事件甚至都没有打开
SET GLOBAL event_scheduler = ON; -- turn her on
show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
按架构名称列出所有事件:
show events from so_gibberish;
或者
show events\G; -- <--------- I like this one from MysqL> prompt
show events; -- <--------- from workbench / sqlyog
*************************** 1. row ***************************
Db: so_gibberish
Name: set_trips_finished
Definer: GuySmiley@localhost
Time zone: SYstem
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2015-08-23 00:00:00
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci