每月在Mysql数据库中为列添加值 [英] Add value to a column each month in Mysql Data Base

问题描述

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

is there a way to update automatically the values of a column in a table in Mysql DB with a predifined value and the update should occur each month.

这种情况是，我想更新表员工的列余额，每个员工每月增加2.5天，并且每两年为每位员工重新设置总天数.

The situation is that i want to update the column balance in the table employees adding 2.5 days each month to each employee and the total amount of days should be reset each two years for each employee.

推荐答案

考虑使用mysql的Create Event策略，该策略可以避免执行cron作业.

Consider using mysql's Create Event strategy that can take away from needing to do cron jobs.

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 ;

---

几乎在任何情况下，您都可以在一年的第一天做一些事情.例如每周根据员工周年纪念日处理更新的事件.

---

In just about any event you could do something that doesn't fall on say the first of the year. Such as a weekly event that handles updates based on employee anniversary dates.

在创建事件的手册"页面中，

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

正确设置事件以使其发生并进行监视非常重要.

Properly setting up events to even happen and monitoring them is important.

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

糟糕，事件调度程序未打开.

ooops, the event scheduler is not turned on.

我可以整天等待，事件甚至都没有打开

Well I can wait all day long, events aren't even turned on

SET GLOBAL event_scheduler = ON;  -- turn her on

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

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

List all events by schema name:

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

这篇关于每月在Mysql数据库中为列添加值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！