mysql - 插入后触发"where in"

Question

我必须在表中插入一个新行，其中的文本列包含对另一个表 ID 的引用。例如:INSERT INTO table1 (引用, 日期) VALUES('23,24,25','2016-09-22');

我的触发器应该更新 table2.status，其中 table2.id 是 table1.reference 值之一。现在是这样的:

DELIMITER ;;
CREATE TRIGGER `rim_ai` AFTER INSERT ON `table1` FOR EACH ROW
UPDATE table2 SET status = 11 WHERE id IN (NEW.reference);;
DELIMITER ;

但是...我发现状态值仅在第一行发生变化(例如 ID 为 23 的行)。如果我的触发器出现问题，它根本不应该更新任何内容!我应该将引用字段转换为“文本”以外的其他内容吗？提前致谢。

Answer 1

不是你的触发器坏了，而是你的 table 设计坏了。您确实不应该在单个字段中存储由逗号(或任何其他分隔符)分隔的一系列值。您应该将每个单独的引用日期对存储在其自己的记录中。

而不是

reference | date
23,24,25  |2016-09-22

有

reference | date
23        |2016-09-22
24        |2016-09-22
25        |2016-09-22

在这种情况下，您的触发器将按预期工作。不过，我会稍微重写 where 子句:

...WHERE id = NEW.reference

从技术上讲，您可以使用多表更新语句和 find_in_set() 函数使触发器适用于当前数据结构，但我不建议这样做。

您遇到的行为的原因是 id IN (NEW.reference) 表达式未计算为 id IN (23,24,25) (in 运算符中的 3 个值的集合)，但为 id IN ('23,24,25') (包含 3 个数字的单个字符串)。 MySQL 默默地将 '23,24,25' 字符串转换为数字，这将得到数字 23(23 后面的逗号不能被解释为数字的一部分，因此 MySQL 停止转换并返回23 为数字)。因此，仅更新 id=23 的记录。