MySQL在重复键上插入行更新多列
问题描述：
我有一个表(name, date, stat1, stat2, stat3)，(name, date)是PK.当我插入行时，将有重复的键，并且我需要总结三个统计信息.我对Java中的PreparedStatement使用以下查询:
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:
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.
答
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)