如何在数据表中执行update，如果数据不存在就执行insert？

问题

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.

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:

• Try to INSERT a new row, and UPDATE if there was an error. Unfortunately, the error on INSERT aborts the current transaction.
• UPDATE the row, and if no rows were modified, INSERT a new row.
• MySQL has an ON DUPLICATE KEY UPDATE clause.

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.

方法一

这个方法有一个特别需要注意的问题，它的执行逻辑是，如果数据不存在，就在执行insert插入，如果数据已经存在，它会先把这条数据删除，注意，是先删除，然后执行insert，所以，之前存在的数据是不保留的。

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.

Documentation: https://dev.mysql.com/doc/refman/8.0/en/replace.html

方法二

SQLite supports replacing a row if 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 INTO expression.

方法三

这个方法是最靠谱的方法，它是先检查数据是否存在，如果数据存在(根据主键或唯一索引判断)，执行update更新，如果数据不存在，则执行insert插入操作。

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.

参考：https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

本文文字及图片出自 出处