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

Question

从MySQL 4.1.0开始，可以在目标表wrt 或某个字段中已经ON DUPLICATE KEY UPDATE插入的值（使用INSERT或SET或VALUES）时添加语句来指定行为。如果表中已经有或个字段的值，则将其替换为。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两个或多个条目满足条件时，更新仅执行一次。

此处带有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

多个键上的行为如下

UPDATE``ON DUPLICATE KEY UPDATE如果UNIQUE字段之一等于要插入的值，则执行in
。在这里，UPDATE在上执行Id = 1 OR Name = C。相当于

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

如果我只想对一个密钥进行一次更新怎么办

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

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

因为没有发现两个键都重复。