SQLite UPSERT：从入门到实践 – wiki大全

作者： /

SQLite UPSERT：从入门到实践

在数据库操作中，我们经常遇到这样的场景：当一条记录不存在时，我们需要插入它；当它已经存在时，我们需要更新它。这种“插入或更新”的操作被形象地称为 UPSERT。SQLite 作为一个轻量级但功能强大的嵌入式数据库，提供了多种方式来实现 UPSERT 功能。本文将深入探讨 SQLite 中 UPSERT 的概念、语法、使用场景及实践技巧。

1. 什么是 UPSERT？

UPSERT 是 “UPDATE” 和 “INSERT” 的合成词，意指根据特定条件，如果数据存在则更新，如果数据不存在则插入。这是一种非常常见的原子性数据库操作，可以避免先查询再根据结果执行插入或更新的两次操作，从而提高效率并简化逻辑。

2. SQLite 中的 UPSERT 机制

SQLite 提供了两种主要的机制来实现 UPSERT：

  1. INSERT OR REPLACE： 这是一个 SQLite 特有的简化语法，用于处理唯一约束冲突。
  2. INSERT ON CONFLICT (UPSERT 子句)： 这是从 SQLite 3.24.0 (2018-06-04) 开始引入的标准 SQL 扩展，提供了更强大和灵活的冲突处理能力。

2.1 INSERT OR REPLACE：简单粗暴的替换

INSERT OR REPLACE 是 SQLite 中实现 UPSERT 的一种较早且直接的方式。当尝试插入一条新记录，但该操作会导致唯一约束（如 PRIMARY KEY 或 UNIQUE 约束）冲突时，INSERT OR REPLACE 会先删除导致冲突的现有行，然后再插入新的行。

语法：

sql
INSERT OR REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

工作原理：

  1. 尝试插入数据。
  2. 如果遇到唯一约束冲突：
    • 删除（DELETE）现有冲突的行。
    • 插入（INSERT）新行。

示例：

假设我们有一个 products 表：

sql
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
price REAL
);

现在我们执行一些操作：

“`sql
— 第一次插入，’Orange’ 不存在，直接插入
INSERT OR REPLACE INTO products (id, name, price) VALUES (1, ‘Orange’, 1.00);
— 结果： id=1, name=’Orange’, price=1.00

— 第二次插入，id=1 已存在，’Orange’ 也已存在。旧行被删除，新行插入。
— 这里的name是UNIQUE约束，id是PRIMARY KEY，两者都会触发REPLACE
INSERT OR REPLACE INTO products (id, name, price) VALUES (1, ‘Orange’, 1.50);
— 结果： id=1, name=’Orange’, price=1.50 (price updated)

— 第三次插入，id=2 不存在，’Apple’ 不存在，直接插入
INSERT OR REPLACE INTO products (id, name, price) VALUES (2, ‘Apple’, 2.00);
— 结果： id=2, name=’Apple’, price=2.00

— 第四次插入，id=1 已存在，但想用不同的名称。
— 因为id是PRIMARY KEY，会触发替换。旧的(1, ‘Orange’, 1.50)被删除，新的插入。
INSERT OR REPLACE INTO products (id, name, price) VALUES (1, ‘Banana’, 2.50);
— 结果： id=1, name=’Banana’, price=2.50
— 注意：此时 id=1 的 ‘Orange’ 记录已被 ‘Banana’ 替换，id=2 的 ‘Apple’ 还在。
“`

优点：
* 语法简洁，易于理解和使用。

缺点：
* REPLACE 实际上是先 DELETE 后 INSERT。这意味着：
* 行 ID 可能会改变： 如果表使用了 ROWID 或 INTEGER PRIMARY KEY，新插入的行可能会获得一个新的 ROWID（或 INTEGER PRIMARY KEY 值，如果它是自动增长的）。这可能影响依赖于这些 ID 的外部引用。
* 触发器： 会触发 DELETE 和 INSERT 相关的触发器。
* 性能： 对于大型表，删除和重新插入可能比直接更新效率低。

2.2 INSERT ON CONFLICT (UPSERT 子句)：更细粒度的控制

INSERT ON CONFLICT 子句提供了更强大和灵活的冲突处理机制，它允许你指定当发生唯一约束冲突时应该执行的动作。这更符合标准 SQL 的思想，并且避免了 REPLACE 的“先删后插”带来的副作用。

语法：

sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target) DO action;

conflict_target：
用于指定检测冲突的列或索引。通常是 (column_name1, column_name2, ...)，或者省略以指代所有 UNIQUE 约束。

action：
当冲突发生时要执行的动作。主要有两种：

  • DO NOTHING： 如果发生冲突，则不执行任何操作（即忽略这次插入）。
  • DO UPDATE SET column1 = value1, ... WHERE condition： 如果发生冲突，则更新现有行。
2.2.1 ON CONFLICT DO NOTHING

当尝试插入数据但发生唯一约束冲突时，DO NOTHING 会简单地放弃本次插入操作，不修改数据库中的任何数据。

示例：

“`sql
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE
);

— 第一次插入
INSERT INTO users (username, email) VALUES (‘alice’, ‘[email protected]’);
— 结果： (1, ‘alice’, ‘[email protected]’)

— 尝试插入相同的用户名，但 ON CONFLICT DO NOTHING
INSERT INTO users (username, email) VALUES (‘alice’, ‘[email protected]’)
ON CONFLICT (username) DO NOTHING;
— 结果： 无变化，仍是 (1, ‘alice’, ‘[email protected]’)，新的email被忽略。

— 尝试插入相同的email，但 ON CONFLICT DO NOTHING
INSERT INTO users (username, email) VALUES (‘bob’, ‘[email protected]’)
ON CONFLICT (email) DO NOTHING;
— 结果： 无变化，仍是 (1, ‘alice’, ‘[email protected]’)，新的username被忽略。

— 插入一个新用户
INSERT INTO users (username, email) VALUES (‘bob’, ‘[email protected]’)
ON CONFLICT DO NOTHING; — 这里没有指定冲突列，会尝试匹配所有UNIQUE约束
— 结果： (2, ‘bob’, ‘[email protected]’)
“`

