MySQL 批量插入或更新

Question

有什么方法可以在 MySQL 服务器上批量执行诸如 INSERT OR UPDATE 之类的查询吗？

INSERT IGNORE ...

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

REPLACE ...

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

INSERT ... ON DUPLICATE KEY UPDATE

可以，但不能批量使用。

所以我想知道是否有像 INSERT ... ON DUPLICATE KEY UPDATE 之类的命令可以批量发出（同时超过一行）。

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

或者我误解了您的问题？

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

语法可能不准确，但这应该为您提供基础知识。另外，我知道这并不漂亮，但它可以完成工作。

更新

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

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)

Answer 4

尝试添加一个插入触发器，该触发器执行飞行前检查并取消重复键上的插入（更新现有行后）。

不确定它是否能很好地扩展批量插入，更不用说加载数据文件了，但这是我能想到的最好的。 :-)

Answer 5

如果您使用的是 Oracle 或 Microsoft SQL，则可以使用MERGE。然而，MySQL 与该语句没有直接关联。您提到了单行解决方案，但正如您所指出的，它的批量处理效果不佳。这是我发现的一篇博客文章，介绍了 Oracle 和 MySQL 之间的差异以及如何在 MySQL 中使用 MERGE 执行 Oracle 的操作：

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

这不是一个漂亮的解决方案，而且可能没有那么完整您想要的解决方案，但我相信这是最好的解决方案。