SQLite UPSERT / 更新或插入

问答风格

好吧，在研究和解决这个问题几个小时后，我发现有两种方法可以实现这一点，具体取决于表的结构以及是否激活了外键限制以保持完整性。我想以一种简洁的格式分享这个，以便为可能处于我这种情况的人节省一些时间。

选项 1：您可以负担得起删除该行

换句话说，你没有外键，或者如果你有外键，你的 SQLite 引擎被配置为没有完整性异常。要走的路是插入或替换。如果您尝试插入/更新其 ID 已存在的播放器，SQLite 引擎将删除该行并插入您提供的数据。现在问题来了：如何保持旧 ID 关联？

假设我们要使用数据 user_name='steven' 和 age=32 进行 UPSERT。

看看这段代码：

INSERT INTO players (id, name, age)

VALUES (
    coalesce((select id from players where user_name='steven'),
             (select max(id) from drawings) + 1),
    32)

诀窍在于合并。它返回用户 'steven' 的 id（如果有），否则返回一个新的新 id。

选项 2：您不能删除该行

在尝试了之前的解决方案之后，我意识到在我的情况下这可能最终会破坏数据，因为这个 ID 作为其他表的外键。此外，我使用 ON DELETE CASCADE 子句创建了表，这意味着它会静默删除数据。危险的。

所以，我首先想到了一个IF子句，但是SQLite只有CASE。如果 EXISTS（从 user_name='steven' 的玩家中选择 id），则不能使用此 CASE（或者至少我没有管理它）执行一个 UPDATE 查询，如果没有则 INSERT。不去。

然后，最后我使用了蛮力，成功了。逻辑是，对于您要执行的每个 UPSERT，首先执行 INSERT OR IGNORE 以确保我们的用户有一行，然后使用您尝试插入的完全相同的数据执行 UPDATE 查询。

与之前相同的数据：user_name='steven' 和 age=32。

-- make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

-- make sure it has the right data
UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; 

就这样！

编辑

正如 Andy 评论的那样，尝试先插入然后更新可能会导致触发触发器的频率比预期的要高。在我看来，这不是数据安全问题，但触发不必要的事件确实没有什么意义。因此，改进的解决方案是：

-- Try to update any existing row
UPDATE players SET age=32 WHERE user_name='steven';

-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

---

这是一个迟到的答案。从 2018 年 6 月 4 日发布的 SQLIte 3.24.0 开始，终于支持遵循 PostgreSQL 语法的 UPSERT 子句。

INSERT INTO players (user_name, age)
  VALUES('steven', 32) 
  ON CONFLICT(user_name) 
  DO UPDATE SET age=excluded.age;

注意：对于那些必须使用早于 3.24.0 的 SQLite 版本的人，请参考下面的答案（由我发布，@MarqueIV）。但是，如果您确实可以选择升级，强烈建议您这样做，因为与我的解决方案不同，此处发布的解决方案在单个语句中实现了所需的行为。此外，您还可以获得更新版本通常附带的所有其他功能、改进和错误修复。

---

这是一种不需要蛮力“忽略”的方法，该方法仅在存在密钥违规时才有效。这种方式基于您在更新中指定的任何条件工作。

尝试这个...

-- Try to update any existing row
UPDATE players
SET age=32
WHERE user_name='steven';

-- If no update happened (i.e. the row didn't exist) then insert one
INSERT INTO players (user_name, age)
SELECT 'steven', 32
WHERE (Select Changes() = 0);

这个怎么运作

这里的“魔法酱”是在 Where 子句中使用 Changes()。 Changes() 表示受最后一次操作（在本例中为更新）影响的行数。

在上面的示例中，如果更新没有更改（即记录不存在），则 Changes() = 0 因此 Insert 语句中的 Where 子句计算为 true 并插入新行指定的数据。

如果 Update 确实 更新了现有行，则 Changes() = 1（或更准确地说，如果更新了不止一行，则不为零），因此 {3 中的 'Where' 子句} 现在评估为 false，因此不会发生插入。

这样做的好处是不需要蛮力，也不需要删除，然后重新插入可能导致外键关系中下游键混乱的数据。

此外，由于它只是一个标准的 Where 子句，它可以基于您定义的任何内容，而不仅仅是键违规。同样，您可以在允许表达式的任何地方将 Changes() 与您想要/需要的任何其他内容结合使用。

---

所有给出的答案的问题是完全没有考虑触发器（可能还有其他副作用）。像这样的解决方案

INSERT OR IGNORE ...
UPDATE ...

当行不存在时，导致执行两个触发器（用于插入，然后用于更新）。

正确的解决方案是

UPDATE OR IGNORE ...
INSERT OR IGNORE ...

在这种情况下，只执行一条语句（当行存在或不存在时）。

---

拥有一个没有漏洞的纯 UPSERT（对于程序员），不依赖唯一键和其他键：

UPDATE players SET user_name="gil", age=32 WHERE user_name='george'; 
SELECT changes();

SELECT changes() 将返回上次查询中完成的更新次数。然后检查changes()的返回值是否为0，如果是则执行：

INSERT INTO players (user_name, age) VALUES ('gil', 32); 

---

选项 1：插入 -> 更新

如果您想避免 changes()=0 和 INSERT OR IGNORE 即使您无法删除该行 - 您可以使用此逻辑；

首先，插入（如果不存在），然后通过使用唯一键过滤进行更新。

例子

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Insert if NOT exists
INSERT INTO players (user_name, age)
SELECT 'johnny', 20
WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name='johnny' AND age=20);

