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

2022-01-17 00:00:00 sql-update upsert mysql sql-insert

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

From MySQL 4.1.0 onwards, it is possible to add ON DUPLICATE KEY UPDATE statement to specify behavior when values inserted (with INSERT or SET or VALUES) are already in destination table w.r.t. PRIMARY KEY or some UNIQUE field. If value for PRIMARY KEY or some UNIQUE field are already in table, INSERT is replaced by an UPDATE.

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

  • How does ON DUPLICATE KEY UPDATE behave in case there are multiple UNIQUE fields in my table ?

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

Can I have one update only, if either UNIQUE field is matched ?

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

Can I have an update only if both UNIQUE fields are matched simultaneously ?

推荐答案

考虑

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 时，仅更新一次.

If a and b are UNIQUE fields, UPDATE occurs on a = 1 OR b = 2. Also when condition a = 1 OR b = 2 is met by two or more entries, update is done only once.

Ex here table table with Id 和 Name UNIQUE 字段

Ex here table table with Id and Name UNIQUE fields

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 的唯一性.现在有了

which violates uniqueness of Id and Name. Now with

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.这里，UPDATE 是在 Id = 1 OR Name = C 上执行的.相当于

UPDATE in ON DUPLICATE KEY UPDATE is performed if one of the UNIQUE field equals the value to be inserted. Here, UPDATE is performed on Id = 1 OR Name = C. It is equivalent to

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

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

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

Can use UPDATE statement with LIMIT keyword

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(或唯一性)在这两个字段上都起作用.

One solution is to ALTER TABLE and make the PRIMARY KEY (or uniqueness) work on both fields.

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

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

since no duplicate (on both keys) is found.

相关文章