SQLite 核心使用技巧：从入门到避坑

#1 发表于 2026-03-21 00:08:28

SQLite 作为全球应用最广泛的嵌入式数据库，以其轻量级、零配置、事务性强的特点著称。然而，正是由于其“简单”的外表，开发者在使用时往往容易陷入一些常见的误区。本文将总结几个核心的使用技巧，帮助你在项目中规避风险，提升开发效率。

1. 活用 `INSERT OR REPLACE` 与 `ON CONFLICT` 子句

在同步数据或处理唯一键冲突时，许多开发者习惯于先 SELECT 判断是否存在，再决定 UPDATE 或 INSERT。这种做法在并发环境下容易产生竞态条件，且效率低下。
SQLite 提供了优雅的冲突解决机制：

INSERT OR REPLACE：如果插入时违反唯一约束，会先删除旧行，再插入新行。注意：这会改变原有行的 rowid，且如果未指定所有列，删除操作可能导致数据丢失（因为旧行被删除，未指定的列变为了默认值）。
ON CONFLICT DO UPDATE（Upsert）：这是更推荐的做法。它允许在冲突时执行更新操作，且不会删除原有行，保持了 rowid 的稳定性。

-- 推荐做法：如果存在则更新数量，否则插入
INSERT INTO inventory (product_id, quantity) 
VALUES (123, 10) 
ON CONFLICT(product_id) DO UPDATE SET quantity = excluded.quantity;

2. 善用 `WITH` 子句（CTE）简化复杂查询

CTE（Common Table Expression，公共表表达式）不仅能让复杂的嵌套查询变得可读，SQLite 还支持递归 CTE，这是处理树形结构数据（如组织结构、评论回复）的神器。

-- 递归查询所有子部门
WITH RECURSIVE 
  dept_tree AS (
    SELECT id, name FROM departments WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name FROM departments d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
  )
SELECT * FROM dept_tree;

3. 严格处理数据类型：STRICT 表

SQLite 的一个特性是“动态类型”（Manifest Typing），允许你将字符串插入整型列。这在某些场景下很灵活，但通常是数据不一致的根源。从 SQLite 3.37.0 开始，引入了 STRICT 表：

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
) STRICT;

启用 STRICT 后，数据库会严格检查插入数据的类型，这能极大地提高代码的健壮性，避免因隐式类型转换导致的查询异常。

4. 充分利用 `EXPLAIN QUERY PLAN`

当查询变慢时，不要猜测。使用 EXPLAIN QUERY PLAN 来查看 SQLite 是否使用了索引。

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 10;

如果输出中包含 SCAN TABLE（全表扫描），就说明需要添加索引。如果是 SEARCH 配合 INDEX，说明索引生效了。

SQLite 作为全球应用最广泛的嵌入式数据库，以其轻量级、零配置、事务性强的特点著称。然而，正是由于其“简单”的外表，开发者在使用时往往容易陷入一些常见的误区。本文将总结几个核心的使用技巧，帮助你在项目中规避风险，提升开发效率。1. 活用 INSERT OR REPLACE 与 ON CONFLICT 子句在同步数据或处理唯一键冲突时，许多开发者习惯于先 SELECT 判断是否存在，再决定 UPDATE 或 INSERT。这种做法在并发环境下容易产生竞态条件，且效率低下。SQLite 提供了优雅的冲突解决机制：INSERT OR REPLACE：如果插入时违反唯一约束，会先删除旧行，再插入新行。注意：这会改变原有行的 rowid，且如果未指定所有列，删除操作可能导致数据丢失（因为旧行被删除，未指定的列变为了默认值）。ON CONFLICT DO UPDATE（Upsert）：这是更推荐的做法。它允许在冲突时执行更新操作，且不会删除原有行，保持了 rowid 的稳定性。sql-- 推荐做法：如果存在则更新数量，否则插入
INSERT INTO inventory (product_id, quantity) 
VALUES (123, 10) 
ON CONFLICT(product_id) DO UPDATE SET quantity = excluded.quantity;2. 善用 WITH 子句（CTE）简化复杂查询CTE（Common Table Expression，公共表表达式）不仅能让复杂的嵌套查询变得可读，SQLite 还支持递归 CTE，这是处理树形结构数据（如组织结构、评论回复）的神器。sql-- 递归查询所有子部门
WITH RECURSIVE 
  dept_tree AS (
    SELECT id, name FROM departments WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name FROM departments d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
  )
SELECT * FROM dept_tree;3. 严格处理数据类型：STRICT 表SQLite 的一个特性是“动态类型”（Manifest Typing），允许你将字符串插入整型列。这在某些场景下很灵活，但通常是数据不一致的根源。从 SQLite 3.37.0 开始，引入了 STRICT 表：sqlCREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
) STRICT;启用 STRICT 后，数据库会严格检查插入数据的类型，这能极大地提高代码的健壮性，避免因隐式类型转换导致的查询异常。4. 充分利用 EXPLAIN QUERY PLAN当查询变慢时，不要猜测。使用 EXPLAIN QUERY PLAN 来查看 SQLite 是否使用了索引。sqlEXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 10;如果输出中包含 SCAN TABLE（全表扫描），就说明需要添加索引。如果是 SEARCH 配合 INDEX，说明索引生效了。