MySQL批量插入或更新 [英] MySQL bulk INSERT or UPDATE

问题描述

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

Is there any way of performing in bulk a query like INSERT OR UPDATE on the MySQL server?

INSERT IGNORE ...

不起作用，因为如果该字段已经存在，它将直接忽略该字段，而不插入任何内容.

won't work, because if the field already exists, it will simply ignore it and not insert anything.

REPLACE ...

无效，因为如果该字段已经存在，它将首先DELETE对该字段，然后再次INSERT，而不是对其进行更新.

won't work, because if the field already exists, it will first DELETE it and then INSERT it again, rather than updating it.

INSERT ... ON DUPLICATE KEY UPDATE

可以使用，但不能批量使用.

will work, but it can't be used in bulk.

所以我想知道是否有像INSERT ... ON DUPLICATE KEY UPDATE这样的命令可以批量发出(一次不止一次).

So I'd like to know if there's any command like INSERT ... ON DUPLICATE KEY UPDATE that can be issued in bulk (more than one row at the same time).

推荐答案

您可以使用INSERT ... ON DUPLICATE KEY UPDATE插入/更新多行. 文档具有以下示例:

You can insert/update multiple rows using INSERT ... ON DUPLICATE KEY UPDATE. The documentation has the following example:

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?

这篇关于MySQL批量插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！