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

Question

有没有一种方法可以使用预先定义的值自动更新Mysql DB中表中的列的值，并且该更新应该每月进行一次。

情况是，我想更新表雇员的列余额，每个雇员每月增加2.5天，并且每两年为每名雇员重设总天数。

Answer 1

考虑使用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