MySQL 如果表中的一行不存在，我如何更新或插入它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/690632/
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 a row in a table or INSERT it if it doesn't exist?
提问by Remy Blank
I have the following table of counters:
我有以下计数器表：
CREATE TABLE cache (
key text PRIMARY KEY,
generation int
);
I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate.
如果相应的行尚不存在，我想增加其中一个计数器，或者将其设置为零。有没有办法在标准 SQL 中没有并发问题的情况下做到这一点？操作有时是事务的一部分，有时是分开的。
The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.
如果可能，SQL 必须在未修改的情况下在 SQLite、PostgreSQL 和 MySQL 上运行。
A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:
搜索产生了几个想法，这些想法要么受到并发问题的影响，要么特定于数据库：
Try to
INSERTa new row, andUPDATEif there was an error. Unfortunately, the error onINSERTaborts the current transaction.UPDATEthe row, and if no rows were modified,INSERTa new row.MySQL has an
ON DUPLICATE KEY UPDATEclause.
尝试
INSERT新行，UPDATE如果有错误。不幸的是，错误INSERT中止了当前的事务。UPDATE行，如果没有修改行，则为INSERT新行。MySQL 有一个
ON DUPLICATE KEY UPDATE子句。
EDIT: Thanks for all the great replies. It looks like Paul is right, and there's not a single, portable way of doing this. That's quite surprising to me, as it sounds like a very basic operation.
编辑：感谢所有伟大的答复。看起来 Paul 是对的，而且没有一种单一的、可移植的方式来做到这一点。这对我来说非常令人惊讶，因为这听起来像是一个非常基本的操作。
回答by andygeers
MySQL (and subsequently SQLite) also support the REPLACE INTO syntax:
MySQL（以及随后的 SQLite）也支持 REPLACE INTO 语法：
REPLACE INTO my_table (pk_id, col1) VALUES (5, '123');
This automatically identifies the primary key and finds a matching row to update, inserting a new one if none is found.
这会自动识别主键并找到要更新的匹配行，如果找不到则插入新行。
回答by Kyle Cronin
SQLite supports replacing a rowif it already exists:
INSERT OR REPLACE INTO [...blah...]
You can shorten this to
您可以将其缩短为
REPLACE INTO [...blah...]
This shortcut was added to be compatible with the MySQL REPLACE INTOexpression.
添加此快捷方式是为了与 MySQLREPLACE INTO表达式兼容。
回答by jmoz
I would do something like the following:
我会做类似以下的事情：
INSERT INTO cache VALUES (key, generation)
ON DUPLICATE KEY UPDATE (key = key, generation = generation + 1);
Setting the generation value to 0 in code or in the sql but the using the ON DUP... to increment the value. I think that's the syntax anyway.
在代码或 sql 中将生成值设置为 0，但使用 ON DUP... 来增加值。无论如何，我认为这就是语法。
回答by Fire Crow
the ON DUPLICATE KEY UPDATE clause is the best solution because: REPLACE does a DELETE followed by an INSERT so for an ever so slight period the record is removed creating the ever so slight possibility that a query could come back having skipped that if the page was viewed during the REPLACE query.
ON DUPLICATE KEY UPDATE 子句是最好的解决方案，因为： REPLACE 执行 DELETE 后跟 INSERT 操作，因此在很短的时间内记录被删除，从而创建查询可能会返回的可能性很小，如果页面是在 REPLACE 查询期间查看。
I prefer INSERT ... ON DUPLICATE UPDATE ... for that reason.
我更喜欢 INSERT ... ON DUPLICATE UPDATE ... 出于这个原因。
jmoz's solution is the best: though I prefer the SET syntax to the parentheses
jmoz 的解决方案是最好的：虽然我更喜欢 SET 语法而不是括号
INSERT INTO cache
SET key = 'key', generation = 'generation'
ON DUPLICATE KEY
UPDATE key = 'key', generation = (generation + 1)
;
回答by BradC
I don't know that you are going to find a platform-neutral solution.
我不知道你会找到一个平台中立的解决方案。
This is commonly called an "UPSERT".
这通常称为“UPSERT”。
See some related discussions:
查看一些相关讨论：
回答by BradC
In PostgreSQL there is no merge command, and actually writing it is not trivial - there are actually strange edge cases that make the task "interesting".
在 PostgreSQL 中没有合并命令，实际上编写它并不是微不足道的 - 实际上有一些奇怪的边缘情况使任务“有趣”。
The best (as in: working in the most possible conditions) approach, is to use function - such as one shown in manual(merge_db).
最好的（如：在最可能的条件下工作）方法是使用函数 - 例如手册（merge_db）中显示的函数。
If you don't want to use function, you can usuallyget away with:
如果你不想使用函数，你通常可以逃脱：
updated = db.execute(UPDATE ... RETURNING 1)
if (!updated)
db.execute(INSERT...)
Just remember that it is not fault proof and it willfail eventually.
请记住，它不是万无一失的，它最终会失败。
回答by Jonathan Leffler
Standard SQL provides the MERGE statement for this task. Not all DBMS support the MERGE statement.
标准 SQL 为该任务提供了 MERGE 语句。并非所有 DBMS 都支持 MERGE 语句。
回答by Seamus Abshere
If you're OK with using a library that writes the SQL for you, then you can use Upsert(currently Ruby and Python only):
如果您可以使用为您编写 SQL 的库，那么您可以使用Upsert（目前仅适用于 Ruby 和 Python）：
Pet.upsert({:name => 'Jerry'}, :breed => 'beagle')
Pet.upsert({:name => 'Jerry'}, :color => 'brown')
That works across MySQL, Postgres, and SQLite3.
这适用于 MySQL、Postgres 和 SQLite3。
It writes a stored procedure or user-defined function (UDF) in MySQL and Postgres. It uses INSERT OR REPLACEin SQLite3.
它在 MySQL 和 Postgres 中编写存储过程或用户定义函数 (UDF)。它INSERT OR REPLACE在 SQLite3 中使用。
回答by Shea
If you don't have a common way to atomically update or insert (e.g., via a transaction) then you can fallback to another locking scheme. A 0-byte file, system mutex, named pipe, etc...
如果您没有原子更新或插入的通用方法（例如，通过事务），那么您可以回退到另一个锁定方案。0 字节文件、系统互斥锁、命名管道等...
回答by Michael Todd
Could you use an insert trigger? If it fails, do an update.
你可以使用插入触发器吗？如果失败，请进行更新。
