多个 UNIQUE 字段的 ON DUPLICATE KEY UPDATE 的 MySQL 行为

Question

从 MySQL 4.1.0 开始，可以添加 ON DUPLICATE KEY UPDATE 语句来指定插入值时的行为(使用 INSERT 或 SET 或 VALUES) 已经在目标表 w.r.t 中。 PRIMARY KEY 或一些 UNIQUE 字段。如果 PRIMARY KEY 或某些 UNIQUE 字段的值已在表中，则 INSERT 将替换为 UPDATE。

• ON DUPLICATE KEY UPDATE 在有多个的情况下如何表现UNIQUE 我的表中的字段？

• 如果任一 UNIQUE 字段匹配，我可以只更新一次吗？

• 只有当两个 UNIQUE 字段同时匹配时，我才能更新吗？

Answer 1

考虑

INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;

如果 a 和 b 是 UNIQUE 字段，则 UPDATE 发生在 a = 1 OR b = 2 上。此外，当两个或多个条目满足条件 a = 1 OR b = 2 时，仅更新一次。

Ex here table table with Id 和 Name UNIQUE 字段

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6

如果查询是

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7);

然后我们得到

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6
1      C        7

这违反了 Id 和 Name 的唯一性。现在有了

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7)
ON DUPLICATE KEY UPDATE Value = 7;

我们得到

Id     Name     Value 
1      P        7 
2      C        7 
3      D        29 
4      A        6

多个键的行为如下

如果 UNIQUE 字段之一等于要插入的值，则执行 ON DUPLICATE KEY UPDATE 中的

UPDATE。这里，UPDATE 是在 Id = 1 OR Name = C 上执行的。相当于

UPDATE table 
SET Value = 7
WHERE Id = 1 OR Name = C;

如果我只想要一个更新，对于任一键怎么办

可以使用带有LIMIT关键字的UPDATE语句

UPDATE table 
SET Value = 7
WHERE Id = 1 OR Name = C
LIMIT 1;

这将给

Id     Name     Value 
1      P        7 
2      C        3 
3      D        29 
4      A        6

如果我只想在两个键的值都匹配的情况下进行一次更新怎么办

一种解决方案是 ALTER TABLE 并使 PRIMARY KEY(或唯一性)在这两个字段上都起作用。

ALTER TABLE table 
DROP PRIMARY KEY,
ADD PRIMARY KEY (Id, Name);

现在开始

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7)
ON DUPLICATE KEY UPDATE Value = 7;

我们得到

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6
1      C        7

因为没有找到重复项(在两个键上)。