-- Update (will affect row, only if found)
-- no point to update user_name to 'johnny' since it's unique, and we filter by it as well
UPDATE players 
SET age=20 
WHERE user_name='johnny';

关于触发器

注意：我尚未对其进行测试以查看正在调用哪些触发器，但我假设以下内容：

如果行不存在

插入前

使用 INSTEAD OF 插入

插入后

更新前

使用 INSTEAD OF 更新

更新后

如果行确实存在

更新前

使用 INSTEAD OF 更新

更新后

选项 2：插入或替换 - 保留您自己的 ID

这样你就可以有一个单一的SQL命令

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Single command to insert or update
INSERT OR REPLACE INTO players 
(id, user_name, age) 
VALUES ((SELECT id from players WHERE user_name='johnny' AND age=20),
        'johnny',
        20);

编辑：添加选项 2。

---

你也可以在你的 user_name 唯一约束中添加一个 ON CONFLICT REPLACE 子句，然后插入，让 SQLite 找出在发生冲突时该怎么做。请参阅：https://sqlite.org/lang_conflict.html。

还要注意关于删除触发器的句子：当 REPLACE 冲突解决策略删除行以满足约束时，当且仅当启用递归触发器时，删除触发器才会触发。

---

对于那些拥有最新版本的 sqlite 的人，您仍然可以使用 INSERT OR REPLACE 在单个语句中执行此操作，但请注意您需要设置所有值。然而，这个“聪明”的 SQL 通过在要插入/更新的表上使用左连接和 ifnull 来工作：

import sqlite3

con = sqlite3.connect( ":memory:" )

cur = con.cursor()
cur.execute("create table test( id varchar(20) PRIMARY KEY, value int, value2 int )")
cur.executemany("insert into test (id, value, value2) values (:id, :value, :value2)",
        [ {'id': 'A', 'value' : 1, 'value2' : 8 }, {'id': 'B', 'value' : 3, 'value2' : 10 } ] )
cur.execute('select * from test')
print( cur.fetchall())

con.commit()
cur = con.cursor()

# upsert using insert or replace. 
 # when id is found it should modify value but ignore value2
 # when id is not found it will enter a record with value and value2
upsert = '''
   insert or replace into test
        select d.id, d.value, ifnull(t.value2, d.value2) from ( select :id as id, :value as value, :value2 as value2 ) d  
           left join test t on d.id = t.id
    '''           


upsert_data = [ { 'id' : 'B', 'value' : 4, 'value2' : 5 },
                { 'id' : 'C', 'value' : 3, 'value2' : 12 } ]
       
cur.executemany( upsert, upsert_data )

cur.execute('select * from test')
print( cur.fetchall())

该代码的前几行用于设置表，其中包含一个 ID 主键列和两个值。然后输入 ID 为“A”和“B”的数据

第二部分创建“upsert”文本，并为 2 行数据调用它，其中一行的 ID 为“B”，但未找到，另一行的 ID 为“C”。

当你运行它时，你会发现最后产生的数据

$python3 main.py
[('A', 1, 8), ('B', 3, 10)]
[('A', 1, 8), ('B', 4, 10), ('C', 3, 12)]

将值“更新”为 4，但忽略了 value2 (5)，插入了 C。

注意：如果您的表具有自动递增的主键，则这不起作用，因为 INSERT OR REPLACE 将用新的数字替换该数字。

添加这样一列的轻微修改

import sqlite3

con = sqlite3.connect( ":memory:" )

cur = con.cursor()
cur.execute("create table test( pkey integer primary key autoincrement not null, id varchar(20) UNIQUE not null, value int, value2 int )")
cur.executemany("insert into test (id, value, value2) values (:id, :value, :value2)",
        [ {'id': 'A', 'value' : 1, 'value2' : 8 }, {'id': 'B', 'value' : 3, 'value2' : 10 } ] )
cur.execute('select * from test')
print( cur.fetchall())

con.commit()
cur = con.cursor()

# upsert using insert or replace. 
 # when id is found it should modify value but ignore value2
 # when id is not found it will enter a record with value and value2
upsert = '''
   insert or replace into test (id, value, value2)
        select d.id, d.value, ifnull(t.value2, d.value2) from ( select :id as id, :value as value, :value2 as value2 ) d  
           left join test t on d.id = t.id
    '''           


upsert_data = [ { 'id' : 'B', 'value' : 4, 'value2' : 5 },
                { 'id' : 'C', 'value' : 3, 'value2' : 12 } ]
       
cur.executemany( upsert, upsert_data )

cur.execute('select * from test')
print( cur.fetchall())

现在的输出是：

$python3 main.py
[(1, 'A', 1, 8), (2, 'B', 3, 10)]
[(1, 'A', 1, 8), (3, 'B', 4, 10), (4, 'C', 3, 12)]

注意 pkey 2 被 3 替换为 id 'B'

因此，这并不理想，但在以下情况下是一个很好的解决方案：

您没有自动生成的主键

您想创建一个带有绑定参数的“upsert”查询

您想使用 executemany() 一次性合并多行数据。

---