UPSERT *不是* INSERT 或 REPLACE。

Question

http://en.wikipedia.org/wiki/Upsert

在SQL Server上插入更新存储过程

是否有一些聪明的方法可以在SQLite中完成这个操作，而我没有考虑到的呢？

基本上，如果记录存在，则我要更新四列中的三列。 如果记录不存在，则我希望使用第四列的默认（NULL）值插入该记录。

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 中的 UPDATE 操作是完整事务，这意味着每个要更新的发送行都需要进行准备 / 绑定 / 步骤 / 完成语句，而 INSERT 则允许使用重置功能

语句对象的生命周期大致如下：

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

我猜更新操作比 INSERT 操作慢，但是与使用主键的 SELECT 操作相比如何？

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

- Mike Trader

---

SQLite - UPSERT在预发布中可用。参考：http://sqlite.1065341.n5.nabble.com/UPSERT-available-in-pre-release-td101550.html - Gaurav

UPSERT在SQLite 3.24.0版本中可用。 - pablo_worker

Answer 1

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

---

不好的写法：这将插入或替换所有列，使用新值来替换ID=1的行：

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

---

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

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

GOOD: 使用SQLite的On conflict语句 SQLite中的UPSERT支持! SQLite版本3.24.0中添加了UPSERT语法！UPSERT是INSERT的一种特殊语法，如果插入违反了唯一性约束，则会导致INSERT行为类似于UPDATE或不执行任何操作。 UPSERT不是标准SQL。 SQLite中的UPSERT遵循由PostgreSQL建立的语法。

GOOD but tedious: 这将更新2列。 当ID = 1存在时，名称不受影响。 当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存在时，角色不受影响。 当ID=1不存在时，角色将设为“替补”而不是默认值。

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

- Eric B

---

+1 太棒了！嵌入的 SELECT 子句为您提供了灵活性，如果您需要结合/比较任何字段的旧值和新值，则可以覆盖默认的 ON CONFLICT REPLACE 功能。 - G__

如果员工在其他行中被引用，并且设置了级联删除，那么其他行将会被替代删除。 - Don Reba

最后一个查询不正确。它应该是：coalesce((select role from Employee where id = 1),'替补') - Viet

你能解释一下为什么在你的第一个例子中，“This will insert or replace all columns with new values for ID=1:”被认为是“BAD”吗？你提供的命令旨在创建一个新记录，该记录具有ID 1，名称为“John Foo”和角色为“CEO”，或者将ID为1的记录（假设id是主键）覆盖为该数据，那么为什么如果确切地发生这种情况就会出问题呢？ - O. R. Mapper

明白了，但这并不是第一个例子中所发生的情况。第一个例子旨在强制设置所有列，这正是发生的事情，无论记录是插入还是替换。那么，为什么会被认为是不好的呢？链接的答案也只指出了当指定列的子集时可能会发生什么不好的事情，比如你的第二个例子; 它似乎没有详细说明发生在你的第一个例子中INSERT OR REPLACE同时为所有列指定值的任何不良影响。 - O. R. Mapper

Answer 2

插入或替换（INSERT OR REPLACE）并不等同于“UPSERT”。

假设我有一个名为Employee的表，其中包含字段id、name和role：

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

糟糕，您丢失了员工号码1的姓名。SQLite已用默认值替换它。

UPSERT 的预期输出应该是更改角色并保留名称。

- gregschlom

---

这里有一个 Ruby 的 upsert 库 和一个 Python 的 upsert 库。 - Seamus Abshere

但如果一个表有两列（且没有空值），在该表上执行INSERT OR REPLACE，那么它等效于UPSERT。 - QED

同意，这并不是100%的upsert，但在某些情况下可以像上面那样使用。因此，程序员需要自行判断。 - Gilco

@QED 不是的，因为删除+插入（即替换）是2个DML语句，每个语句都有自己的触发器。这与仅有1个更新语句不同。 - Sebas

Answer 3

Eric B's answer 如果你只想保留一个或两个现有行的列，则可以使用。如果你想保留很多列，它会变得过于繁琐。

这里有一种方法，可以适应任何一侧的任意数量的列。为了说明这一点，我将假设以下模式：

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

需要特别注意的是，name 是该行的自然键（natural key），而 id 仅用于外键，因此 SQLite 在插入新行时会自行选择 ID 值。但是，当基于其 name 更新现有行时，我希望它继续具有旧的 ID 值（显然！）。

我使用以下结构来实现真正的 UPSERT：

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

这个查询的确切形式可能会有些不同。关键在于使用INSERT SELECT和左外连接，将现有行与新值连接起来。

