Mysql触发器更新如果日期匹配和插入如果不匹配所有之前插入

Question

我有一个表格summary。我希望在插入此表之前触发 TRIGGER 即 summary 我想要的是，TRIGGER 应该检查传入日期是否与现有日期匹配，它应该更新表并将传入值添加到现有值(value)，否则它应该插入

`summary`+------+-------+-------------+------+| date |income | expenditure | other||22/17 | 200   |   50        |  30  ||22/17 | 100   |   10        |  80  ||23/17 | 50    |   100       |  0   |+------+-------+-------------+------+`summary` // this is how I want it to be+------+-------+-------------+------+| date |income | expenditure | other||22/17 | 300   |   150       |  30  ||23/17 | 50    |   100       |  0   |+------+-------+-------------+------+

我的表格代码

CREATE TABLE `summary` (  `id_` int(11) NOT NULL,  `date_` date NOT NULL,  `income_` text NOT NULL,  `expenditure_` text NOT NULL,  `other_` text NOT NULL,  PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `summary` (`id_`, `date_`, `income_`, `expenditure_`, `other_`)VALUES(1, '2017-12-22', 200, 50, 30),(2, '2017-12-22', 100, 10, 80),(3, '2017-12-23', 50, 100, 0);

这是我迄今为止一直在尝试的，尽管我是一名新教徒
触发码

DELIMITER$$CREATE TRIGGER inc_trig BEFORE INSERT ON income_FOR EaCH ROWBEGINDECLARE income,expenditure,other INT;SET income=1; SET expenditure=2; SET other=3;IF( NEW.date_ != date_ )THENIF NEW.name_ = income THENINSERT INTO summary (date_,income)VALUES (new.date_,new.amount);ELSEIF NEW.name_ = expenditure THENINSERT INTO summary (date_,expenditure_)VALUES (new.date_,new.amount);ELSEIF NEW.name_ = other THENINSERT INTO summary (date_,other_)VALUES (new.date_,new.amount);ELSE(new.date_ = date_)THENIF NEW.name_ = income_ THENUPDATE summary SET income_ = income_ + new.amount);ELSEIF NEW.name_ = expenditure THENUPDATE summary SET expenditure_ = expenditure_ + new.amount);ELSEIF NEW.name_ = other THENUPDATE summary SET other_ = other_ + new.amount);END IF;END$$

Answer 1

有一种更简单的方法。首先，在 date 列上添加一个 unique 约束以防止重复条目:

ALTER TABLE summary ADD CONSTRAINT summary_date_uniq UNIQUE(`date`);

然后使用on duplicate key update插入新数据时的语法:

INSERT INTO summary
(`date_`, `income_`, `expenditure_`, `other_`)
VALUES (2, '2017-12-22', 100, 10, 80)
ON DUPLICATE KEY UPDATE
`income` = `income_` + VALUES(`income_`),
`expenditure` = `expenditure_` + VALUES(`expenditure_`),
`others` = `others_` + VALUES(`others_`);