SQLite 长期被当作“小项目临时存数据”的嵌入式数据库很多开发者对它的第一印象是只能单机使用、并发写入容易报错、功能比 MySQL 和 PostgreSQL 差得多。实际上SQLite 是当前部署范围最广的关系型数据库引擎之一它完整实现了 ACID 事务并且支持窗口函数、递归 CTE、JSON 处理、全文检索、UPSERT、生成列、部分索引、WAL 并发模型等一批在大数据库里才常见的高级能力。Mikaël Francoeur 在 MTL_code 的分享标题 “SQLite is WAY More Powerful Than You Think” 说的正是这个现象SQLite 不是功能少而是大多数用户只用了它的少部分能力。这篇文章会从工程实践角度重新过一遍 SQLite 的高阶能力先纠正几个常见的认知偏差再分别演示窗口函数、递归 CTE、JSON、FTS5 全文检索、UPSERT、生成列和部分索引最后结合 DB Browser for SQLite 的本地调试方式和 Turso 的边缘数据库场景给出可以直接落地的结论。所有 SQL 都基于最小示例表结构读者可以在自己的电脑上用 sqlite3 命令行或 GUI 工具逐条执行。1. 先重新评估 SQLite 的真实能力不要急着叫它“玩具数据库”1.1 SQLite 的本质嵌入式关系型数据库引擎SQLite 不是一个需要独立启动的数据库服务进程而是一个以 C 语言库形式存在的关系型数据库引擎。应用程序把 SQLite 编译进自己的进程里直接调用它的 API 读写本地磁盘文件。这是它和 MySQL、PostgreSQL 最本质的区别没有网络监听、没有独立进程、没有数据库管理员账号数据全部落在普通文件里。这个设计带来几个直接影响。第一部署成本极低应用装上就能用不需要单独安装数据库软件。第二单文件存储让备份、迁移、测试变得非常简单复制一个.db文件就等于完成了大部分数据搬迁。第三因为数据库和应用程序同进程普通查询没有网络往返开销小数据量场景下读性能非常可观。SQLite 并不是“简化版数据库”。它支持标准 SQL 的绝大部分能力包括事务、触发器、视图、外键、CHECK 约束、递归 CTE、窗口函数、JSON 函数和全文检索。它通过“虽然小但完整”的方式在嵌入式场景中提供了接近传统关系型数据库的功能密度。1.2 常见认知偏差与真实情况很多“SQLite 很弱”的说法来自只插入过几条数据的初学者而不是来自对官方文档和实际压测的理解。下面把最常见的误判和真实情况放在一起看。常见说法真实情况SQLite 不支持并发同一时刻只允许一个写事务但允许多个读事务并行启用 WAL 后读写可以并发写请求之间仍然串行SQLite 只适合小型项目桌面软件、移动 App、IoT 设备、浏览器都有大量生产使用低并发 Web 服务也能承载可观读流量SQLite 没有事务能力支持完整 ACID 事务具备原子提交、回滚、崩溃恢复能力SQLite 的 SQL 功能少支持窗口函数、CTE、JSON、FTS5、UPSERT、生成列、部分索引、触发器、视图、递归查询等SQLite 没有安全机制它把权限控制交给操作系统文件权限适合嵌入式场景不适合多租户服务端直接暴露这里要强调一点并发写确实是 SQLite 的短板一个时刻只能有一个写事务。但很多业务场景是读多写少SQLite 在这种负载下表现稳定。关键是在选型时把“单机、单写者、多读者”的模型和自身业务对齐而不是直接套用 MySQL 的心智模型。1.3 搭建本地验证环境命令行、GUI 和样例数据动手之前先把环境确认好。SQLite 版本直接决定你能用哪些高级特性窗口函数从 3.25.0 开始支持JSON 函数从 3.38.0 开始默认内置trigram 分词器从 3.34.0 开始可用。sqlite3 --version如果没有安装按系统选择安装方式。# macOS brew install sqlite # Ubuntu / Debian sudo apt update sudo apt install sqlite3 # Windows 可以使用 SQLite 官方命令行工具或直接使用 DB Browser for SQLiteDB Browser for SQLite 是免费开源的可视化工具界面支持简体中文适合查看表结构、执行 SQL、导入导出 CSV、查看执行计划。下载时到项目官网选择对应操作系统的安装包注意不要下载第三方改版。创建演示数据库sqlite3 demo.db进入 sqlite3 交互环境后先创建一张订单表后面所有示例都会在此基础上展开。CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date TEXT NOT NULL, amount_cents INTEGER NOT NULL ); INSERT INTO orders (customer_id, order_date, amount_cents) VALUES (1, 2024-11-01, 12000), (1, 2024-11-15, 8000), (2, 2024-11-02, 50000), (2, 2024-11-18, 30000), (3, 2024-11-05, 20000);2. 用窗口函数和 CTE 把复杂分析查询留在数据库里2.1 窗口函数分组内排名和累计值不再需要二次查询在没有窗口函数之前要实现“每个客户按时间倒序的第 1 单、第 2 单”通常要把数据查回应用层再用 for 循环分组排序。窗口函数可以直接在 SQL 里完成这个计算。SELECT customer_id, order_date, amount_cents, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY order_date DESC ) AS order_seq, SUM(amount_cents) OVER ( PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM orders ORDER BY customer_id, order_date;ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)的含义是按客户分组组内按日期倒序编号得到每个客户第 1 单、第 2 单的顺序。SUM(amount_cents) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)是从该客户最早一单累加到当前行得到累计金额。这里最关键的概念是“窗口”PARTITION BY决定分组范围ORDER BY决定组内顺序ROWS BETWEEN决定窗口边界。除了ROW_NUMBER还有RANK、DENSE_RANK、LAG、LEAD、FIRST_VALUE等函数用法类似。这样做的直接收益是减少应用层代码和数据传输量。分析逻辑收敛在 SQL 里测试时只需要准备数据并比对 SQL 输出不需要为排序逻辑单独写单元测试。2.2 递归 CTE处理树形和层级数据递归 CTE 是 SQLite 容易被人忽略的能力。它的典型场景是组织架构、分类树、评论楼中楼这类层级数据。CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER REFERENCES employees(id) ); INSERT INTO employees (id, name, manager_id) VALUES (1, 李总, NULL), (2, 张经理, 1), (3, 王组长, 2), (4, 赵开发, 3), (5, 刘开发, 3); WITH RECURSIVE org_tree AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, ot.depth 1 FROM employees e JOIN org_tree ot ON e.manager_id ot.id ) SELECT name, depth FROM org_tree ORDER BY depth, name;递归 CTE 由三部分组成锚点查询、递归查询和结束条件。锚点查询WHERE manager_id IS NULL找出顶级节点递归查询通过JOIN org_tree一层层向下展开当递归部分找不到新行时停止。实际项目中容易出现两个问题一是忘记写WHERE导致无限递归二是层级过深导致查询变慢。SQLite 默认递归上限是 1000超出会报错可以通过PRAGMA recursion_limit调整但一般不建议盲目调大应该先检查数据结构是否有环。2.3 和应用层处理方式对比把分析查询放在 SQLite 里和取数后在应用层计算相比至少有四个好处减少网络或进程间数据传输只把结果集带回应用层。SQL 可以直接复用数据库索引比应用层全量排序快。逻辑集中在数据库层多语言客户端可以共享同一套查询。用EXPLAIN QUERY PLAN可以直观看到执行路径方便调优。代价是复杂 SQL 的可读性下降。当一个查询超过三四十行时建议在查询头部写清楚业务目的并在代码仓库里保存可复现的测试数据。3. JSON 支持把 SQLite 当作轻量文档型数据库3.1 JSON 函数从哪来SQLite 很早就通过 JSON1 扩展提供 JSON 处理能力从 3.38.0 版本开始JSON 函数默认集成进核心不再需要手动开启扩展。常用函数包括json、json_extract、json_set、json_insert、json_remove、json_each、json_type。这套 JSON 能力让 SQLite 可以在同一张表里同时处理强类型字段和动态字段达到“关系型 文档型”混合的效果。3.2 用 JSON 存动态字段假设用户附带一组不固定的属性例如标签、渠道来源、活跃状态。把这些属性装进 JSON 字段可以避免频繁修改表结构。CREATE TABLE user_profiles ( id INTEGER PRIMARY KEY, profile TEXT NOT NULL ); INSERT INTO user_profiles (id, profile) VALUES (1, {name:张力,active:true,tags:[后端,SQLite]}), (2, {name:刘敏,active:false,tags:[前端]}); SELECT id, json_extract(profile, $.name) AS username, json_extract(profile, $.tags[0]) AS first_tag, json_type(profile, $.tags) AS tags_type FROM user_profiles;json_extract(profile, $.name)提取 JSON 对象中的name字段$.tags[0]表示取tags数组的第一个元素json_type返回字段类型例如array、object、text、integer。3.3 更新与索引更新 JSON 字段要用json_set它会把新的值写进 JSON 字符串的指定路径。UPDATE user_profiles SET profile json_set(profile, $.active, 1) WHERE id 2; SELECT id, json_extract(profile, $.active) AS active FROM user_profiles;注意 SQLite 没有独立布尔类型布尔值实际用整数 1 和 0 表示。在 JSON 函数中直接写true也能被识别为 JSON 字面量但为了和应用层类型保持一致建议统一用 1/0。JSON 字段也可以建表达式索引让常见查询走索引而不是全表扫描。CREATE INDEX idx_user_profile_active ON user_profiles(json_extract(profile, $.active));这里有一个容易被忽略的点如果表中大部分行都满足active 1优化器可能仍然选择全表扫描。表达式索引不是万能药要结合数据分布判断。还可以用json_each把 JSON 数组展开成多行这在统计标签、汇总数组字段时非常有用。SELECT up.id, je.value AS tag FROM user_profiles up, json_each(up.profile, $.tags) AS je WHERE up.id 1;3.4 关系型和 JSON 的取舍场景推荐方案原因字段会被频繁过滤、排序、JOIN单独建列可以使用普通索引类型约束清晰字段需要外键约束、非空约束单独建列数据库层强制完整性字段是稀疏属性很多行没有JSON 字段避免大量 NULL 列字段结构频繁变化JSON 字段减少 DDL 变更字段仅用于展示、低频过滤JSON 字段简单直接维护成本低4. FTS5 全文检索SQLite 自带的搜索能力4.1 创建虚拟表和写入数据FTS5 是 SQLite 的全文检索扩展它不是普通表而是一个虚拟表。它会把文本拆成 token建立倒排索引供MATCH查询使用。CREATE VIRTUAL TABLE articles_fts USING fts5( title, content, tokenize unicode61 ); INSERT INTO articles_fts (title, content) VALUES (SQLite 高级特性, SQLite 的 FTS5 支持全文检索、BM25 排序和前缀查询。), (Turso 与边缘数据库, Turso 使用 SQLite 衍生版本 libSQL 提供分布式的边缘数据库服务。);建立虚拟表后插入、更新、删除都走标准 SQL但真正的价值在MATCH查询。4.2 查询语法和排序SELECT title, bm25(articles_fts) AS score FROM articles_fts WHERE articles_fts MATCH SQLite OR Turso ORDER BY score;MATCH后面是 FTS5 查询语法支持AND、OR、NOT、短语查询、前缀查询。bm25()是相关性评分函数得分越低表示相关性越高所以用ORDER BY score升序排列。FTS5 还有一个实用特性是snippet()和highlight()可以生成搜索结果摘要和高亮片段避免在应用层手动截取文本。SELECT title, snippet(articles_fts, 1, [, ], ..., 12) AS snippet_text FROM articles_fts WHERE articles_fts MATCH SQLite;4.3 中文搜索的坑默认分词器不友好默认的unicode61分词器把连续汉字合并成整段 token。例如“SQLite 高级特性”会被切分成sqlite和高级特性两个 token搜索“高级”时可能匹配不到因为“高级”不是独立 token。从 SQLite 3.34.0 开始FTS5 提供了trigram分词器支持三字符以上的子串匹配。CREATE VIRTUAL TABLE articles_fts_trgm USING fts5( title, content, tokenize trigram );trigram对长度大于等于 3 的查询片段有效但两个字的中文词仍然不好处理。生产环境如果要支持
