MySQL批量插入或更新

Question

有没有一种批量执行类似于在MySQL服务器上的 INSERT OR UPDATE 查询的方法？

INSERT IGNORE ...

这样不起作用，因为如果该字段已经存在，它会简单地忽略它并且不插入任何内容。

REPLACE ...

无法工作，因为如果字段已经存在，它将首先DELETE该字段，然后再INSERT它，而不是更新它。

INSERT ... ON DUPLICATE KEY UPDATE

这个方法可以运行，但不能批量使用。

所以我想知道是否有类似INSERT ... ON DUPLICATE KEY UPDATE的命令可以一次性处理多行。

- rid

---

为什么你说INSERT ... ON DUPLICATE KEY UPDATE不能批量发出？ - danorton

这个问题可以更清晰明了。也许OP想要的是通过SQL进行批量更新。这就是被接受的答案。 - Akrikos

Answer 1

你可以使用INSERT ... ON DUPLICATE KEY UPDATE 插入/更新多行数据。 文档 中给出了以下示例：

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

还是我误解了你的问题？

- user359996

---

如果你正在阅读这篇文章，这里有一个更清晰的例子：INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c); 这样你就可以使用VALUES (1,2,NOW())来更新像updated_at这样的列，例如将其放在c的位置。 - Zack Morris

嘿，大家好，如果表中有一个auto_increment的id，“INSERT ... ON DUPLICATE KEY UPDATE”会使id成为不连续递增的序列号，你们是怎么解决的？ - Zander Wong

@ZanderWong 我认为这不是MySQL 5.6的问题。请参考此示例。 - yyFred

@ZanderWong 即使在 MySQL 8.x 中，这仍然是一个问题 —— "ON DUPLICATE KEY" 语句会增加 InnoDB 表中的自增值，即使没有插入新行。请参考MySQL 手册。不幸的是，这个问题没有真正的解决方案。你需要意识到这一点，并确保你的自增列足够大。如果你使用 "ON DUPLICATE KEY" 语句进行批量更新，你会非常快地消耗掉很多值。 - Jeff Kilbride

@ZanderWong 将 innodb_autoinc_lock_mode 设置为 2，而不是默认值 1。MySQL 文档：https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html - Tiago B

Answer 2

有一种可能的方法是创建一个临时表，将数据插入该表中，然后使用连接执行1个查询来插入不存在的记录，然后更新已存在的字段。基本操作如下：

CREATE TABLE MyTable_Temp LIKE MyTable

LOAD DATA INFILE..... INTO MyTable_Temp

UPDATE MyTable INNER JOIN 
MyTable_Temp
ON MyTable.ID=MyTable_Temp.ID
SET MyTable.Col1=MyTable_Temp.Col1, MyTable.Col2=MyTable_Temp.Col2.....

INSERT INTO MyTable(ID,Col1,Col2,...)
SELECT ID,Col1,Col2,... 
FROM MyTable_Temp
LEFT JOIN MyTable 
ON MyTable_Temp.ID = MyTable.ID
WHERE myTable.ID IS NULL

DROP TABLE MyTable_Temp

语法可能不是完全准确的，但这应该能给你提供基本知识。另外，我知道它不太好看，但它完成了工作。

更新

我交换了插入和更新的顺序，因为先插入会导致在调用更新时更新所有插入的行。如果您首先进行更新，则仅更新现有记录。这应该意味着服务器需要处理的工作更少，尽管结果应该相同。

- Kibbee

---

如果两个线程同时尝试通过递增键的值来更新表，会发生什么情况？可能会出现一个键的值没有被更新的情况，因为在UPDATE步骤中它并不存在，并且在执行INSERT步骤之前被另一个线程插入。 - Ben

@Ben 你说得对。这确实取决于使用情况，包括表如何更新，有多少个更新线程正在运行，以及这些更新线程是否会更新表中的相同数据。如果有多个线程在运行，都在更新相同的数据，那么在开始更新之前锁定目标表可能是值得的，然后在插入完成后解锁。很多事情都取决于你要实现什么，上述方法在许多情况下都适用，其中你从未更新过表格，除了从批量CSV文件中进行更新。 - Kibbee

一个小的改进：如果您在事务内执行此操作，您可以将其更改为CREATE TEMPORARY TABLE MyTable_Temp like MyTable，这将在提交事务后自动销毁（并且仅在此事务内可见）。否则，如果您正在对同一表进行多个批量更新（即使是因其他原因不相交的更新），您可能会遇到竞争条件。 - Lucas Wiman

使用此方法时要小心，因为如果您在共享服务器上，如果表的大小适中，则查询很可能会超时。左连接不是用于此目的的。 - captainspi

Answer 3

尽管这个问题已经得到正确的回答（MySQL通过使用带有预期多值设置语法的ON DUPLICATE UPDATE支持此操作），但我想通过提供一个任何使用MySQL的人都可以运行的演示来扩展此问题。

CREATE SCHEMA IF NOT EXISTS `test`;
DROP TABLE IF EXISTS test.new_table;
CREATE TABLE test.new_table (`Key` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`Key`)) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8;

SELECT * FROM test.new_table;
INSERT INTO test.new_table VALUES (1),(2),(3),(4),(5) ON DUPLICATE KEY UPDATE `Key`=`Key`+100;
SELECT * FROM test.new_table;
INSERT INTO test.new_table VALUES (1),(2),(3),(4),(5) ON DUPLICATE KEY UPDATE `Key`=`Key`+100;
SELECT * FROM test.new_table;

输出结果如下：

Empty set (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

+-----+
| Key |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
5 rows in set (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 5  Duplicates: 5  Warnings: 0

+-----+
| Key |
+-----+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+-----+
5 rows in set (0.00 sec)

- Christopher McGowan

Answer 4

尝试添加一个插入触发器，进行预检并在重复键上取消插入（在更新现有行之后）。

不确定它是否适用于批量插入，更不用说对于load data infile是否有效，但这是我能想到的最好的方法。 :-)

- Denis de Bernardy

Answer 5

如果您使用的是Oracle或Microsoft SQL，您可以使用MERGE。然而，MySQL没有直接对应该语句。正如您所指出的那样，有单行解决方案，但它不能很好地处理大量数据。我在这里找到了一篇博客文章，介绍了Oracle和MySQL之间的区别以及如何在MySQL中实现Oracle使用MERGE的功能：

http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/

这不是一个完美的解决方案，可能也不是你想要的完整解决方案，但我相信这是最好的解决方案。

- IAmTimCorey