MySQL 在重复键上插入行更新多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11235501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL insert row on duplicate key update multiple columns
提问by DrXCheng
I have a table (name, date, stat1, stat2, stat3), (name, date)is the PK. When I insert rows, there will be duplicate keys, and I need to sum up the three stats. I use the following query with PreparedStatement in Java:
我有一个表(name, date, stat1, stat2, stat3)，(name, date)就是PK。当我插入行时，会有重复的键，我需要总结三个统计信息。我在 Java 中对 PreparedStatement 使用以下查询：
INSERT INTO tb (name, date, stat1, stat2, stat3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE stat1 = stat1 + ?, stat2 = stat2 + ?, stat3 = stat3 + ?
Is there a more concise query to achieve that? Because I have simplify the query, there are over ten stats there.
是否有更简洁的查询来实现这一目标？因为我简化了查询，那里有十多个统计信息。
回答by vearutop
INSERT INTO tb (name, date, stat1, stat2, stat3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE stat1 = stat1 + VALUES(stat1), stat2 = stat2 + VALUES(stat2), stat3 = stat3 + VALUES(stat3)
回答by Kermit
Add a computed column (sum) and include that in your PK.
添加计算列（总和）并将其包含在您的 PK 中。
However, this does denormalize your table. You could use a surrogate key and do the calculation in your SELECT
但是，这确实使您的表非规范化。您可以使用代理键并在 SELECT 中进行计算
回答by shivaP
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:
如果您指定 ON DUPLICATE KEY UPDATE，并且插入的行会导致 UNIQUE 索引或 PRIMARY KEY 中的重复值，则 MySQL 执行旧行的 UPDATE。例如，如果列 a 声明为 UNIQUE 并包含值 1，则以下两个语句具有类似的效果：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
The equivalent update query is as below,
等效的更新查询如下，
UPDATE table SET c=c+1 WHERE a=1;
更新表 SET c=c+1 WHERE a=1;
If a and b column is unique, equivalent update query would be ,
如果 a 和 b 列是唯一的，则等效的更新查询将是，
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
更新表 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
