mysql - 使用计算值或触发器用同一表中不同行的数据更新行？

Question

我希望为 mainKey = 1 插入 2019-08-15，并且如果总数大于 200 2019-08-14 我想保存为 2019-08-15 创建的新行中最后一列的差异。 total 是一个可以保持不变或增加的值。

+------------+---------+-------+--------------------------+
| date       | mainKey | total | differenceSinceYesterday |
+------------+---------+-------+--------------------------+
| 2019-08-14 |       1 |   200 |                        0 |
| 2019-08-14 |       2 |   500 |                        0 |
+------------+---------+-------+--------------------------+

date 和 mainKey 一起唯一标识该行，因此特定 mainKey 的行每天只能插入一次。

+--------------------------+---------+------+-----+---------+-------+
| Field                    | Type    | Null | Key | Default | Extra |
+--------------------------+---------+------+-----+---------+-------+
| date                     | date    | NO   | PRI | NULL    |       |
| mainKey                  | int(11) | NO   | PRI | NULL    |       |
| total                    | int(11) | NO   |     | NULL    |       |
| differenceSinceYesterday | int(11) | NO   |     | NULL    |       |
+--------------------------+---------+------+-----+---------+-------+

我研究了触发器，但如果在更新后运行，它会变得递归，因为它是同一个表。或者我应该将逻辑转移到首先进行插入的外部脚本，然后运行另一个 SELECT 来查找具有特定 mainKey 的 1 天前的行并更新今天的行？

Answer 1

您可以使用 BEFORE INSERT 触发器来实现您想要的目的，检查前一天的 total 值 >= 200，如果存在，则设置differenceSinceYesterday 今天和昨天总计之间的差异值:

DELIMITER //
CREATE TRIGGER updateDifference
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  DECLARE yesterday INT;
  SELECT total INTO yesterday FROM table1 WHERE date = NEW.date - INTERVAL 1 DAY AND mainKey = NEW.mainKey;
  IF yesterday >= 200 THEN
     SET NEW.differenceSinceYesterday = NEW.total - yesterday;
  END IF;
END;
DELIMITER ;
INSERT INTO table1 (date, mainKey, total)
VALUES ('2019-08-15', 1, 400), ('2019-08-16', 1, 300);
SELECT * FROM table1

输出:

date                mainKey total   differenceSinceYesterday
2019-08-14 00:00:00 1       200     0
2019-08-14 00:00:00 2       500     0
2019-08-15 00:00:00 1       400     200
2019-08-16 00:00:00 1       300     -100

Demo on dbfiddle