2.2.2 ON CONFLICT DO UPDATE SET ... (真正的 UPSERT)

这是实现传统 UPSERT 行为的最推荐方式。当发生唯一约束冲突时，它会更新现有行，而不是删除重插。

EXCLUDED 关键字：
在 DO UPDATE 子句中，可以使用 EXCLUDED 关键字来引用原本要插入但因冲突而被“排除”在外的值。例如，EXCLUDED.column_name 指的是新行中 column_name 的值。

示例：

继续使用 users 表：

“`sql
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE,
last_login_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

— 第一次插入
INSERT INTO users (username, email) VALUES (‘alice’, ‘[email protected]’);
— 结果： (1, ‘alice’, ‘[email protected]’, )

— 尝试插入相同的用户名，更新email和last_login_at
INSERT INTO users (username, email) VALUES (‘alice’, ‘[email protected]’)
ON CONFLICT (username) DO UPDATE SET
email = EXCLUDED.email,
last_login_at = CURRENT_TIMESTAMP;
— 结果： (1, ‘alice’, ‘[email protected]’, )

— 尝试插入一个新用户
INSERT INTO users (username, email) VALUES (‘bob’, ‘[email protected]’)
ON CONFLICT (username) DO UPDATE SET
email = EXCLUDED.email,
last_login_at = CURRENT_TIMESTAMP;
— 结果： (2, ‘bob’, ‘[email protected]’, )

— 尝试插入相同的email，更新username和last_login_at
INSERT INTO users (username, email) VALUES (‘charlie’, ‘[email protected]’)
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
last_login_at = CURRENT_TIMESTAMP;
— 结果： (1, ‘charlie’, ‘[email protected]’, )
— 注意：此时 username 从 ‘alice’ 变成了 ‘charlie’
“`

WHERE 子句与 DO UPDATE：
你还可以在 DO UPDATE 后面添加 WHERE 子句，以进一步限制更新的条件。例如：

sql
INSERT INTO users (username, email) VALUES ('david', '[email protected]')
ON CONFLICT (username) DO UPDATE SET
email = EXCLUDED.email
WHERE users.last_login_at < '2023-01-01'; -- 只有当旧记录的last_login_at早于2023-01-01才更新

优点：
* 原子性： 单次操作完成插入或更新，避免了竞态条件。
* 精细控制： 可以选择在哪个唯一约束冲突时触发 UPSERT，并精确控制更新哪些列。
* 无副作用： 不会像 REPLACE 那样导致行被删除重插，因此不会改变 ROWID，也不会不必要地触发 DELETE 触发器。
* 性能： 通常比 INSERT OR REPLACE 更高效，因为它直接进行更新而不是删除/插入。

3. 选择哪种 UPSERT 方式？

  • 推荐使用 INSERT ON CONFLICT DO UPDATE SET ...。

    • 这是更现代、更灵活、更高效的方式，也是大多数关系型数据库中 UPSERT 的标准实现（尽管语法可能略有不同）。
    • 它能更好地保持数据的完整性，避免了 ROWID 变化等副作用。
    • 如果你的 SQLite 版本是 3.24.0 或更高，并且你的应用不需要兼容更旧的版本，那么这就是首选。

  • 在以下情况下考虑 INSERT OR REPLACE：

    • 你需要兼容非常旧的 SQLite 版本（3.24.0 之前）。
    • 你确实希望在冲突时完全替换现有行（包括改变 ROWID，并且触发 DELETE 和 INSERT 触发器），但这通常不是我们想要的 UPSERT 行为。
    • 你追求极致的简洁语法，并且完全理解并接受其副作用。

4. 实践中的注意事项

  1. 唯一约束（Unique Constraint）是核心： 无论是 INSERT OR REPLACE 还是 ON CONFLICT，它们都依赖于表上定义的唯一约束（PRIMARY KEY 或 UNIQUE 索引）来检测冲突。如果表中没有合适的唯一约束，UPSERT 行为将无法正确触发。
  2. 选择正确的冲突目标： 在 ON CONFLICT (conflict_target) 中，仔细选择作为冲突检测依据的列。这决定了哪种冲突会触发 UPSERT 操作。
  3. 理解 EXCLUDED 关键字： 掌握 EXCLUDED 的用法对于编写正确的 DO UPDATE 逻辑至关重要，它允许你引用即将插入的新值。
  4. 事务管理： UPSERT 操作通常是原子性的，但如果它们是更大事务的一部分，请确保整个事务的 ACID 特性得到维护。
  5. 性能考虑： 虽然 ON CONFLICT DO UPDATE 通常比 INSERT OR REPLACE 更高效，但任何数据库操作的性能都取决于表的大小、索引情况以及更新的复杂度。在性能敏感的场景，始终进行基准测试。

5. 总结

SQLite 的 UPSERT 功能是处理数据“存在则更新，不存在则插入”逻辑的强大工具。INSERT OR REPLACE 提供了简洁的语法，但伴随着“删除再插入”的副作用。而 INSERT ON CONFLICT DO UPDATE SET ... 则提供了更细粒度的控制，避免了这些副作用，并且是更推荐和现代的实现方式。在实际开发中，理解并选择合适的 UPSERT 策略，将大大简化你的数据操作逻辑，并提升应用的健壮性。

滚动至顶部