MySQL 批量插入或更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6286452/
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 bulk INSERT or UPDATE
提问by rid
Is there any way of performing in bulk a query like INSERT OR UPDATEon the MySQL server?
有没有办法像INSERT OR UPDATE在 MySQL 服务器上那样批量执行查询？
INSERT IGNORE ...
won't work, because if the field already exists, it will simply ignore it and not insert anything.
不会工作，因为如果该字段已经存在，它会简单地忽略它而不插入任何内容。
REPLACE ...
won't work, because if the field already exists, it will first DELETEit and then INSERTit again, rather than updating it.
将不起作用，因为如果该字段已经存在，它将首先使用DELETE它，然后再使用INSERT它，而不是更新它。
INSERT ... ON DUPLICATE KEY UPDATE
will work, but it can't be used in bulk.
会起作用，但不能批量使用。
So I'd like to know if there's any command like INSERT ... ON DUPLICATE KEY UPDATEthat can be issued in bulk (more than one row at the same time).
所以我想知道是否有任何类似的命令INSERT ... ON DUPLICATE KEY UPDATE可以批量发出（同时多于一行）。
回答by user359996
You caninsert/update multiple rows using INSERT ... ON DUPLICATE KEY UPDATE. The documentationhas the following example:
您可以使用 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);
Or am I misunderstanding your question?
还是我误解了你的问题？
回答by Kibbee
One possible way to do this is to create a temporary table, insert the data into that, and then do 1 query with a join to insert the records that don't exist followed by and update to the fields that do exist. The basics would be something like this.
一种可能的方法是创建一个临时表，将数据插入其中，然后使用连接执行 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
The syntax may not be exact, but this should give you the basics. Also, I know it's not pretty, but it gets the job done.
语法可能不准确，但这应该为您提供基础知识。此外，我知道它不漂亮，但它完成了工作。
Update
更新
I swapped the order of the insert and update, because doing insert first causes all the inserted rows to be updated when the update is called. If you do update first, only the existing records are updated. This should mean a little less work for the server, although the results should be the same.
我交换了插入和更新的顺序，因为先插入会导致在调用更新时更新所有插入的行。如果您先更新，则只会更新现有记录。这应该意味着服务器的工作会减少一些，尽管结果应该是相同的。
回答by Christopher McGowan
Although this question has been answered correctly already (that MySQL does support this via ON DUPLICATE UPDATE with the expected multiple value set syntax), I'd like to expand on this by providing a demonstration that anyone with MySQL can run:
虽然这个问题已经得到了正确的回答（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;
The output is as follows:
输出如下：
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)
回答by Denis de Bernardy
Try adding an insert trigger that does a pre-flight check and cancels the insert on duplicate key (after updating the existing row).
尝试添加一个插入触发器，该触发器执行飞行前检查并取消对重复键的插入（在更新现有行之后）。
Not sure it'll scale well for bulk inserts, let alone work for load data infile, but it's the best I can think of. :-)
不确定它是否可以很好地用于批量插入，更不用说加载数据 infile 了，但它是我能想到的最好的。:-)
回答by IAmTimCorey
If you were using Oracle or Microsoft SQL, you could use the MERGE. However, MySQL does not have a direct correlation to that statement. There is the single-row solution that you mentioned but, as you pointed out, it doesn't do bulk very well. Here is a blog post I found on the difference between Oracle and MySQL and how to do what Oracle does with MERGEin MySQL:
如果您使用的是 Oracle 或 Microsoft SQL，则可以使用MERGE. 但是，MySQL 与该语句没有直接关联。您提到了单行解决方案，但正如您所指出的，它的批量处理效果不佳。这是我发现的一篇关于 Oracle 和 MySQL 之间的区别以及如何MERGE在 MySQL 中执行 Oracle 操作的博客文章：
http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/
http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/
It isn't a pretty solution and it probably isn't as full a solution as you would like, but I believe that is the best there is for a solution.
这不是一个很好的解决方案，它可能不像您希望的那样完整，但我相信这是最好的解决方案。
