多个UNIQUE字段的ON DUPLICATE KEY UPDATE的MySQL行为 [英] MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields

问题描述

从MySQL 4.1.0起，可以添加ON DUPLICATE KEY UPDATE语句来指定插入的值(使用INSERT或SET或VALUES)已经存在于目标表w.r.t中时的行为. 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.

具有ID和名称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和名称的唯一性.现在有了

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.在此，对Id = 1 OR Name = C执行UPDATE.等效于

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

如果我只想对其中一个键进行一次更新

可以将UPDATE语句与LIMIT关键字一起使用

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.

这篇关于多个UNIQUE字段的ON DUPLICATE KEY UPDATE的MySQL行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！