MySQL MySQL关于重复密钥更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/867166/
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 on duplicate key update
提问by Joshua Martell
If I have query like this, how can I refer to values I have already given in update statement, so that I don't need to insert same data to query again? Example I would like to update col1 value with 'xxx', but now I need to enter 'xxx' again in duplicate statement. Is there anyway to refer those values in duplicate statement?
如果我有这样的查询，我如何引用我在更新语句中已经给出的值，这样我就不需要插入相同的数据来再次查询？示例 我想用 'xxx' 更新 col1 值，但现在我需要在重复语句中再次输入 'xxx'。无论如何要在重复语句中引用这些值吗？
INSERT INTO TABLENAME(col1, col2)
VALUES ('xxx', ‘yyy')
ON DUPLICATE KEY UPDATE col1 = ‘zzz'
回答by Joshua Martell
This should work and is a little more elegant:
这应该可以工作并且更优雅：
INSERT INTO TABLENAME(col1, col2)
VALUES ('xxx', 'yyy')
ON DUPLICATE KEY UPDATE col1 = VALUES(col1)
Note that you don't need to update the primary key part of the row. We know that's the same because there was a collision.
请注意，您不需要更新行的主键部分。我们知道那是一样的，因为发生了碰撞。
回答by Marc Armstrong
In PHP I do this to solve the issue:
在 PHP 中，我这样做是为了解决这个问题：
$fields = "a = 1, b = 2, c = 3";
$sql = "INSERT INTO some_table SET id = $id, $fields ON DUPLICATE KEY UPDATE $fields";
mysql_query($sql);