在这里，如果以前不存在行，则old.id将为NULL，SQLite将自动分配一个ID，但如果已经存在这样的行，则old.id将具有实际值，并且将被重新使用。这正是我想要的。

事实上，这非常灵活。请注意，ts列在两侧都完全缺失 - 因为它具有默认值，所以在任何情况下SQLite都会做正确的事情，所以我不必自己处理它。

您还可以在new和old两侧包含一列，然后在外部SELECT中使用例如COALESCE(new.content, old.content)，表示“如果有新内容，请插入新内容，否则保留旧内容” - 例如，如果您正在使用固定查询并使用占位符绑定新值。

- Aristotle Pagaltzis

---

+1，运行得非常好，但在 SELECT ... AS old 中添加一个 WHERE name = "about" 的限制条件可以加快速度。如果你有 1 百万行或更多行数据，否则会非常慢。 - user918938

好观点，你的评论加一。不过我会把那个留出来不放在答案里，因为添加这样的WHERE子句会在查询中引入冗余，而我最初想出这种方法是为了避免这种冗余。与往常一样：当需要性能时，去规范化——这种情况下是查询的结构。 - Aristotle Pagaltzis

如果你想的话，可以将亚里士多德的例子简化为以下形式：将以下内容插入或替换到page表中：(id, name, title, content, author) 从一个空表 LEFT JOIN (从name = 'about'的page表中选择所有) 中进行选择，并将author设置为42。 选择的内容为id，'about'，'About this site'，content。 - jcox

当执行替换（即更新）时，这是否会不必要地触发“ON DELETE”触发器？ - Karakuri

它一定会触发ON DELETE触发器，不确定是否会带来不必要的影响。对于大多数用户来说，它可能是不必要的甚至是不想要的，但对于某些用户可能并非如此。同样，对于它还将级联删除任何具有外键引用到相关行的行 - 这可能是许多用户的问题。遗憾的是，SQLite没有更接近真正UPSERT的功能。（除了使用INSTEAD OF UPDATE触发器模拟它，我猜。） - Aristotle Pagaltzis

Answer 4

此答案已更新，下面的评论不再适用。

2018-05-18 最新消息。

SQLite 支持 UPSERT 语法！ UPSERT 语法在 SQLite 3.24.0 版本中被添加（待发布）！

UPSERT 是 INSERT 的一种特殊语法，如果执行 INSERT 操作会违反唯一性约束，则让 INSERT 像 UPDATE 或者什么都不做。UPSERT 不是标准 SQL。SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法规则。

或者：

另一种完全不同的方法是，在我的应用程序中，当我在内存中创建行时，我将其行 ID 设置为 long.MaxValue。（MaxValue 永远不会被用作 ID，你活不够长……）然后，如果 rowID 不是那个值，那么它必须已经在数据库中了，需要进行 UPDATE；如果是 MaxValue，则需要插入。这只有在您可以跟踪应用程序中的行 ID 时才有用。

- AnthonyLambert

---

INSERT INTO table(...) SELECT ... WHERE changes() = 0; 对我有效。 - Manuel Lopera

简单明了，但如果在更新和插入之间删除行，则存在竞态条件，不是吗？ - w00t

@w00t 如果你在一个事务内运行这个会怎么样？ - copolii

@copolii 实际上不确定这是否足够，也许它应该锁定行？ - w00t

这是nodejs sqlite-upsert模块的工作方式。https://github.com/pbrandt1/sqlite3-upsert/blob/master/index.js - phyatt

Answer 5

如果您通常要执行更新操作，我会建议您按照以下步骤进行：

1. 开始一个事务
2. 执行更新操作
3. 检查所影响的行数
4. 如果为0，则执行插入操作
5. 提交事务

如果您通常要执行插入操作，我则建议您按照以下步骤进行：

1. 开始一个事务
2. 尝试插入数据
3. 检查是否出现主键冲突错误
4. 如果有错误，则执行更新操作
5. 提交事务

这样做可以避免选择（select）操作，并且可以在Sqlite上进行事务控制。

- sambo99

---

如果你要在第三步使用sqlite3_changes()来检查行数，请确保不要在多个线程中使用同一个DB句柄进行修改。 - Linulin

以下方式是否更简洁且效果相同： 1）从表中选择id，其中id = 'x' 2）如果（ResultSet.rows.length == 0），则更新表，其中id = 'x'。 - Florin

Answer 6

我知道这是一个老帖子，但我最近一直在使用sqlite3，并想出了这种方法，它更适合我动态生成参数化查询的需求：

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

