MySQL 有没有办法使用 ON DUPLICATE KEY 来更新我想插入的所有内容？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9537710/
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
Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?
提问by Naftali aka Neal
I know that you can use ON DUPLICATE KEY UPDATEto update a certain value if there is a record for that key already,
我知道ON DUPLICATE KEY UPDATE如果已经有该键的记录，您可以使用它来更新某个值，
I can do this:
我可以做这个：
INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE `a`=1, `b`=2, `c`=3
But how can I do this without having to write out the columns and values twice?
但是我怎样才能做到这一点而不必两次写出列和值呢？
回答by Lightness Races in Orbit
You can get half-way there by not having to repeat the value:
不必重复该值，您就可以做到一半：
INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);
But you still have to list the columns.
但是您仍然必须列出列。
回答by Naftali aka Neal
use REPLACE INTO
The meaning of REPLACE INTOis that IF the new record presents new key values, then it will be inserted as anew record.
意思REPLACE INTO是如果新记录呈现新的键值，那么它将作为新记录插入。
IF the new record has key values that match a pre-existing record,then the key violation will be ignored and the new record will replacethe pre-existing record.
如果新记录具有与预先存在的记录匹配的键值，则键冲突将被忽略并且新记录将替换预先存在的记录。
回答by Javier P
If it is useful, I made a query to avoid writing by hand the last part of the "on duplicate" query, for versions >= 5.0:
如果它有用，我做了一个查询，以避免手工编写“重复”查询的最后一部分，版本> = 5.0：
SELECT GROUP_CONCAT( CONCAT(COLUMN_NAME,"=values(", COLUMN_NAME,")") SEPARATOR ", ") FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
and its output is this:
它的输出是这样的：
a=values(a), b=values(b), c=values(c), d=values(d)
on a table that has columns a,b,c and d, so you can append to the first part of the query:
在包含 a、b、c 和 d 列的表上，您可以附加到查询的第一部分：
INSERT INTO `tableName` (`a`,`b`,`c`, `d`) VALUES (1,2,3,4) ON DUPLICATE KEY UPDATE a=values(a), b=values(b), c=values(c), d=values(d)
UPDATE:For a very long list of columns you may see a truncated output, you may use this statement before the query above (thanks Uncle iroh):
更新：对于很长的列列表，您可能会看到截断的输出，您可以在上述查询之前使用此语句（感谢Uncle iroh）：
SET SESSION group_concat_max_len = 1000000;
回答by mowwwalker
per @BhendiGawaar's comment on @Lightness Races in Orbit's answer, here is the MySQL 8.019+ version:
根据@BhendiGawaar 在 Orbit 回答中对@Lightness Races 的评论，这是 MySQL 8.019+ 版本：
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
OR
或者
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
Works with setas well:
也适用于set：
INSERT INTO t1 SET a=1,b=2,c=3 AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
Copied directly from MySQL Docs
直接从MySQL Docs复制
The deprecation warning about the use of VALUES:
关于使用的弃用警告VALUES：
The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section.
从 MySQL 8.0.20 开始，不推荐使用 VALUES() 来引用新的行和列，并且在未来的 MySQL 版本中可能会被删除。相反，使用行和列别名，如本节接下来的几段所述。
回答by hyphen
i know this is an old question, and this is a somewhat unconventional answer, but i ran into the same thing and was having issues with setting the group_concat_max_len property mentioned in another answer and was always getting a truncated result. Another option that I ultimately went with when writing a long script was to use this formula in excel:
我知道这是一个老问题，这是一个有点非常规的答案，但我遇到了同样的事情，并且在设置另一个答案中提到的 group_concat_max_len 属性时遇到了问题，并且总是得到截断的结果。在编写长脚本时，我最终采用的另一个选择是在 excel 中使用此公式：
=SUBSTITUTE(TRIM(A1), ",", "") & " = VALUES(" & SUBSTITUTE(TRIM(A1), ",", "") & "),"
where A1 is the cell you copy the field name into. To do this quickly, I'd right click the table and copy the select statement to the clipboard, which gives you all the colum names, the formula removes the commas.
其中 A1 是您将字段名称复制到的单元格。为了快速做到这一点，我会右键单击表格并将选择语句复制到剪贴板，它为您提供所有列名称，公式删除逗号。
Hope this helps someone!
希望这可以帮助某人！
