是否有一种方法可以使用 ON 重复密钥来更新我想要插入的所有内容？

我知道你可以使用 ON DUPLICATE KEY UPDATE来更新一个特定的值，如果已经有这个键的记录,

我能做到:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE `a`=1, `b`=2, `c`=3

但是如何才能做到这一点，而不必写出两次列和值？

71020 次浏览
小开
最佳答案

Unfortunately not.

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.

小开

use REPLACE INTO

The meaning of REPLACE INTO is that IF the new record presents new key values, then it will be inserted as anew record.

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 replace the pre-existing record.

小开

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:

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:

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):

SET SESSION group_concat_max_len = 1000000;
小开

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:

=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.

Hope this helps someone!

小开

per @BhendiGawaar's comment on @Lightness Races in Orbit's answer, here is the MySQL 8.019+ version:

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 set as well:

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

The deprecation warning about the use of 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.


提交答案