SQLite UPSERT/UPDATE 或 INSERT

我需要对 SQLite 数据库执行 UPSERT/INSERT 或 UPDATE。

有一个命令 INSERT OR REPLACE，在许多情况下都很有用。但是，如果由于外键的原因，您希望保持 ID 的自动递增，那么它就不起作用了，因为它删除了行，创建了一个新的行，因此这个新行有一个新的 ID。

这就是那张桌子:

Player-(id 上的主键，user _ name only)

|  id   | user_name |  age   |
------------------------------
|  1982 |   johnny  |  23    |
|  1983 |   steven  |  29    |
|  1984 |   pepee   |  40    |
127989 次浏览
小开

问答风格

经过几个小时的研究和解决这个问题，我发现有两种方法可以完成这个任务，这取决于表的结构以及是否激活了外键限制来保持完整性。我想以一种干净的形式分享这个，以节省一些时间给那些可能处于我这种情况的人。


选项1: 您可以删除该行

换句话说，您没有外键，或者如果您有外键，您的 SQLite 引擎配置为不存在完整性异常。方法是 INSERT OR REPLACE。如果您试图插入/更新 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 作为其他表的外键工作。此外，我使用子句 删除级联创建了表，这意味着它将静默地删除数据。危险。

因此，我首先想到的是 IF 子句，但是 SQLite 只有 凯斯。如果 EXISTS (从 user _ name = ‘ steven’的球员中选择 id)执行一个 更新查询，那么这个 凯斯就不能执行(或者至少我没有管理它) ，如果没有执行，那么就执行 插入查询。不行。

最后，我成功地使用了蛮力。其逻辑是，对于希望执行的每个 UPSERT，首先执行一个 插入或忽略以确保有一行与我们的用户，然后执行一个 更新查询，其中包含与您试图插入的数据完全相同的数据。

与前面相同的数据: 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);
小开

所有提供的答案都完全没有考虑到触发因素(可能还有其他副作用)。 比如

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 change ()将返回上次查询中完成的更新次数。 然后检查 change ()的返回值是否为0，如果为0，则执行:

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

这里有一个方法，不需要暴力“忽略”，只有在存在关键违规的情况下才会起作用。这种方法基于更新中指定的 任何条件。

试试这个..。

-- 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);

How It Works

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

In the above example, if there are no changes from the update (i.e. the record doesn't exist) then Changes() = 0 so the Where clause in the Insert statement evaluates to true and a new row is inserted with the specified data.

如果 Update 是的更新了一个现有行，那么 Changes() = 1(或者更准确地说，如果更新了多于一行，则不为零) ，因此 Insert中的“ Where”子句现在计算结果为 false，因此不会执行插入操作。

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

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

小开

Option 1: Insert -> Update

如果您希望同时避免 changes()=0和 INSERT OR IGNORE，即使您不能负担删除行-您可以使用这种逻辑;

First, 插入 (if not exists) and then 更新 by filtering with the unique key.

Example

-- 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';

Regarding Triggers

注意: 我还没有测试它来查看调用了哪些触发器，但是我 assume如下所示:

如果行不存在

  • 插入前
  • 使用 INSTEADOF 插入
  • 插入后
  • BEFORE UPDATE
  • 使用 INSTEADOF 更新
  • 更新后

if row does exists

  • 更新前
  • 使用 INSTEADOF 更新
  • 更新后

选项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。

小开

您还可以将一个 ON CONFLICT REPLACE 子句添加到 user _ name 唯一约束中，然后直接 INSERT，让 SQLite 决定在发生冲突时应该做什么。见: https://sqlite.org/lang_conflict.html。

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

小开
最佳答案

这是一个迟到的回答。从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 发布)。

但是，如果您确实可以选择升级，那么您就是 强烈鼓励，因为与我的解决方案不同，这里提供的解决方案可以在一个语句中实现所需的行为。另外，您还可以获得所有其他特性、改进和 bug 修复，这些通常都是最近发布的版本所提供的。

小开

For those who have the latest version of sqlite available, you can still do it in a single statement using INSERT OR REPLACE but beware you need to set all the values. However this "clever" SQL works by use of a left-join on the table into which you are inserting / updating and 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)]

B 'updated' value to 4 but value2 (5) was ignored, C inserted.

注意: 如果您的表有一个自动递增的主键，这将不起作用，因为 INSERT 或 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())

output is now:

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

注意，对于 id‘ B’，pkey 2被替换为3

因此，这不是一个理想的解决办法，但在以下情况下是一个好的解决办法:

  • 您没有自动生成的主键
  • 您希望创建一个具有绑定参数的“ upsert”查询
  • 您希望使用 Executemany ()一次合并多行数据。

提交答案