SQLite - UPSERT *not* INSERT 或 REPLACE

admin

SQLite - UPSERT not INSERT 或 REPLACE

sql

在 SQLite 中是否有一些我没有想到的聪明方法来做到这一点？

基本上，如果记录存在，我想更新四列中的三列，如果它不存在，我想用第四列的默认 (NUL) 值插入记录。

ID 是主键，因此 UPSERT 将永远只有一条记录。

（我试图避免 SELECT 的开销，以确定我是否需要 UPDATE 或 INSERT 显然）

建议？

我无法在 SQLite 站点上确认 TABLE CREATE 的语法。我还没有建立一个演示来测试它，但它似乎不受支持。

如果是，我有三列，所以它实际上看起来像：

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

但前两个 blob 不会引起冲突，只有 ID 会所以我假设 Blob1 和 Blob2 不会被替换（根据需要）

当绑定数据是一个完整的事务时，SQLite 中的更新，这意味着要更新的每个发送的行都需要： Prepare/Bind/Step/Finalize 语句与允许使用重置功能的 INSERT 不同

语句对象的生命周期是这样的：

使用 sqlite3_prepare_v2() 创建对象
使用 sqlite3_bind_ 接口将值绑定到主机参数。
通过调用 sqlite3_step() 运行 SQL
使用 sqlite3_reset() 重置语句，然后返回步骤 2 并重复。
使用 sqlite3_finalize() 销毁语句对象。

UPDATE 我猜与 INSERT 相比比较慢，但它与使用主键的 SELECT 相比如何？

也许我应该使用 select 来读取第 4 列 (Blob3)，然后使用 REPLACE 编写一条新记录，将原始第 4 列与前 3 列的新数据混合？

阅读 167

2021-07-01

共1个答案

admin

假设表中有三列：ID、NAME、ROLE

BAD：这将插入或替换所有列的 ID=1 的新值：

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD：这将插入或替换 2 列...... NAME 列将被设置为 NULL 或默认值：

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD：在 SQLite 中使用 SQLite On 冲突子句 UPSERT 支持！UPSERT 语法已添加到 SQLite 3.24.0 版！

UPSERT 是对 INSERT 的特殊语法补充，如果 INSERT 违反唯一性约束，它会导致 INSERT 表现为 UPDATE 或无操作。UPSERT 不是标准 SQL。SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法。

GOOD but tedious：这将更新 2 列。当 ID=1 存在时，NAME 将不受影响。当 ID=1 不存在时，名称将是默认值 (NULL)。

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

这将更新 2 列。当 ID=1 存在时，ROLE 将不受影响。当 ID=1 不存在时，角色将被设置为“Benchwarmer”而不是默认值。

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

2021-07-01