如何更新表中的行或将其插入(如果不存在)? [英] How do I UPDATE a row in a table or INSERT it if it doesn't exist?

问题描述

我有下表的计数器:

CREATE TABLE cache (
    key text PRIMARY KEY,
    generation int
);

我想增加一个计数器，或者如果相应的行还不存在，则将其设置为零.有没有办法在标准SQL中没有并发问题的情况下做到这一点?该操作有时是事务的一部分，有时是独立的.

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必须在SQLite，PostgreSQL和MySQL上未经修改地运行.

The SQL must run unmodified on SQLite, PostgreSQL and MySQL, if possible.

搜索产生了一些想法，这些想法可能遇到并发问题，或者特定于数据库:

A search yielded several ideas which either suffer from concurrency issues, or are specific to a database:

• 尝试INSERT新行，如果出现错误，请UPDATE.不幸的是，INSERT上的错误使当前事务中止.

• Try to INSERT a new row, and UPDATE if there was an error. Unfortunately, the error on INSERT aborts the current transaction.

UPDATE该行，如果未修改任何行，请INSERT新建一行.

UPDATE the row, and if no rows were modified, INSERT a new row.

MySQL有一个ON DUPLICATE KEY UPDATE子句.

MySQL has an ON DUPLICATE KEY UPDATE clause.

感谢所有出色的答复.保罗看起来是对的，而且没有一种可移植的方式来做到这一点.对于我来说，这真是令人惊讶，因为这听起来像是一个非常基本的操作.

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.

推荐答案

MySQL(以及随后的SQLite)也支持REPLACE INTO语法:

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

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.

这篇关于如何更新表中的行或将其插入(如果不存在)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！