如果表中的一行不存在，我如何更新或插入它? [英] 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.

感谢您的所有精彩回复.看起来 Paul 是对的，并且没有一种单一的、可移植的方式来做到这一点.这让我很惊讶，因为这听起来像是一项非常基本的操作.

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屋！