SQLite 完全指南 / 11 - 全文搜索

11 - 全文搜索：FTS5、分词器、高亮与性能优化

11.1 FTS5 简介

FTS5（Full-Text Search 5）是 SQLite 内置的全文搜索引擎，支持中文、英文等多种语言的文本搜索。

11.1.1 创建 FTS5 表

-- 基本创建
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    content,
    author
);

-- 带配置选项
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    content,
    author,
    content='',              -- 非内容表模式（推荐）
    tokenize='unicode61'     -- 分词器
);

-- 插入数据
INSERT INTO articles_fts (title, content, author) VALUES
    ('SQLite 入门教程', '本文介绍 SQLite 的基本用法，包括创建表、插入数据和查询数据。', '张三'),
    ('Python 编程指南', 'Python 是一种简单易学的编程语言，适合初学者。', '李四'),
    ('数据库优化技巧', '本文介绍如何优化 SQL 查询性能，包括索引设计和查询计划分析。', '王五'),
    ('Web 开发实战', '使用 Flask 框架构建 Web 应用，结合 SQLite 数据库存储数据。', '赵六'),
    ('机器学习入门', '机器学习是人工智能的重要分支，使用 Python 和 scikit-learn 实现。', '孙七');

11.1.2 基本搜索

-- 全文搜索
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite';

-- 搜索多个词（AND 关系）
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite 教程';

-- 搜索短语
SELECT * FROM articles_fts WHERE articles_fts MATCH '"SQLite 入门"';

-- 指定列搜索
SELECT * FROM articles_fts WHERE articles_fts MATCH 'title:SQLite';
SELECT * FROM articles_fts WHERE articles_fts MATCH 'content:编程';

-- OR 搜索
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite OR Python';

-- NOT 搜索
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite NOT 教程';

-- 前缀搜索
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite*';

11.2 FTS5 查询语法

11.2.1 查询语法参考

语法	说明	示例
`term`	匹配单词	`SQLite`
`"phrase"`	匹配短语	`"SQLite 入门"`
`term1 OR term2`	OR 查询	`SQLite OR MySQL`
`term1 NOT term2`	排除	`SQLite NOT MySQL`
`term*`	前缀匹配	`Sear*`
`col:term`	列限定	`title:SQLite`
`NEAR(term1 term2, n)`	邻近查询	`NEAR(数据库优化, 10)`
`^term`	必须匹配	`^SQLite`

11.2.2 复杂查询示例

-- NEAR 邻近搜索（两个词在 10 个词以内）
SELECT * FROM articles_fts
WHERE articles_fts MATCH 'NEAR(数据库 优化, 10)';

-- 列限定 + 组合
SELECT * FROM articles_fts
WHERE articles_fts MATCH 'title:SQLite OR content:数据库';

-- 前缀搜索
SELECT * FROM articles_fts
WHERE articles_fts MATCH 'Sear* OR Py*';

-- 必须匹配词
SELECT * FROM articles_fts
WHERE articles_fts MATCH '^SQLite 教程';
-- SQLite 必须出现，教程 可选

11.3 排序与评分

11.3.1 BM25 排序

-- 按相关性排序（默认）
SELECT * FROM articles_fts
WHERE articles_fts MATCH 'SQLite OR 数据库'
ORDER BY rank;

-- rank 是 FTS5 内置的 BM25 分数
-- 分数越小越相关（负数）

-- 显式使用 bm25() 函数
SELECT *, bm25(articles_fts) AS score
FROM articles_fts
WHERE articles_fts MATCH 'SQLite OR 数据库'
ORDER BY score;

-- 调整列权重
SELECT *, bm25(articles_fts, 10.0, 5.0, 1.0) AS score
FROM articles_fts
WHERE articles_fts MATCH 'SQLite OR 数据库'
ORDER BY score;
-- 权重：title=10, content=5, author=1

11.3.2 自定义排序

-- 按特定列排序
SELECT * FROM articles_fts
WHERE articles_fts MATCH 'SQLite'
ORDER BY title ASC;

-- 使用 snippet 高亮显示
SELECT
    snippet(articles_fts, 0, '<b>', '</b>', '...', 10) AS title_snippet,
    snippet(articles_fts, 1, '<b>', '</b>', '...', 20) AS content_snippet,
    author
FROM articles_fts
WHERE articles_fts MATCH 'SQLite OR 数据库';

11.4 高亮与摘要

11.4.1 snippet（摘要）

