如何在MySQL中实现upsert：如果不存在则插入？

目录

1 使用 REPLACE

2 使用 INSERT ... ON DUPLICATE KEY UPDATE

---

使用 INSERT IGNORE 有效会导致 MySQL 在尝试执行语句时忽略执行错误 INSERT 。这意味着 包含 索引或  字段 INSERT IGNORE 中重复值的语句 不会 产生错误，而只是完全忽略该特定 命令。其明显目的是 针对数据库中已存在的数据以及进入系统的新数据的组合 执行大量语句。

例如，我们的 books 表可能已经包含一些记录：

mysql> SELECT * FROM books LIMIT 3;
+----+-------------------------+---------------------+----------------+
| id | title                   | author              | year_published |
+----+-------------------------+---------------------+----------------+
|  1 | In Search of Lost Time  | Marcel Proust       |           1913 |
|  2 | Ulysses                 | James Joyce         |           1922 |
|  3 | Don Quixote             | Miguel de Cervantes |           1605 |
+----+-------------------------+---------------------+----------------+
3 rows in set (0.00 sec)

如果我们有大量新数据和现有数据， INSERT 并且其中一部分数据包含字段的匹配值 id （ UNIQUE PRIMARY_KEY 表中的），则使用基本方法 INSERT 将产生预期的错误：

mysql> INSERT INTO books(id, title, author, year_published)
VALUES(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

另一方面，如果我们使用 INSERT IGNORE，则重复尝试将被忽略，并且不会发生任何错误：

mysql> INSERT IGNORE INTO books(id, title, author, year_published)
VALUES(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 0 rows affected (0.00 sec)

1 使用 REPLACE

---

如果您希望实际替换 由于重复 或  如上所述的值而 INSERT 导致命令产生错误的行 ，则一种选择是选择该 语句。UNIQUEPRIMARY KEYREPLACE

当发出 REPLACE 语句时，每个发出的命令都有两种可能的结果：

• 未找到具有匹配值的现有数据行，因此 INSERT 执行标准语句。
• 找到匹配的数据行，导致使用标准语句删除该现有行 ，然后 执行DELETE 正常操作 。INSERT

例如，我们可以使用  苏斯博士的《绿鸡蛋和火腿》REPLACE 替换掉  马塞尔·普鲁斯特的《 id = 1 追忆似水年华》的现有记录：

mysql> REPLACE INTO books(id, title, author, year_published)
VALUES(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 2 rows affected (0.00 sec)

请注意，尽管我们只改变了一行，但结果表明有 两 行受到了影响，因为我们实际上是 DELETED 用现有行 INSERTED 来替换它。

有关使用的更多信息， REPLACE 请参阅 官方文档。

2 使用 INSERT ... ON DUPLICATE KEY UPDATE

---

 对于可能包含重复或 值的INSERTING 行， 替代的（通常是首选的）方法  是使用  语句和子句。UNIQUEPRIMARY KEYINSERT ... ON DUPLICATE KEY UPDATE

与 REPLACE - 由于 DELETE 它在必要时执行的命令而具有固有的破坏性命令 - 使用 INSERT ... ON DUPLICATE KEY UPDATE 是非破坏性的，因为它只会发出 INSERT 或 UPDATE 语句，但绝不会 DELETE。

例如，我们决定替换 “绿鸡蛋和火腿”id = 1 的记录  ，并将其恢复为原始的“ 追忆似水年华” 记录。因此，我们可以采用原始  语句并添加新  子句：INSERTON DUPLICATE KEY UPDATE

mysql> SET @id = 1,@title = 'In Search of Lost Time',@author = 'Marcel Proust',@year_published = 1913;
INSERT INTO books(id, title, author, year_published)
VALUES(@id, @title, @author, @year_published)
ON DUPLICATE KEY UPDATEtitle = @title,author = @author,year_published = @year_published;

请注意，我们使用了正常 UPDATE 语法（但不包括不必要的 table 名称和 SET 关键字），并且只分配值 non-UNIQUE 。此外，尽管该方法不需要它们 ON DUPLICATE KEY UPDATE 才能正常运行，但我们也选择使用它们， user variables 这样我们就不需要  多次指定我们想要的INSERT 实际 值。UPDATE

结果，我们的 id = 1 记录正如 UPDATED 预期的那样：

mysql> SELECT * FROM books LIMIT 1;
+----+------------------------+---------------+----------------+
| id | title                  | author        | year_published |
+----+------------------------+---------------+----------------+
|  1 | In Search of Lost Time | Marcel Proust |           1913 |
+----+------------------------+---------------+----------------+
1 row in set (0.00 sec)