当前位置：首页 > news >正文

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

news 2025/8/4 3:26:23

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)

查看全文

http://www.lryc.cn/news/366151.html