-- snippet(table, col_index, start_tag, end_tag, ellipsis, max_tokens)
SELECT
    title,
    snippet(articles_fts, 1, '<b>', '</b>', '...', 10) AS content_preview
FROM articles_fts
WHERE articles_fts MATCH 'SQLite';

11.4.2 highlight（高亮）

-- highlight(table, col_index, start_tag, end_tag)
SELECT
    title,
    highlight(articles_fts, 1, '<mark>', '</mark>') AS content_highlighted
FROM articles_fts
WHERE articles_fts MATCH 'SQLite OR 数据库';

11.4.3 完整搜索接口示例

-- 带分页的搜索接口
SELECT
    rowid,
    title,
    highlight(articles_fts, 1, '<mark>', '</mark>') AS content,
    author,
    bm25(articles_fts) AS relevance
FROM articles_fts
WHERE articles_fts MATCH 'SQLite OR 数据库'
ORDER BY rank
LIMIT 10 OFFSET 0;

11.5 分词器（Tokenizer）

11.5.1 内置分词器

分词器	说明
`unicode61`	默认，基于 Unicode 6.1 分词
`ascii`	仅 ASCII 字符
`porter`	Porter 词干提取（英文）
`trigram`	三字符分组（适合模糊搜索）
`unicode61 remove_diacritics`	去除变音符号

-- 使用 unicode61（默认）
CREATE VIRTUAL TABLE t1 USING fts5(content, tokenize='unicode61');

-- 使用 porter 词干提取
CREATE VIRTUAL TABLE t2 USING fts5(content, tokenize='porter unicode61');

-- 使用 trigram（适合 LIKE 查询）
CREATE VIRTUAL TABLE t3 USING fts5(content, tokenize='trigram');

-- trigram 搜索
SELECT * FROM t3 WHERE content LIKE '%SQLite%';

11.5.2 中文分词

SQLite 的默认分词器对中文支持有限。推荐方案：

-- 方案 1：使用 ICU 分词器（需编译启用）
-- tokenize='icu zh_CN'

-- 方案 2：使用 simple_tokenizer 自定义分词（需扩展）
-- 方案 3：使用 jieba 等工具预分词后存储
-- 方案 4：使用 trigram（字符级，适合短文本）

-- 方案 3 示例：预分词
CREATE VIRTUAL TABLE articles_cn USING fts5(
    title,
    content,
    tokenize='unicode61'
);

-- 插入时用空格分隔中文词
INSERT INTO articles_cn VALUES
    ('SQLite 入门 教程', '本文 介绍 SQLite 的 基本 用法');

-- 搜索
SELECT * FROM articles_cn WHERE articles_cn MATCH '入门 教程';

11.5.3 自定义分词器（C API）

// C 语言自定义分词器示例框架
typedef struct MyTokenizer {
    sqlite3_tokenizer base;
    // 自定义字段
} MyTokenizer;

static int myCreate(int argc, const char *const *argv,
                    sqlite3_tokenizer **ppTokenizer) {
    // 创建分词器实例
}

static int myTokenize(sqlite3_tokenizer *pTokenizer,
                      const char *pText, int nText,
                      sqlite3_tokenizer_ctx *pCtx,
                      xTokenCallback xToken) {
    // 分词逻辑，对每个词调用 xToken()
}

static const sqlite3_tokenizer_module myTokenizerModule = {
    0,            // iVersion
    myCreate,     // xCreate
    myDestroy,    // xDestroy
    myTokenize    // xTokenize
};

11.6 FTS5 内容表（External Content）

11.6.1 问题：数据同步

默认的 FTS5 表会存储数据的副本——一份在原始表，一份在 FTS 表。

-- 方案 1：同步表（数据冗余）
CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, content TEXT);
CREATE VIRTUAL TABLE articles_fts USING fts5(title, content);

-- 需要手动同步
INSERT INTO articles VALUES (1, '标题', '内容');
INSERT INTO articles_fts VALUES ('标题', '内容');  -- 手动插入

-- 方案 2：内容表模式（推荐）
CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, content TEXT);
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, content,
    content='articles',      -- 指定内容表
    content_rowid='id'       -- 指定 rowid 列
);

-- 使用触发器自动同步
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, content)
    VALUES (new.id, new.title, new.content);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content)
    VALUES ('delete', old.id, old.title, old.content);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content)
    VALUES ('delete', old.id, old.title, old.content);
    INSERT INTO articles_fts(rowid, title, content)
    VALUES (new.id, new.title, new.content);
END;

