数据库基础体系 · 第 115/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQLite JSON、FTS5 与扩展：虚拟表、Tokenizer 和安全加载

SQLite 的 JSON、FTS5 和扩展机制经常一起出现在应用数据库中：

• JSON 用于保存结构不固定的属性；
• FTS5 用于全文检索；
• 虚拟表为这类功能提供 SQL 表接口；
• Tokenizer 决定全文索引如何切分文本；
• 扩展加载让 SQLite 能够加入自定义函数、虚拟表和 Tokenizer，但也因此引入了本地代码执行风险。

这些概念并不处于同一层。JSON 主要是函数和操作符，FTS5 是基于虚拟表的全文索引模块，扩展是 SQLite 的可加载或静态注册代码，而 Tokenizer 是 FTS5 内部负责文本切词的组件。理解它们之间的边界，比记住若干 SQL 语法更重要。

---

一、先区分 SQLite 的几种“表”

1. 普通表

普通表由 SQLite 的 B-tree 存储。表中的每一行通常通过 rowid 或显式的 INTEGER PRIMARY KEY 定位，索引也是 B-tree。

例如：

CREATE TABLE article (
    id      INTEGER PRIMARY KEY,
    title   TEXT NOT NULL,
    body    TEXT NOT NULL
);

对 title 建立普通索引后，索引适合处理：

WHERE title = ?
WHERE title >= ? AND title < ?
ORDER BY title

但普通 B-tree 不会自然地解决“正文中包含多个词，并按相关性排序”的问题。对任意子串执行：

WHERE body LIKE '%database%'

通常不能利用前缀 B-tree 索引，因为通配符出现在搜索词之前。

2. 虚拟表

虚拟表（virtual table）是一个 SQL 表接口，但它的存储和访问逻辑由一个模块实现，不一定对应 SQLite 自己管理的普通 B-tree 表。

CREATE VIRTUAL TABLE article_fts USING fts5(
    title,
    body
);

这里的 article_fts 看起来像一张表，但查询和维护由 FTS5 模块处理。

虚拟表模块通过 C API 注册。其核心生命周期大致如下：

1. sqlite3_create_module() 注册模块名称和回调；
2. CREATE VIRTUAL TABLE ... USING module(...) 时调用 xCreate 或 xConnect；
3. 查询规划阶段调用 xBestIndex；
4. 执行查询时创建游标，调用 xFilter；
5. 通过 xEof、xNext 逐行推进；
6. 通过 xColumn 取列值，通过 xRowid 取行号；
7. 如果支持写入，还会调用 xUpdate；
8. 事务边界可能触发 xBegin、xSync、xCommit、xRollback。

因此，虚拟表不是“普通表加了一个特殊索引”，而是 SQLite 查询引擎与外部模块之间的一套访问协议。

EXPLAIN QUERY PLAN 中经常能看到类似：

SCAN article_fts VIRTUAL TABLE INDEX 0:M1

这表示查询规划器把条件交给了虚拟表模块。M1 等细节是模块与 SQLite 查询规划器之间的内部约定，不应当被应用程序当作稳定格式解析。

3. 表值函数与虚拟表接口

JSON1 提供的 json_each() 和 json_tree() 可以放在 FROM 子句中：

SELECT *
FROM json_each('{"a": 1, "b": 2}');

它们表现为表值函数，并且内部采用了与虚拟表相近的表访问抽象，但它们不是通过用户执行：

CREATE VIRTUAL TABLE ... USING json_each

创建出来的持久表。它们是每次查询根据 JSON 输入产生行。

这一区分很重要：

• FTS5 虚拟表通常有持久化的索引状态；
• json_each() 和 json_tree() 是查询期间展开 JSON；
• 前者可以接受 MATCH 约束并利用全文索引；
• 后者主要是逐行遍历 JSON 值，不会自动把 JSON 路径查询变成普通 B-tree 索引查找。

---

二、SQLite 中的 JSON：值、路径和存储类

1. JSON 不是 SQLite 的独立存储类

SQLite 的基本存储类是：

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

JSON1 不会新增一个名为 JSON 的原生存储类。下面的 payload 仍然是 TEXT：

CREATE TABLE event (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL
);

可以用约束保证它是合法 JSON：

CREATE TABLE event (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL
        CHECK (json_valid(payload))
);

在 STRICT 表中也一样：

CREATE TABLE strict_event (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL
        CHECK (json_valid(payload))
) STRICT;

STRICT 约束的是 SQLite 存储类与列声明类型之间的转换规则；它不会把 TEXT 列变成具有 JSON 类型系统的列。CHECK (json_valid(payload)) 解决的是另一个问题：内容是否能够被 JSON 解析。

这两个约束不能互相替代：

• STRICT 不能保证一段文本是合法 JSON；
• json_valid() 不能保证列中一定以期望的 SQLite 存储类保存。

2. JSON 路径

SQLite JSON 路径从 $ 开始：

