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

Question

我有以下计数器表：

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

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

如果可能的话，SQL 必须在 SQLite、PostgreSQL 和 MySQL 上运行且未经修改。

搜索产生了几个想法，这些想法要么存在并发问题，要么特定于数据库：

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

• UPDATE 行，如果没有修改任何行，则INSERT 一个新行。

• MySQL 有一个 ON DUPLICATE KEY UPDATE 子句。

编辑：感谢所有精彩的回复。 看来保罗是对的，而且没有一种单一的、可移植的方法可以做到这一点。 这让我感到非常惊讶，因为这听起来像是一个非常基本的操作。

Answer 1

MySQL（以及随后的 SQLite）还支持 REPLACE INTO 语法：

REPLACE INTO my_table (pk_id, col1) VALUES (5, '123');

这会自动识别主键并查找要更新的匹配行，如果未找到则插入新行。

文档： https://dev.mysql.com/doc/refman/8.0 /en/replace.html

Answer 2

SQLite 支持替换行（如果已存在）：

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

您可以将其缩短

REPLACE INTO [...blah...]

为此快捷方式已添加为与 MySQL REPLACE INTO 表达式兼容。

Answer 3

我会执行如下操作：

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

在代码或 sql 中将生成值设置为 0，但使用 ON DUP... 来增加该值。 我认为无论如何这就是语法。

Answer 4

ON DUPLICATE KEY UPDATE 子句是最佳解决方案，因为：
REPLACE 会先执行 DELETE，然后再执行 INSERT，因此在很短的一段时间内，记录会被删除，从而导致如果在 REPLACE 查询期间查看页面，则查询可能会跳过该记录而返回。

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

jmoz的解决方案是最好的：
虽然我更喜欢 SET 语法而不是括号

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

Answer 5

我不知道您是否会找到平台中立的解决方案。

这通常称为“UPSERT”。

请参阅一些相关讨论：

• SQL Server 上的 INSERT 或 UPDATE 解决方案
• SQL Server 2005 实施 MySQL REPLACE INTO？

Answer 6

在 PostgreSQL 中没有合并命令，实际上编写它并不简单 - 实际上有一些奇怪的边缘情况使任务“有趣”。

最好的（如：在最可能的条件下工作）方法是使用函数 - 例如 手册 (merge_db)。

如果您不想使用函数，您通常可以这样做：

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

只要记住它不是防错的，它最终将会失败。

Answer 7

标准 SQL 提供了用于此任务的 MERGE 语句。 并非所有 DBMS 都支持 MERGE 语句。

Answer 8

如果您没有原子更新或插入的通用方法（例如，通过事务），那么您可以回退到另一个锁定方案。 0 字节文件、系统互斥体、命名管道等...

Answer 9

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

Answer 10

如果您同意使用为您编写 SQL 的库，那么您可以使用 Upsert （目前仅限 Ruby 和 Python）：

Pet.upsert({:name => 'Jerry'}, :breed => 'beagle')
Pet.upsert({:name => 'Jerry'}, :color => 'brown')

适用于 MySQL、Postgres 和 SQLite3。

它在 MySQL 和 Postgres 中编写存储过程或用户定义函数 (UDF)。 它在 SQLite3 中使用INSERT OR REPLACE。