MySQL插入行对重复键更新多个列

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.

是否有更简洁的查询来实现这一点?因为我简化了查询，那里有10多个统计数据。

3 个解决方案

#1

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)

#2

Add a computed column (sum) and include that in your PK.

添加计算列(sum)，并将其包含在您的PK中。

However, this does denormalize your table. You could use a surrogate key and do the calculation in your SELECT

但是，这确实使您的表非规范化。您可以使用代理键并在SELECT中进行计算

#3

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:

如果在重复键更新时指定，并插入一行，这会导致惟一索引或主键中的重复值，那么MySQL将对旧行进行更新。例如，如果将列a声明为惟一并包含值1，则以下两个语句具有类似的效果:

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

在重复密钥更新c=c+1时插入表(a、b、c)值(1,2,3);

The equivalent update query is as below,

等效更新查询如下所示，

UPDATE table SET c=c+1 WHERE a=1;

更新表集c=c+1，其中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;

更新表集合c=c+1，其中a=1或b=2限制1;