如果表中的一行不存在，我如何更新它或插入它?

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.

我想增加一个计数器的值，如果对应的行还不存在，则将其设置为0。是否有一种方法可以在标准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 INSERT a new row, and UPDATE if there was an error. Unfortunately, the error on INSERT aborts the current transaction.

尝试插入一个新行，如果有错误，则进行更新。不幸的是，INSERT上的错误会中止当前事务。
UPDATE the row, and if no rows were modified, INSERT a new row.

更新行，如果没有修改行，则插入新的行。
MySQL has an ON DUPLICATE KEY UPDATE clause.

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.

编辑:谢谢你的回复。看起来保罗是对的，而且没有一个单一的，可移植的方法来做这件事。这让我很惊讶，因为这听起来像是一个非常基本的操作。

10 个解决方案

#1

131

MySQL (and subsequently SQLite) also support the REPLACE INTO syntax:

MySQL(以及后来的SQLite)也支持将其替换为语法:

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.

这将自动标识主键并找到要更新的匹配行，如果没有找到，则插入一个新的行。

#2

SQLite supports replacing a row if it already exists:

SQLite支持替换已存在的行:

INSERT OR REPLACE INTO [...blah...]

You can shorten this to

你可以把这个缩短为

REPLACE INTO [...blah...]

This shortcut was added to be compatible with the MySQL REPLACE INTO expression.

这个快捷方式被添加到与MySQL REPLACE兼容的表达式中。

#3

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…增加价值。我想这就是语法。

#4

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.

对重复键更新条款是最好的解决方案,因为:替换一个删除紧随其后插入对于一段非常轻微的记录删除创建非常轻微的查询可能返回跳过,如果查询页面中被取代。

I prefer INSERT ... ON DUPLICATE UPDATE ... for that reason.

我喜欢插入……重复的更新…出于这个原因。

jmoz's solution is the best: though I prefer the SET syntax to the parentheses

jmoz的解决方案是最好的:尽管与括号相比，我更喜欢集合语法

INSERT INTO cache 
SET key = 'key', generation = 'generation'
ON DUPLICATE KEY 
UPDATE key = 'key', generation = (generation + 1)
;

#5

I don't know that you are going to find a platform-neutral solution.

我不知道你会找到一个与平台无关的解决方案。

This is commonly called an "UPSERT".

这通常被称为“维护器”。

See some related discussions:

看到一些相关的讨论:

Solutions for INSERT OR UPDATE on SQL Server
在SQL服务器上插入或更新的解决方案
SQL Server 2005 implementation of MySQL REPLACE INTO?
SQL Server 2005实现的MySQL替换为?

#6

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中没有merge命令，实际上编写这个命令并不简单——实际上有一些奇怪的边缘情况使任务“有趣”。

The best (as in: working in the most possible conditions) approach, is to use function - such as one shown in manual (merge_db).

最好的方法(如:在最可能的条件下工作)是使用函数——如manual (merge_db)中所示。

If you don't want to use function, you can usually get away with:

如果你不想使用函数，你可以这样做:

updated = db.execute(UPDATE ... RETURNING 1)
if (!updated)
  db.execute(INSERT...)

Just remember that it is not fault proof and it will fail eventually.

只要记住这不是错误的证明，它最终会失败。

#7

Standard SQL provides the MERGE statement for this task. Not all DBMS support the MERGE statement.

标准SQL为这个任务提供了MERGE语句。并非所有DBMS都支持MERGE语句。

#8

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字节的文件，系统互斥，命名管道，等等。

#9

Could you use an insert trigger? If it fails, do an update.

你能用插入触发器吗?如果失败，请进行更新。

#10

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 REPLACE in SQLite3.

它在MySQL和Postgres中编写存储过程或用户定义函数(UDF)。它在SQLite3中使用插入或替换。

#1

131