$.user.name
$.items[0].price
$.items[#-1]

例如：

SELECT
    json_extract('{"user":{"name":"Ada"}}', '$.user.name');

结果是 SQL 的文本值：

Ada

数组和对象通常以 JSON 文本形式返回：

SELECT json_extract('{"items":[1,2,3]}', '$.items');

结果类似：

[1,2,3]

json_extract() 在 SQLite 中有一个容易与其他数据库混淆的行为：如果只提取一个路径，并且该路径对应 JSON 字符串、数字、布尔值或 JSON null，它通常返回对应的 SQLite 标量，而不是总返回 JSON 文本。

例如：

SELECT
    typeof(json_extract('{"n": 10}', '$.n')),
    typeof(json_extract('{"s": "x"}', '$.s')),
    typeof(json_extract('{"b": true}', '$.b')),
    typeof(json_extract('{"x": null}', '$.x'));

结果分别对应：

integer
text
integer
null

JSON 的 true 和 false 会映射为 SQLite 的整数 1 和 0。

如果应用需要保留 JSON 表示，可以使用 json()、多路径提取，或者根据接口语义选择 -> 与 ->>。

SELECT
    '{"n":10}' -> '$.n'  AS json_value,
    '{"n":10}' ->> '$.n' AS sql_value;

可以把它们理解为：

• -> 更接近返回 JSON 表示；
• ->> 更接近返回 SQL 标量。

这些操作符和相关行为应以目标 SQLite 版本的 JSON 文档为准。部署环境如果混用旧版 SQLite，不能只根据开发机上的语法测试作结论。

3. json_each() 与 json_tree()

json_each() 展开一个 JSON 对象的直接成员或数组的直接元素：

SELECT key, value, type
FROM json_each('{"a":1,"b":"x","c":true}');

可能得到：

a | 1 | integer
b | x | text
c | 1 | true

json_tree() 则递归遍历整个 JSON：

SELECT
    fullkey,
    type,
    atom
FROM json_tree('{"user":{"name":"Ada"},"tags":["sql","json"]}')
WHERE atom IS NOT NULL;

关键列包括：

• key：对象成员名或数组下标；
• value：当前节点的 JSON 或 SQL 表示；
• type：节点类型；
• atom：叶节点对应的 SQL 标量；
• path：当前容器路径；
• fullkey：当前节点完整路径。

对表中的 JSON 列使用它们时，通常是相关展开：

CREATE TABLE document (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL CHECK (json_valid(payload))
);

SELECT
    d.id,
    j.fullkey,
    j.atom
FROM document AS d,
     json_tree(d.payload) AS j
WHERE j.atom IS NOT NULL;

这里发生的是：

1. SQLite 读取 document 的一行；
2. 把这一行的 payload 作为 json_tree() 输入；
3. JSON 树产生多行；
4. 外层查询过滤叶节点。

这不是“对 JSON 建了索引”。如果每次都需要递归展开大量 JSON，成本仍然与被展开的数据量有关。

4. JSON 索引的正确边界

如果查询固定访问一个路径，应把路径提取为生成列或建立表达式索引。

表达式索引

CREATE INDEX document_category_idx
ON document(json_extract(payload, '$.category'));

查询中的表达式必须与索引表达式保持足够一致：

SELECT id
FROM document
WHERE json_extract(payload, '$.category') = 'database';

生成列

CREATE TABLE article (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL CHECK (json_valid(payload)),
    category TEXT
        GENERATED ALWAYS AS (
            json_extract(payload, '$.category')
        ) STORED
) STRICT;

CREATE INDEX article_category_idx
ON article(category);

插入：

INSERT INTO article(payload)
VALUES ('{"category":"database","title":"SQLite"}');

查询：

SELECT id, category
FROM article
WHERE category = 'database';

这里的因果链是：

1. 插入或更新 payload；
2. SQLite 计算生成列 category；
3. 普通 B-tree 索引保存 category；
4. 查询使用普通索引，而不是重新遍历整个 JSON。

生成列适合稳定、频繁查询的路径。路径不固定、字段高度动态时，直接使用 JSON 函数或 JSON 展开更自然。

5. JSONB 的边界

较新的 SQLite 版本提供 JSONB 相关能力，用 SQLite 自己的内部二进制表示保存 JSON 解析结果。它不是 PostgreSQL JSONB 的兼容实现，也不意味着所有路径查询都变成 O(1) 索引查找。

应区分三个概念：

• JSON 文本：可读的 JSON 字符串；
• SQLite JSONB：SQLite 定义的内部二进制表示；
• JSON 路径索引：针对特定字段建立的普通索引。

JSONB 主要可以减少反复解析和表示转换，但应用不应依赖其内部布局，也不应把任意 BLOB 都当成可移植的 JSONB。跨版本、跨产品或跨语言传输时，JSON 文本通常更适合作为边界格式。

---

三、FTS5：不是“给文本列加普通索引”

1. FTS5 的基本模型

FTS5 是 SQLite 的全文搜索模块。它把文本拆成 Token，建立反向索引，使查询能够从搜索词定位到匹配文档。

可以创建一张独立内容表：

CREATE VIRTUAL TABLE article_fts USING fts5(
    title,
    body
);

插入：

INSERT INTO article_fts(rowid, title, body)
VALUES
    (1, 'SQLite JSON', 'SQLite can query JSON values.'),
    (2, 'SQLite FTS5', 'FTS5 provides full text search.');

查询：

SELECT rowid, title, body
FROM article_fts
WHERE article_fts MATCH 'SQLite';

也可以使用 MATCH 运算符：

SELECT rowid, title
FROM article_fts
WHERE article_fts MATCH 'JSON OR FTS5';

FTS5 的查询不是普通 SQL 字符串比较。右侧内容会由 FTS5 查询语法解析器解释。

2. FTS5 的 rowid

FTS5 虚拟表有一个与普通表类似的 rowid 概念。它用于标识被索引的文档，也是 FTS5 查询结果与业务表关联的关键。

SELECT a.id, a.title
FROM article AS a
JOIN article_fts AS f
  ON f.rowid = a.id
WHERE article_fts MATCH 'SQLite';

如果业务表使用：

id INTEGER PRIMARY KEY

那么 id 同时是普通表的 rowid 别名，适合与 FTS5 的 rowid 对齐。

FTS5 的 rowid 不应被理解为“全文索引自动知道业务主键”。如果业务主键是任意文本或 UUID，通常仍需建立一个稳定的整数 rowid 映射。

3. FTS5 的 MATCH 与普通 WHERE 的区别

下面两个条件不是同一类操作：

WHERE body LIKE '%sqlite%'

WHERE article_fts MATCH 'sqlite'

前者是普通字符串模式匹配，后者是全文查询：

• FTS5 会先解析查询表达式；
• 按 Tokenizer 规则切分搜索词；
• 查找包含这些 Token 的文档；
• 可以支持短语、列限定、布尔组合、前缀等能力；
• 某些情况下还能提供 BM25 相关性排序。

例如：

SELECT
    rowid,
    title,
    bm25(article_fts) AS score
FROM article_fts
WHERE article_fts MATCH 'SQLite NEAR/5 JSON'
ORDER BY score;

SQLite FTS5 的 bm25() 分数通常是负数，分数越小表示相关性越高。不要套用其他全文引擎“分数越大越相关”的习惯。

4. FTS5 的查询输入存在双重解析

应用代码应该绑定参数：

SELECT rowid, title
FROM article_fts
WHERE article_fts MATCH ?1;

但是，绑定参数只防止了 SQL 字符串拼接注入；它不会让参数内容失去 FTS5 查询语义。

例如用户输入：

sqlite OR json

作为 MATCH 参数后，FTS5 仍会把它解释为两个词的 OR 查询。

因此要区分两种需求：

需求 A：允许用户使用全文查询语法

直接绑定参数即可：

sqlite OR json
"full text"
title : database

此时应向用户明确查询语法，并处理 FTS5 解析错误。

需求 B：把用户输入当作普通字面文本

需要在应用层根据 FTS5 查询语法对词项进行引用或转义。不能简单地认为“使用参数绑定后就安全且语义正确”。

如果输入：

foo"bar

应用还必须正确处理引号和 Token 边界，否则可能出现：

• SQLITE_ERROR；
• 搜索结果与用户直觉不符；
• 用户输入中的 OR、NEAR 等词被当成操作符。

这属于查询语言注入或语义注入问题，而不是传统 SQL 注入。

---

四、Tokenizer：FTS5 如何把文本变成可搜索的词

1. Tokenizer 的定义

Tokenizer 是 FTS5 负责分词的组件。它把输入文本变成一系列 Token，每个 Token 通常带有：

• 文本内容；
• 在原文中的起始位置；
• 在原文中的结束位置；
• 某些场景下的偏移信息。

建立索引时，FTS5 对文档执行 Tokenizer；查询时，也对 MATCH 右侧的查询词执行相同或兼容的 Tokenizer。只有二者产生能够对应的 Token，才可能匹配。

因此，Tokenizer 不是显示层的“高亮工具”，而是索引语义的一部分。

2. unicode61

常见配置是：

CREATE VIRTUAL TABLE article_fts USING fts5(
    title,
    body,
    tokenize = 'unicode61 remove_diacritics 2'
);

unicode61 是 FTS5 内置 Tokenizer，名称来自其 Unicode 字符分类处理方式。remove_diacritics 控制重音符号处理。例如在合适的语言文本中，去除重音可能使：

café

与：

cafe

更容易匹配。

但这不等于中文分词。对中文、日文等没有空格分隔的语言，Unicode 字符分类和空格分词通常不能替代词法分析。一个汉字串可能被视为连续 Token，或者产生并不符合业务词语边界的结果。选择 FTS5 并不自动获得面向中文的搜索效果。

3. porter

porter 是一种词干提取 Tokenizer，主要面向英语。典型配置：

CREATE VIRTUAL TABLE english_fts USING fts5(
    content,
    tokenize = 'porter unicode61'
);

它通常将不同词形归并到相近词干，例如复数或时态变化可能被视为同类词。

但词干提取不是词典分词，也不是自然语言理解：

• 它不理解领域术语；
• 不适合直接用于中文；
• 可能把本应区分的英语词形合并；
• 变更 Tokenizer 后，原索引通常需要重建。

4. trigram

对于子串匹配，可以使用 trigram Tokenizer：

CREATE VIRTUAL TABLE code_fts USING fts5(
    code,
    tokenize = 'trigram'
);

它按三个字符的窗口生成 Token，因此适合某些任意子串搜索场景。代价是：

• 索引可能更大；
• 写入成本可能更高；
• 短于三个字符的查询有特殊边界；
• 它仍然不是语言分词器。

如果需求是“查找任意字符子串”，trigram 可能比默认 Tokenizer 更接近目标；如果需求是“按词搜索”，则应选择词法边界明确的 Tokenizer。

5. Tokenizer 改变后必须考虑索引重建

Tokenizer 定义了索引键。假设一段文本原来被切成：

["database", "system"]

换用另一种 Tokenizer 后可能变成：

["data", "base", "system"]

旧索引保存的倒排键仍然是旧结果。仅修改应用查询代码不能修复已有索引。

重建示例：

INSERT INTO article_fts(article_fts) VALUES('rebuild');

这要求 FTS5 表使用了能够重建内容的配置，尤其是有内容表或内部内容副本的情况。生产环境中应把 Tokenizer 配置视为索引格式的一部分，并在升级时明确重建策略。

6. 自定义 Tokenizer

自定义 Tokenizer 需要实现 FTS5 Tokenizer 接口，通常包括：

• 创建 Tokenizer 实例；
• 初始化一个文本输入；
• 逐个输出 Token；
• 释放实例；
• 释放 Tokenizer 对象。

FTS5 的扩展 API 可以取得 fts5_api，然后注册自定义 Tokenizer。典型流程是：

1. 扩展代码被加载或静态注册；
2. 扩展找到当前数据库连接上的 FTS5 API；
3. 调用 xCreateTokenizer() 注册名称；
4. SQL 中通过 tokenize = 'custom_name ...' 使用；
5. 创建或重建 FTS5 表时，FTS5 调用该 Tokenizer。

自定义 Tokenizer 的难点不只在切词，还包括：

• 索引和查询必须使用一致的规范化规则；
• 字符偏移必须与高亮、snippet 的预期一致；
• 版本升级时 Tokenizer 语义不能悄悄改变；
• 多个连接必须都注册该 Tokenizer；
• 数据库打开时若模块不存在，相关虚拟表可能无法连接或重建。

一个数据库文件可以持久化 FTS5 的索引数据，但不会把自定义 Tokenizer 的机器码一起持久化。因此，“数据库文件可复制”不代表“接收该文件的环境一定可查询”。

---

五、FTS5 的内容模式与一致性

FTS5 常见的三种内容组织方式有明显不同。

1. 普通 FTS5 表：内部保存内容

CREATE VIRTUAL TABLE article_fts USING fts5(
    title,
    body
);

插入 FTS5 表时，文本和索引都由 FTS5 管理：

INSERT INTO article_fts(rowid, title, body)
VALUES (1, '标题', '正文');

优点是使用简单。缺点是业务表和 FTS5 表可能重复保存正文。

2. 外部内容表：FTS5 只保存索引

CREATE TABLE article (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body  TEXT NOT NULL
);

CREATE VIRTUAL TABLE article_fts USING fts5(
    title,
    body,
    content = 'article',
    content_rowid = 'id'
);

此时：

• article 保存真实文本；
• article_fts 保存全文索引；
• FTS5 查询命中 rowid 后，可以从 article 读取列内容。

需要用触发器保持同步：

CREATE TRIGGER article_ai AFTER INSERT ON article BEGIN
    INSERT INTO article_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER article_ad AFTER DELETE ON article BEGIN
    INSERT INTO article_fts(article_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER article_au AFTER UPDATE ON article BEGIN
    INSERT INTO article_fts(article_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);

    INSERT INTO article_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

对于已有数据，创建 FTS5 表之后要初始化索引：

INSERT INTO article_fts(rowid, title, body)
SELECT id, title, body
FROM article;

或者使用适合该配置的 rebuild 操作。

外部内容模式最重要的风险是“一致性分裂”。如果业务表已经写入，但 FTS5 索引没有同步，查询可能出现：

• FTS5 找不到刚插入的文档；
• FTS5 命中了已删除的 rowid；
• FTS5 命中后从内容表读取不到对应内容；
• 更新后仍返回旧词项。

将业务表更新和 FTS5 更新放在同一个 SQLite 事务中，能够使它们原子提交或原子回滚。触发器通常正是为了把同步动作绑定到同一个写事务。

如果程序使用多个连接并发写入，SQLite 的写事务仍然需要遵守其锁和事务规则。WAL 模式可以改善读写并发，但不会让 SQLite 变成多写者数据库，也不会自动修复外部内容索引的不一致。

3. 无内容表

无内容模式只保存索引信息，不保存原文。这适合原文在别处存储、或只关心 rowid 的场景，但会影响：

• highlight()；
• snippet()；
• 通过 FTS5 列读取原文；
• 删除和更新时提供旧 Token 信息的方式。

因此，不能只因为“少存一份文本”就默认选择无内容模式。需要先确认查询结果是否需要原文和高亮。

---

六、一个可运行的 JSON + FTS5 示例

下面示例在 SQLite 命令行中运行。它假定目标 SQLite 已编译 FTS5 和 JSON 支持。可以先检查版本与编译选项：

SELECT sqlite_version();

PRAGMA compile_options;

不同发行版可能把 JSON 功能内置，也可能通过编译选项控制；FTS5 是否可用则取决于具体构建。不要仅凭系统中存在 sqlite3 命令就假定所有模块都启用。

1. 创建业务表

PRAGMA foreign_keys = ON;

CREATE TABLE article (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL CHECK (json_valid(payload)),

    title TEXT GENERATED ALWAYS AS (
        json_extract(payload, '$.title')
    ) STORED,

    body TEXT GENERATED ALWAYS AS (
        json_extract(payload, '$.body')
    ) STORED,

    category TEXT GENERATED ALWAYS AS (
        json_extract(payload, '$.category')
    ) STORED
) STRICT;

CREATE INDEX article_category_idx
ON article(category);

这里有三个生成列：

• title 从 $.title 提取；
• body 从 $.body 提取；
• category 用于普通筛选和索引。

payload 仍然是 JSON 文本；生成列把稳定访问路径投影为普通 SQL 列。

2. 创建外部内容 FTS5 表

CREATE VIRTUAL TABLE article_fts USING fts5(
    title,
    body,
    content = 'article',
    content_rowid = 'id',
    tokenize = 'unicode61 remove_diacritics 2'
);

content = 'article' 表示内容来源是 article 表；content_rowid = 'id' 表示 FTS5 的 rowid 对应 article.id。

3. 建立同步触发器

CREATE TRIGGER article_ai AFTER INSERT ON article BEGIN
    INSERT INTO article_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER article_ad AFTER DELETE ON article BEGIN
    INSERT INTO article_fts(article_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER article_au AFTER UPDATE ON article BEGIN
    INSERT INTO article_fts(article_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);

    INSERT INTO article_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

更新时先删除旧索引项，再插入新索引项。顺序不能反过来，否则旧 Token 可能继续存在。

4. 插入数据并查询

BEGIN;

INSERT INTO article(payload)
VALUES
('{
  "title": "SQLite JSON",
  "body": "SQLite can query JSON values and build indexes.",
  "category": "database"
}'),
('{
  "title": "SQLite FTS5",
  "body": "FTS5 provides full text search with tokenizers.",
  "category": "database"
}'),
('{
  "title": "Deployment",
  "body": "A native extension must be loaded carefully.",
  "category": "security"
}');

COMMIT;

按 JSON 提取列筛选：

SELECT id, title, category
FROM article
WHERE category = 'database';

这条语句可以使用 article_category_idx，因为过滤条件直接作用于生成列。

按全文检索：

SELECT
    a.id,
    a.title,
    bm25(article_fts) AS score
FROM article_fts
JOIN article AS a
  ON a.id = article_fts.rowid
WHERE article_fts MATCH ?
ORDER BY score;

绑定参数为：

SQLite

预期会命中前两篇文章。

按 JSON 条件和全文条件同时查询：

SELECT
    a.id,
    a.title,
    a.category,
    bm25(article_fts) AS score
FROM article_fts
JOIN article AS a
  ON a.id = article_fts.rowid
WHERE article_fts MATCH ?
  AND a.category = ?
ORDER BY score;

绑定参数：

第一个参数：SQLite
第二个参数：database

执行过程可以理解为：

1. FTS5 根据 MATCH 找到包含 SQLite Token 的 rowid；
2. 通过 rowid 与 article 连接；
3. 普通 B-tree 索引筛选 category = 'database'，具体执行顺序由查询规划器决定；
4. FTS5 提供相关性分数；
5. 最后排序并返回业务列。

不要假设 SQLite 一定先执行 JSON 条件或一定先执行全文条件。实际计划应通过：

EXPLAIN QUERY PLAN
SELECT
    a.id,
    a.title
FROM article_fts
JOIN article AS a
  ON a.id = article_fts.rowid
WHERE article_fts MATCH 'SQLite'
  AND a.category = 'database';

以及真实数据上的测量来判断。

---

七、FTS5 的故障路径与诊断

1. FTS5 不可用

执行：

CREATE VIRTUAL TABLE t USING fts5(content);

如果目标构建没有 FTS5，常见表现是类似：

no such module: fts5

这不是 SQL 语法问题，而是 SQLite 构建能力或部署包的问题。应检查：

PRAGMA compile_options;

并在应用启动时执行一条最小能力探测，而不是等到用户第一次搜索时才发现。

2. Tokenizer 不存在

如果数据库声明：

tokenize = 'my_tokenizer'

但当前连接没有注册该 Tokenizer，创建或连接虚拟表可能失败，错误通常会指出 Tokenizer 不存在或无法初始化。

常见原因包括：

• 扩展没有加载；
• 只在一个数据库连接上注册，其他连接没有注册；
• 动态库版本不匹配；
• 扩展初始化函数失败；
• 应用重启后忘记重新注册；
• 数据库文件依赖了部署环境没有的自定义模块。

数据库文件本身不会携带 Tokenizer 实现，因此部署检查必须包括“代码模块可用性”。

3. 外部内容索引不一致

可以用对照查询检查业务表行数与 FTS5 行数：

SELECT
    (SELECT count(*) FROM article) AS content_count,
    (SELECT count(*) FROM article_fts) AS fts_count;

这只能发现部分问题。更可靠的检查还包括抽样比较：

SELECT a.id
FROM article AS a
LEFT JOIN article_fts AS f
  ON f.rowid = a.id
WHERE f.rowid IS NULL;

如果确认外部内容表是权威来源，可以在维护窗口重建索引：

INSERT INTO article_fts(article_fts) VALUES('rebuild');

重建期间会产生读写成本，是否能够在线执行取决于数据库大小、事务模式和业务并发。重建前应先备份并在目标版本上验证。

4. 删除后的旧结果

外部内容表删除时，如果没有执行 FTS5 的删除操作，全文索引中可能仍有旧 Token。查询可能先命中该 rowid，再因为内容表已经没有对应行而表现为结果缺失或连接失败。

这类问题的重点不是“FTS5 查询不可靠”，而是索引维护协议被破坏。

---

八、扩展：SQLite 如何加入原生能力

1. 扩展能做什么

SQLite 扩展通常是实现 SQLite C API 的本地代码，可以注册：

• 标量函数；
• 聚合函数；
• 窗口函数；
• 排序规则；
• 虚拟表模块；
• FTS5 自定义 Tokenizer；
• 其他与数据库连接关联的能力。

动态扩展通常导出类似下面的初始化函数：

#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_example_init(
    sqlite3 *db,
    char **pzErrMsg,
    const sqlite3_api_routines *pApi
){
    SQLITE_EXTENSION_INIT2(pApi);

    /* 在这里注册函数、模块或 tokenizer */
    return SQLITE_OK;
}

具体符号名还会受文件名、入口参数和加载方式影响。工程上不应只依赖“文件名一定能推导出入口名”的假设；需要明确指定入口或按照目标平台规则验证。

2. 动态加载的两道开关

SQLite 为动态扩展提供 C API 和 SQL 函数接口。二者不要混为一谈。

C API

sqlite3_load_extension(db, "./myext", 0, &err);

SQL 函数

SELECT load_extension('./myext');

动态扩展加载默认不是应用应当无条件开启的能力。C 代码可以使用：

sqlite3_enable_load_extension(db, 1);

这会启用扩展加载能力，并涉及 SQL load_extension() 接口。

如果应用只需要通过受控的 C API 加载，而不希望任意 SQL 文本调用 load_extension()，可以使用更细粒度的数据库配置接口：

int on = 1;
sqlite3_db_config(
    db,
    SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION,
    on,
    0
);

根据 SQLite 官方接口语义，这个配置项用于启用 C 接口加载能力，而不会同时开放 SQL load_extension() 函数。应用加载完成后，还应关闭相应能力。

不同版本的头文件和库必须匹配。调用前要检查返回值：

char *err = NULL;
int rc = sqlite3_load_extension(db, path, entry, &err);

if (rc != SQLITE_OK) {
    fprintf(stderr, "load extension failed: %s\n",
            err ? err : sqlite3_errmsg(db));
    sqlite3_free(err);
}

sqlite3_load_extension() 返回成功，只表示动态库和初始化入口成功执行，不表示扩展提供的每一个函数都满足应用预期。

3. 命令行工具中的 .load

SQLite CLI 支持：

.load ./myext

这适合本地验证，但不应直接当作生产部署策略。CLI 的当前目录、动态库搜索路径和进程权限可能与应用服务完全不同。

验证一个扩展至少应检查：

SELECT my_function('input');

或者：

CREATE VIRTUAL TABLE test_vtab USING my_module(...);

还要测试：

• 新连接是否仍然需要重新加载；
• 事务中是否能正常使用；
• 错误输入是否返回 SQLite 错误而不是崩溃；
• 关闭数据库连接时是否正确释放资源。

4. 静态注册与动态加载的取舍

如果扩展与应用一起编译，可以采用静态注册方式，例如通过 sqlite3_auto_extension() 让每个新连接自动执行扩展初始化。

静态注册减少了：

• 动态库路径问题；
• 运行时任意路径加载风险；
• 平台 ABI 不匹配问题；
• 连接创建后忘记加载的问题。

但它会增加主程序体积，并把扩展生命周期绑定到应用构建流程。对于受控嵌入式产品，静态链接通常比开放动态加载更容易审计。

---

九、安全加载：扩展不是普通配置文件

1. 为什么动态扩展风险高

SQLite 动态扩展是本地机器码。加载一个恶意或被替换的扩展，通常意味着攻击者可以在进程权限下执行任意代码。

风险来源包括：

• 数据库文件中保存了恶意的加载路径或 Tokenizer 名称；
• 应用把用户输入直接传给 load_extension()；
• 扩展目录可被低权限用户写入；
• 动态库依赖搜索路径被劫持；
• 应用打开了 SQL load_extension() 后执行了不可信 SQL；
• 数据库连接复用时，先前代码留下了扩展加载开关。

因此，“扩展加载失败”是功能故障；“扩展加载成功但来源不可信”是安全故障。

2. 推荐的加载边界

一个较清晰的流程是：

1. 应用启动；
2. 打开数据库连接；
3. 在可信代码中启用 C API 扩展加载；
4. 从固定目录加载固定文件；
5. 校验版本、签名或哈希；
6. 检查初始化返回值；
7. 运行能力探测；
8. 立即关闭扩展加载能力；
9. 再把连接交给普通 SQL 业务代码。

示意代码：

int on = 1;
int rc = sqlite3_db_config(
    db,
    SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION,
    on,
    0
);
if (rc != SQLITE_OK) {
    /* 配置失败，不能继续依赖扩展 */
}

/* path 不来自用户输入，而是经过固定映射和校验 */
rc = sqlite3_load_extension(
    db,
    "/opt/myapp/lib/libfts_tokenizer.so",
    NULL,
    &err
);

/* 检查 rc 和 err，必要时中止启动 */

on = 0;
sqlite3_db_config(
    db,
    SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION,
    on,
    0
);

这里的关键不是“把路径写死”这一种形式，而是扩展来源必须由受信任的部署系统决定，而不能由数据库内容或请求参数决定。

3. trusted_schema 与数据库对象

SQLite 还提供 PRAGMA trusted_schema 及对应的数据库配置能力，用于限制某些数据库模式对象调用非安全函数。它解决的是模式对象信任边界问题，不等价于动态扩展加载开关。

需要区分：

• trusted_schema：是否信任数据库 schema 中定义的触发器、视图、生成列等对象调用相关函数；
• ENABLE_LOAD_EXTENSION：当前连接是否允许加载本地扩展；
• 扩展函数的 SQLITE_INNOCUOUS、SQLITE_DIRECTONLY 等属性：函数在受信任上下文中的可调用范围。

关闭扩展加载不能替代 schema 安全策略；设置 trusted_schema 也不能把恶意动态库变成安全代码。它们处于不同层次。

4. 数据库文件不应决定代码加载

一个危险设计是：

1. 打开任意数据库文件；
2. 读取其中的 Tokenizer 名称或扩展路径；
3. 根据数据库内容加载动态库；
4. 再创建其中声明的虚拟表。

这相当于让数据文件选择进程要执行的代码。

更安全的方式是：

• 应用先根据自身配置加载所需模块；
• 再打开或验证数据库；
• 只允许白名单模块名；
• 不允许数据库内容提供任意文件路径；
• 对数据库中的虚拟表声明做结构审核；
• 在沙箱、低权限用户和受限文件系统下运行不可信数据库。

---

十、扩展、虚拟表和 Tokenizer 的生命周期差异

这三个对象的生命周期不能混为一体。

1. 扩展生命周期

动态库通常在进程中被加载，初始化函数接收一个 sqlite3* 连接并注册能力。具体动态库的卸载时机由运行时平台和 SQLite 内部实现管理，扩展不能简单假设“某个连接关闭后代码立即卸载”。

扩展注册的全局状态如果没有正确同步，可能导致多个数据库连接并发使用时出现问题。

2. 虚拟表生命周期

CREATE VIRTUAL TABLE 只是在数据库 schema 中记录虚拟表定义，并让当前连接找到对应模块。数据库重新打开时，SQLite 还需要再次找到该模块，调用 xConnect 连接该虚拟表。

因此：

• schema 中有虚拟表定义，不代表模块代码始终存在；
• 删除扩展或不加载扩展后，数据库可能无法正常连接；
• 多线程或连接池中的每个连接都必须满足模块注册要求。

3. Tokenizer 生命周期

Tokenizer 的注册通常与某个数据库连接上的 FTS5 API 关联。自定义 Tokenizer 的参数、实例和输入文本有各自释放责任。

当 FTS5 扫描数据时，大致发生：

原文
  ↓
Tokenizer 实例
  ↓
Token 序列
  ↓
倒排索引或查询词
  ↓
匹配 rowid

如果索引时和查询时使用的规范化规则不同，表现通常不是明确报错，而是“应该命中却没有结果”。这类问题比加载失败更难发现，必须用固定测试语料验证：

• 大小写；
• 重音；
• 标点；
• 连字符；
• 中文和英文混排；
• emoji 或非 BMP 字符；
• 前缀和短词；
• 高亮偏移。

---

十一、JSON 与 FTS5 的组合边界

JSON 字段可以作为全文索引的输入，但不能直接把任意 JSON 文本当作高质量自然语言文本。

1. 对固定字段建立生成列，再送入 FTS5

前面的示例已经把：

{
  "title": "...",
  "body": "..."
}

投影为 title 和 body。这样做的好处是：

• JSON 路径明确；
• FTS5 不需要每次查询时解析 JSON；
• 内容表中的列可直接作为外部内容；
• 可以独立检查字段类型和缺失值。

如果 title 可能不是字符串，应显式约束或在生成表达式中处理，否则全文索引输入可能出现类型转换差异。

2. 不要把 JSON 键名、结构符号当成业务文本

直接对整个 JSON 文本建立 FTS5 索引会把引号、键名、结构内容和字符串值混在一起。即使 Tokenizer 能够处理，它也未必符合用户对“搜索文档正文”的理解。

例如：

{"status":"closed","body":"database error"}

用户搜索 status 时，是否应该命中？这不是 Tokenizer 能替你决定的问题，而是索引字段设计问题。

更明确的设计是：

CREATE VIRTUAL TABLE article_fts USING fts5(
    title,
    body,
    category UNINDEXED,
    content = 'article',
    content_rowid = 'id'
);

其中 UNINDEXED 列可以随结果保存或读取，但不会参与全文索引。分类筛选仍由普通列和普通索引负责。

3. 动态 JSON 数组与全文索引

如果标签保存在：

{"tags":["sqlite","json","database"]}

可以用 json_each() 查询：

SELECT a.id
FROM article AS a, json_each(a.payload, '$.tags') AS tag
WHERE tag.value = 'json';

但这不是高效的全文索引方案。若标签查询是核心路径，可以：

• 建立规范化的 article_tag(article_id, tag) 表和普通索引；
• 将标签拼接成明确的 FTS5 字段；
• 根据查询模式建立生成列或维护专门索引。

选择哪一种取决于标签是否需要：

• 精确相等；
• 前缀搜索；
• 相关性排序；
• 多词全文匹配；
• 保持重复值和顺序。

---

十二、性能边界：JSON、B-tree 与 FTS5 各自解决什么问题

1. JSON 函数不是自动索引

WHERE json_extract(payload, '$.category') = 'database'

只有在存在匹配的表达式索引或生成列索引时，才可能避免对大量行逐行解析和比较。

json_each() 和 json_tree() 更不能被当成“JSON 全字段索引”。它们的主要作用是把结构化值转换为关系行。

2. FTS5 不是通用排序索引

FTS5 擅长：

• Token 匹配；
• 短语；
• 前缀；
• NEAR；
• 相关性排序。

它不自动替代：

• 等值索引；
• 范围索引；
• 外键索引；
• ORDER BY created_at；
• 唯一性约束。

典型查询需要把 FTS5 与普通表连接：

SELECT a.id, a.title
FROM article_fts AS f
JOIN article AS a ON a.id = f.rowid
WHERE f MATCH 'sqlite'
  AND a.category = 'database'
  AND a.id > ?
ORDER BY a.id
LIMIT 50;

FTS5 负责词项到 rowid 的检索，普通 B-tree 负责业务过滤或排序。最终性能取决于选择性、数据分布、连接方式和结果集大小，而不是单独取决于是否使用了 FTS5。

3. 写入成本与事务成本

维护 JSON 生成列、普通索引、FTS5 索引和同步触发器时，一次业务写入可能产生多份工作：

更新 payload
  ↓
重新计算生成列
  ↓
维护 category B-tree
  ↓
触发 FTS5 删除旧 Token
  ↓
触发 FTS5 插入新 Token

这些操作若处于同一个事务中，原子性较好，但事务持续时间也可能增加。批量导入时，通常需要在受控事务中批量写入，并在真实数据上测量索引构建、锁持有和 WAL 增长。

---

十三、生产中最容易混淆的结论

误解一：STRICT 表提供 JSON 类型安全

不正确。STRICT 约束 SQLite 存储类，CHECK (json_valid(...)) 才检查 JSON 语法。业务还可能需要检查路径类型：

CHECK (
    json_type(payload, '$.category') = 'text'
)

但这仍然只是约束类型和结构的一部分，不会自动验证完整业务 schema。

误解二：参数绑定可以直接解决 FTS5 查询注入

不完整。参数绑定解决 SQL 层拼接问题，但 MATCH 参数仍会被 FTS5 查询语法解析。允许高级语法还是只允许字面搜索，必须在应用层明确选择。

误解三：FTS5 Tokenizer 会自动做好中文搜索

不正确。内置 Tokenizer 的 Unicode 处理不等于中文词典分词。中文搜索的词边界、同义词、拼音、繁简转换等需求，可能需要专门 Tokenizer 或应用层预处理。

误解四：数据库文件包含了自定义 Tokenizer

不正确。文件保存索引数据和虚拟表配置，不保存执行 Tokenizer 所需的本地代码。恢复数据库时必须同时恢复模块、版本和注册过程。

误解五：关闭 SQL load_extension() 就完全没有扩展风险

不正确。应用仍可能通过 C API 加载扩展，或者扩展已经被静态注册。安全边界需要同时考虑：

• 哪些代码能调用加载 API；
• 动态库从哪里来；
• 进程以什么权限运行；
• 数据库 schema 是否可信；
• 扩展注册的函数和虚拟表是否安全。

误解六：FTS5 的 shadow table 可以直接维护

不应这样做。FTS5 可能使用 shadow tables 保存内部索引状态，但这些表属于模块实现细节。直接修改它们可能破坏索引格式、事务语义和未来版本兼容性。应使用 FTS5 的公开 SQL 操作、正常写入方式或 rebuild。

---

十四、部署前的验证路径

一个同时使用 JSON、FTS5 和扩展的应用，至少应在目标部署环境执行以下验证。

能力验证

SELECT sqlite_version();

SELECT json_valid('{"ok":true}');

CREATE VIRTUAL TABLE __fts5_test USING fts5(x);

INSERT INTO __fts5_test(rowid, x)
VALUES (1, 'SQLite FTS5 test');

SELECT rowid
FROM __fts5_test
WHERE __fts5_test MATCH 'FTS5';

DROP TABLE __fts5_test;

这分别验证：

• SQLite 版本；
• JSON 函数是否可用；
• FTS5 模块是否可用；
• FTS5 是否能建立索引并查询。

外部内容一致性验证

在测试事务中执行插入、更新、删除：

BEGIN;

INSERT INTO article(payload)
VALUES ('{"title":"one","body":"alpha","category":"test"}');

SELECT count(*)
FROM article_fts
WHERE article_fts MATCH 'alpha';

UPDATE article
SET payload = '{"title":"one","body":"beta","category":"test"}'
WHERE title = 'one';

SELECT count(*)
FROM article_fts
WHERE article_fts MATCH 'alpha';

SELECT count(*)
FROM article_fts
WHERE article_fts MATCH 'beta';

DELETE FROM article
WHERE title = 'one';

SELECT count(*)
FROM article_fts
WHERE article_fts MATCH 'beta';

ROLLBACK;

预期行为是：

1. 插入后 alpha 可命中；
2. 更新后 alpha 不再命中，beta 可命中；
3. 删除后 beta 不再命中；
4. 回滚后业务表和全文索引都恢复到事务前状态。

扩展安全验证

验证内容应包括：

• 未加载扩展时，依赖的函数或 Tokenizer 是否按预期失败；
• 受信任路径加载后，功能是否可用；
• 关闭加载开关后，普通 SQL 是否不能再次加载任意库；
• 错误的 ABI、入口函数和文件权限是否能被清晰诊断；
• 连接池创建新连接后，扩展注册是否仍然存在；
• 恶意数据库文件是否能够诱导应用加载非白名单库。

SQLite 的可嵌入性使它非常适合把 JSON、全文索引和自定义能力放进一个进程，但也要求应用明确划分三条边界：

1. JSON 是数据表示和查询函数，不是自动索引；
2. FTS5 是基于虚拟表和 Tokenizer 的全文索引模块，不是普通 B-tree；
3. 扩展是执行进程本地代码的机制，数据库内容不应未经验证地决定它加载什么代码。

把这三条边界保持清楚，才能在需要灵活结构、全文搜索和原生扩展时，同时保留可验证的查询语义、事务一致性和部署安全性。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：SQLite 类型系统与 STRICT 表：亲和性、存储类、约束和兼容
• 下一篇：SQLite Backup API 与在线复制：快照、一致性、锁和恢复验证
• 延伸：SQLite 查询与索引：类型亲和性、查询计划、FTS5 和性能边界

官方资料

• SQLite Documentation
• SQLite Architecture

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。