当前位置：文江博客话题详情

在 MySQL 中，如果存在如何更新，如果不存在则插入（又名“upsert”或“merge”）？

发布于 2024-08-01 13:08:52 字数 53 浏览 6 评论 0原文

有没有一种简单的方法可以使用一个 MySQL 查询来在行不存在时插入行或更新行（如果存在）？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

记忆之渊 2024-08-08 13:08:52

使用 INSERT ... ON DUPLICATE KEY UPDATE< /代码>。例如：

INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1

Use INSERT ... ON DUPLICATE KEY UPDATE. For example:

INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1

回复收藏 0 原文

羁〃客ぐ 2024-08-08 13:08:52

我知道这是一个老问题，但谷歌最近把我带到了这里，所以我想其他人也会来这里。

@chaos是正确的：有 INSERT 。 .. ON DUPLICATE KEY UPDATE 语法。

然而，最初的问题专门询问了 MySQL，在 MySQL 中有 <代码>REPLACE INTO ... 语法。恕我直言，这个命令对于更新插入来说更容易、更直接。从手册：

REPLACE 的工作方式与 INSERT 完全相同，只不过如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前会删除旧行。

请注意，这不是标准 SQL。手册中的一个示例：

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

编辑：只是一个合理的警告，REPLACE INTO 与UPDATE 不同。正如手册所述，REPLACE 会删除该行（如果存在），然后插入一个新行。（请注意上面示例中有趣的“受影响的 2 行”。）也就是说，它将替换现有记录的所有列的值（而不仅仅是更新某些列。）MySQL 的 REPLACE INTO 行为与 Sqlite 的 INSERT OR REPLACE INTO 非常相似。如果您只想更新几列（（并非所有列）如果记录已存在。

I know this is an old question, but the Google lead me here recently so I imagine others come here, too.

@chaos is correct: there is the INSERT ... ON DUPLICATE KEY UPDATE syntax.

However, the original question asked about MySQL specifically, and in MySQL there is the REPLACE INTO ... syntax. IMHO, this command is easier and more straightforward to use for upserts. From the manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Note this is not standard SQL. An example from the manual:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

Edit: Just a fair warning that REPLACE INTO isn't like UPDATE. As the manual says, REPLACE deletes the row if it exists, then inserts a new one. (Note the funny "2 rows affected" in the example above.) That is, it will replace the values of all columns of an existing record (and not merely update some columns.) The behavior of MySQL's REPLACE INTO is much like that of Sqlite's INSERT OR REPLACE INTO. See this question for some workarounds if you only want to update a few columns (and not all columns) if the record already exists.