每月在Mysql数据库中为列添加值

每月在Mysql数据库中为列添加值

考虑使用mysql的Create Event策略，该策略可以免去执行cron作业的麻烦。

DELIMITER $$CREATE EVENT monthlyAddFlexDaysEventON SCHEDULE EVERY '1' MonTHSTARTS '2015-09-01 00:00:00'DO BEGIN update empAccrued set daysAccrued=daysAccrued+2.5;END$$DELIMITER ;

---

DELIMITER $$CREATE EVENT annualThingEventON SCHEDULE EVERY '1' YEARSTARTS '2016-01-01 00:00:00'DO BEGIN -- perform some annual thingEND$$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 onshow variables where variable_name='event_scheduler';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| event_scheduler | ON    |+-----------------+-------+

按架构名称列出所有事件：

show events from so_gibberish;

或者

show eventsG; -- <--------- I like this one from mysql> promptshow events; -- <--------- from workbench / sqlyog*************************** 1. row ***************************       Db: so_gibberish     Name: set_trips_finished  Definer: GuySmiley@localhostTime 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: 1character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci