如何在 MySQL 中更新（如果存在），如果不存在则插入（又名“upsert”或“merge”）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1218905/
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
How do I update if exists, insert if not (AKA "upsert" or "merge") in MySQL?
提问by blub
Is there an easy way to INSERTa row when it does not exist, or to UPDATEif it exists, using one MySQL query?
是否有一种简单的方法可以使用一个 MySQL 查询INSERT来访问不存在的行，或者UPDATE如果存在，则使用一个 MySQL 查询？
回答by chaos
Yes, INSERT ... ON DUPLICATE KEY UPDATE. For example:
是的，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
回答by Armadillo Jim
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 UPDATEsyntax.
@chaos 是正确的：有INSERT ... ON DUPLICATE KEY UPDATE语法。
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:
但是，最初的问题专门询问了 MySQL，在 MySQL 中有REPLACE INTO ...语法。恕我直言，这个命令用于 upserts 更容易、更直接。从手册：
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.
REPLACE 的工作方式与 INSERT 完全相同，但如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行。
Note this is not standard SQL. An example from the manual:
请注意，这不是标准 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)
Edit:Just a fair warning that REPLACE INTOisn't like UPDATE. As the manual says, REPLACEdeletes 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 allcolumns of an existing record (and not merely update somecolumns.) The behavior of MySQL's REPLACE INTOis much like that of Sqlite's INSERT OR REPLACE INTO. See this questionfor some workarounds if you only want to update a few columns (and not all columns) if the record already exists.
编辑：只是一个公平的警告，REPLACE INTO不像UPDATE. 正如手册所说，REPLACE如果该行存在，则删除该行，然后插入一个新行。（注意上面示例中有趣的“受影响的 2 行”。）也就是说，它将替换现有记录的所有列的值（而不仅仅是更新某些列。）MySQL 的行为REPLACE INTO与 Sqlite 的INSERT OR REPLACE INTO. 如果您只想在记录已存在的情况下更新几列（而不是所有列），请参阅此问题以了解一些解决方法。