11.6.2 content 表优势

特性	同步表	内容表
数据冗余	两份	一份
同步复杂度	手动	触发器自动
存储空间	大	小
更新操作	需同步两处	只需一处

11.7 FTS5 配置

11.7.1 常用配置

-- 设置列权重（用于 bm25 排序）
INSERT INTO articles_fts(articles_fts, rank) VALUES ('rank', 'bm25(10.0, 5.0, 1.0)');

-- 删除并重建 FTS 索引
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

-- 优化 FTS 索引（合并段）
INSERT INTO articles_fts(articles_fts) VALUES('optimize');

-- 增量合并
INSERT INTO articles_fts(articles_fts, rank) VALUES('merge', 500);

-- 删除所有数据
DELETE FROM articles_fts;
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

11.7.2 查看 FTS 索引结构

-- 查看段信息
INSERT INTO articles_fts(articles_fts, rank) VALUES('automerge', 4);
-- 设置自动合并的段数量

-- 查看 FTS 表的内部结构（调试用）
SELECT * FROM articles_fts_data;
SELECT * FROM articles_fts_idx;
SELECT * FROM articles_fts_content;
SELECT * FROM articles_fts_docsize;
SELECT * FROM articles_fts_config;

11.8 性能优化

11.8.1 索引优化

-- 1. 使用 content 表避免数据冗余
-- 2. 定期 optimize 合并段
INSERT INTO articles_fts(articles_fts) VALUES('optimize');

-- 3. 批量插入后 optimize
BEGIN;
INSERT INTO articles_fts VALUES ('...', '...', '...');
-- ... 大量插入
COMMIT;
INSERT INTO articles_fts(articles_fts) VALUES('optimize');

11.8.2 查询优化

-- ✅ 使用 MATCH 而非 LIKE
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite';
-- 比以下快很多：
SELECT * FROM articles_fts WHERE content LIKE '%SQLite%';

-- ✅ 使用列限定减少扫描范围
SELECT * FROM articles_fts WHERE articles_fts MATCH 'title:SQLite';

-- ✅ 使用 bm25 排序获取最相关结果
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite' ORDER BY rank LIMIT 10;

⚠️ 注意事项

FTS5 需要编译时启用——检查 PRAGMA compile_options 是否包含 ENABLE_FTS5
中文分词需要额外处理——默认分词器对中文按字符分词，效果有限
content 表模式需要手动同步——使用触发器确保一致性
FTS 表不支持部分更新——更新需要删除后重新插入
MATCH 查询不能与其他表 JOIN 使用——FTS 表的特殊语法限制

💡 技巧

content 表 + 触发器是生产环境的标准模式
trigram 分词器适合实现 LIKE '%keyword%' 的高效搜索
定期 optimize 可以合并 FTS 索引段，提升搜索性能
bm25() 函数实现了 BM25 排序算法，效果比简单关键词匹配好得多
snippet() 和 highlight() 可以直接在数据库层生成搜索摘要

📌 业务场景

场景一：文章搜索

CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, content TEXT, author TEXT);
CREATE VIRTUAL TABLE articles_fts USING fts5(title, content, content='articles', content_rowid='id');

-- 触发器自动同步
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, content) VALUES (new.id, new.title, new.content);
END;
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content) VALUES ('delete', old.id, old.title, old.content);
END;
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, content) VALUES ('delete', old.id, old.title, old.content);
    INSERT INTO articles_fts(rowid, title, content) VALUES (new.id, new.title, new.content);
END;

-- 搜索接口
SELECT a.id, a.title, a.author,
       highlight(articles_fts, 1, '<mark>', '</mark>') AS content,
       bm25(articles_fts) AS relevance
FROM articles_fts fts
JOIN articles a ON fts.rowid = a.id
WHERE articles_fts MATCH 'SQLite OR 数据库'
ORDER BY rank
LIMIT 20;

场景二：商品搜索

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, description TEXT, category TEXT);
CREATE VIRTUAL TABLE products_fts USING fts5(name, description, content='products', content_rowid='id');

-- 多维度搜索
SELECT p.*, highlight(products_fts, 0, '<b>', '</b>') AS name_hl
FROM products_fts
JOIN products p ON products_fts.rowid = p.id
WHERE products_fts MATCH 'name:无线 OR description:蓝牙'
AND p.category = '电子产品'
ORDER BY rank
LIMIT 20;

🔗 扩展阅读

📖 下一章：12 - JSON 支持 —— json 函数、索引、部分更新