在 SQLite 中，在一个语句中增加计数器或插入行 [英] Increment counter or insert row in one statement, in SQLite

问题描述

在 SQLite 中，给定此数据库架构

In SQLite, given this database schema

CREATE TABLE observations (
    src TEXT,
    dest TEXT,
    verb TEXT,
    occurrences INTEGER
);
CREATE UNIQUE INDEX observations_index
    ON observations (src, dest, verb);

每当一个新的观察元组 (:src, :dest, :verb) 进来时，我想要么增加该元组现有行的出现次数"列，要么添加一个新的如果还没有出现次数 = 1 的行.在具体的伪代码中:

whenever a new observation tuple (:src, :dest, :verb) comes in, I want to either increment the "occurrences" column for the existing row for that tuple, or add a new row with occurrences=1 if there isn't already one. In concrete pseudocode:

if (SELECT COUNT(*) FROM observations
        WHERE src == :src AND dest == :dest AND verb == :verb) == 1:
    UPDATE observations SET occurrences = occurrences + 1
        WHERE src == :src AND dest == :dest AND verb == :verb
else:
    INSERT INTO observations VALUES (:src, :dest, :verb, 1)

我想知道是否可以在一个 SQLite 语句中完成整个操作.这将简化应用程序逻辑(这是完全异步的wrt数据库操作所必需的)，并且还可以避免使用完全相同的键进行双索引查找.INSERT OR REPLACE 似乎不是我想要的，可惜没有 UPDATE OR INSERT.

I'm wondering if it's possible to do this entire operation in one SQLite statement. That would simplify the application logic (which is required to be fully asynchronous wrt database operations) and also avoid a double index lookup with exactly the same key. INSERT OR REPLACE doesn't appear to be what I want, and alas there is no UPDATE OR INSERT.

推荐答案

我从 Igor Tandetnik 在 sqlite-users 上得到了这个答案:

I got this answer from Igor Tandetnik on sqlite-users:

INSERT OR REPLACE INTO observations
VALUES (:src, :dest, :verb,
  COALESCE(
    (SELECT occurrences FROM observations
       WHERE src=:src AND dest=:dest AND verb=:verb),
    0) + 1);

它比 dan04 的方法略有但始终快.

It's slightly but consistently faster than dan04's approach.

这篇关于在 SQLite 中，在一个语句中增加计数器或插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！