仍然是2个带有where子句的查询，但似乎可以解决问题。我也想到了这样一个想法，即如果对changes()的调用大于零，则sqlite可以完全优化掉更新语句。无论它是否实际做到这一点超出了我的知识范畴，但人总是可以做梦的，不是吗？ ;)

额外加分的话，您可以追加此行代码，它会返回行的id，无论是新插入的行还是现有行。

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

- Chris Stavropoulos

Answer 7

从版本3.24.0开始，SQLite支持UPSERT。

根据文档:

UPSERT是一种特殊的INSERT语法，它使INSERT在违反唯一性约束时表现为UPDATE或no-op。 UPSERT不是标准SQL。 SQLite中的UPSERT遵循由PostgreSQL确定的语法。UPSERT语法已添加到SQLite 3.24.0版本（待定）。

UPSERT是普通INSERT语句，后跟特殊的ON CONFLICT子句。

^{图片来源：https://www.sqlite.org/images/syntax/upsert-clause.gif}

---

示例：

CREATE TABLE t1(id INT PRIMARY KEY, c TEXT);
INSERT INTO t1(id, c) VALUES (1,'a'), (2, 'b');
SELECT * FROM t1;


INSERT INTO t1(id, c) VALUES (1, 'c');
-- UNIQUE constraint failed: t1.id

INSERT INTO t1(id, c) VALUES (1, 'c')
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

INSERT INTO t1(id, c)
VALUES (1, 'c')
ON CONFLICT(id) DO UPDATE SET c = excluded.c;

SELECT * FROM t1;

db<>fiddle演示

- Lukasz Szozda

---

截至API 27，安卓系统仍停留在3.19版本。 - Brill Pappin

Answer 8

以下是一种真正的UPSERT（UPDATE或INSERT）解决方案，而不是使用INSERT OR REPLACE（在许多情况下功能不同）。

它的工作方式如下：
1. 尝试更新是否存在具有相同ID的记录。
2. 如果更新不更改任何行（NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)），则插入该记录。

因此，要么更新现有记录，要么执行插入操作。

重要细节是使用changes() SQL函数检查更新语句是否命中了任何现有记录，并且只有在没有命中任何记录时才执行插入语句。

需要注意的一件事是，changes()函数不返回较低级别触发器执行的更改（请参见http://sqlite.org/lang_corefunc.html#changes），请确保考虑到这一点。

以下是SQL...

测试更新：

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 2, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

测试插入：

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 3, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

- David Liebeherr

---

相较于Eric的方案，这对我来说似乎是更好的解决方案。不过，INSERT INTO Contact (Id, Name) SELECT 3, 'Bob' WHERE changes() = 0; 也应该能够起到同样的作用。 - bkausbk

Answer 9

伯恩哈特的最新消息：

在SQLite中确实可以进行upsert操作，只是与您习惯的略有不同。它看起来会像这样：

INSERT INTO table_name (id, column1, column2) 
VALUES ("youruuid", "value12", "value2")
ON CONFLICT(id) DO UPDATE 
SET column1 = "value1", column2 = "value2"

- Brill Pappin

---

只是想指出给Android程序员（也可能是其他人），"upsert"语法是从2018年（Sqlite 3.24）开始的。因此，除非您使用的是最新的API，否则您将无法使用此功能。 - H.A.H.

Answer 10

我知道的最好方法是先进行更新，然后再进行插入。虽然需要执行“select”语句来检索主键，但这并不会带来太大的负担。

您应该能够根据自己的表和字段名称修改以下语句以达到您想要的效果。

--first, update any matches
UPDATE DESTINATION_TABLE DT
SET
  MY_FIELD1 = (
              SELECT MY_FIELD1
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
 ,MY_FIELD2 = (
              SELECT MY_FIELD2
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
WHERE EXISTS(
            SELECT ST2.PRIMARY_KEY
            FROM
              SOURCE_TABLE ST2
             ,DESTINATION_TABLE DT2
            WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY
            );

--second, insert any non-matches
INSERT INTO DESTINATION_TABLE(
  MY_FIELD1
 ,MY_FIELD2
)
SELECT
  ST.MY_FIELD1
 ,NULL AS MY_FIELD2  --insert NULL into this field
FROM
  SOURCE_TABLE ST
WHERE NOT EXISTS(
                SELECT DT2.PRIMARY_KEY
                FROM DESTINATION_TABLE DT2
                WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY
                );

- kogus

---

我认为这不是一个好主意，因为你需要向数据库引擎发送两次请求。 - user6695319