SQLite FTS5 全文搜索引擎详解

FTS5（Full-Text Search 5）是 SQLite 内置的全文搜索扩展，让你在不依赖外部搜索引擎（Elasticsearch、Solr）的情况下，用纯 SQL 实现高性能的文本检索。它的 API 比前代 FTS3/FTS4 更清晰，性能更好，是 SQLite 全文搜索的首选方案。

#一、FTS5 是什么

简单说：FTS5 让你像查数据库一样搜索文本内容。

传统 LIKE ‘%关键词%’ 的问题：

• 每次查询都要扫描全表（O(n)）
• 无法对搜索结果排序（相关性）
• 不支持中文分词
• 不支持短语搜索

FTS5 的优势：

• 基于倒排索引，查询接近 O(1)
• 内置BM25 排序算法，按相关性排结果
• 支持短语搜索、前缀搜索、布尔运算
• 可自定义分词器（tokenize）处理中文等语言

#二、基本用法

#2.1 创建 FTS5 虚拟表

复制CREATE VIRTUAL TABLE articles USING fts5(
    title,
    content,
    author,
    tokenize='porter unicode61'
);

这行代码做了什么：

• 创建一个名为 articles 的 FTS5 虚拟表
• 有三个列：title、content、author
• 使用 porter（词干提取）+ unicode61（Unicode 分词）分词器

#2.2 插入数据

复制INSERT INTO articles(title, content, author) VALUES
('SQLite 入门', 'SQLite 是一个轻量级的关系型数据库', '张三'),
('FTS5 教程', 'FTS5 是 SQLite 的全文搜索扩展', '李四'),
('Python 数据库', 'Python 可以使用 sqlite3 模块操作 SQLite', '张三');

插入方式和普通表完全一样。FTS5 会自动建立倒排索引。

#2.3 基本搜索

复制-- 搜索包含"数据库"的文章
SELECT * FROM articles WHERE articles MATCH '数据库';

-- 搜索包含 SQLite 和轻量级的文章
SELECT * FROM articles WHERE articles MATCH 'SQLite 轻量级';

-- 带相关性评分的搜索
SELECT *, rank FROM articles WHERE articles MATCH '数据库' ORDER BY rank;

关键点：MATCH 是 FTS5 的专用操作符，不能用 LIKE。搜索结果默认按相关性排序（rank 值越小越相关）。

#三、搜索语法详解

#3.1 基本匹配

复制-- 单个词
WHERE articles MATCH 'SQLite'

-- 多个词（默认 OR 逻辑）
WHERE articles MATCH 'SQLite 数据库'

-- 显式 AND
WHERE articles MATCH 'SQLite AND 数据库'

-- 排除词
WHERE articles MATCH 'SQLite NOT Python'

-- 短语精确匹配
WHERE articles MATCH '"轻量级数据库"'

#3.2 布尔运算

复制-- AND：同时包含
WHERE articles MATCH 'SQLite AND FTS5'

-- OR：任一包含
WHERE articles MATCH 'SQLite OR 数据库'

-- NOT：排除
WHERE articles MATCH 'SQLite NOT Python'

-- 括号组合
WHERE articles MATCH '(SQLite OR 数据库) AND 入门'

#3.3 前缀搜索

复制-- 搜以 SQL 开头的词（匹配 SQLite、SQL 等）
WHERE articles MATCH 'SQL*'

-- 搜以"数据库"开头的词
WHERE articles MATCH '数据库*'

#3.4 列限定搜索

复制-- 只在 title 列中搜索
WHERE articles MATCH 'title:SQLite'

-- 在 title 或 content 中搜索
WHERE articles MATCH '{title OR content}:SQLite'

#3.5 短语搜索

复制-- 精确短语
WHERE articles MATCH '"轻量级的关系型数据库"'

-- 短语 + 前缀
WHERE articles MATCH '"SQLite 是"*'

#3.6 NEAR 搜索（近似匹配）

复制-- SQLite 和 Python 之间不超过 5 个词
WHERE articles MATCH 'NEAR(SQLite Python, 5)'

#四、分词器（Tokenizer）

分词器决定 FTS5 如何拆分文本。选对分词器是搜索准确性的关键。

#4.1 内置分词器

• unicode61：基于 Unicode 规则分词，按空白和标点分割。适用英文、欧洲语言
• porter：词干提取（running 变成 run）。适用英文搜索
• ascii：只按 ASCII 空白分割。纯英文
• simple：不做词干提取的简单分词

组合使用：tokenize=‘porter unicode61’ 表示先用 unicode61 分词，再用 porter 提取词干。

#4.2 中文分词

FTS5 原生不支持中文分词（中文没有空格分隔）。常见解决方案：

方案一：jieba 分词插件（推荐）

复制CREATE VIRTUAL TABLE articles USING fts5(
    title, content,
    tokenize='jieba'
);

方案二：应用层预分词

复制import jieba

title = "SQLite数据库入门"
tokens = " ".join(jieba.cut(title))
# tokens = "SQLite 数据库 入门"
cursor.execute(
    "INSERT INTO articles(title, content) VALUES (?, ?)",
    (tokens, content)
)

查询时也要用同样的分词器处理查询词。

方案三：字符级分词（无外部依赖）

复制CREATE VIRTUAL TABLE articles USING fts5(
    title, content,
    tokenize='unicode61'
);

每个中文字符会被当作独立 token。能搜到，但精度一般。

#五、内容表（Content Table）

FTS5 默认是一个独立的虚拟表——数据只存在于 FTS5 表中。但在实际项目中，你通常需要把 FTS5 索引和普通表的数据分开存储。

#5.1 什么是内容表

复制-- 普通数据表
CREATE TABLE articles_data(
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT,
    author TEXT,
    created_at TEXT
);

-- FTS5 索引表，指向数据表
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, content, author,
    content='articles_data',
    content_rowid='id'
);

这样 articles_data 存储原始数据，articles_fts 只存储倒排索引。

#5.2 同步数据

复制-- 插入：同时插入数据表和 FTS 索引
BEGIN;
INSERT INTO articles_data(title, content, author) VALUES('标题', '内容', '作者');
INSERT INTO articles_fts(rowid, title, content, author)
VALUES(last_insert_rowid(), '标题', '内容', '作者');
COMMIT;

-- 删除：同时删除
BEGIN;
DELETE FROM articles_fts WHERE rowid = 1;
DELETE FROM articles_data WHERE id = 1;
COMMIT;

-- 更新：FTS5 不支持直接 UPDATE，需要先删后插
BEGIN;
DELETE FROM articles_fts WHERE rowid = 1;
INSERT INTO articles_fts(rowid, title, content, author)
VALUES(1, '新标题', '新内容', '新作者');
UPDATE articles_data SET title='新标题', content='新内容' WHERE id = 1;
COMMIT;

#5.3 自动同步（触发器）

复制CREATE TRIGGER articles_ai AFTER INSERT ON articles_data BEGIN
    INSERT INTO articles_fts(rowid, title, content, author)
    VALUES(new.id, new.title, new.content, new.author);
END;

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

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

#六、BM25 排序与自定义评分

#6.1 默认 BM25 排序

复制SELECT *, rank FROM articles WHERE articles MATCH 'SQLite' ORDER BY rank;

rank 值越小（越负）表示越相关。

#6.2 自定义权重

复制-- 给 title 列 10 倍权重，content 列 1 倍，author 列 5 倍
SELECT *, rank FROM articles WHERE articles MATCH 'SQLite'
ORDER BY rank(articles, 0, 10, 1, 5);

#6.3 bm25() 函数

复制-- bm25() 返回正数，越大越相关
SELECT *, bm25(articles) AS score
FROM articles
WHERE articles MATCH 'SQLite'
ORDER BY score DESC;

#6.4 逐列评分

复制-- 只看 title 的相关性
SELECT *, bm25(articles, 1, 0, 0) AS title_score
FROM articles
WHERE articles MATCH 'SQLite'
ORDER BY title_score DESC;

#七、高级功能

#7.1 snippet() 短语高亮

复制SELECT snippet(articles_fts, '<b>', '</b>', '...', 10, 30) AS highlight
FROM articles_fts
WHERE articles_fts MATCH 'SQLite';

-- 结果示例："...<b>SQLite</b> 是一个轻量级的..."

参数含义：表名、开始标签、结束标签、省略符、最大 token 数、最大上下文 token 数。

#7.2 highlight() 列高亮

复制-- 高亮指定列中的匹配词
SELECT highlight(articles_fts, 0, '<b>', '</b>') AS title_highlight
FROM articles_fts
WHERE articles_fts MATCH 'SQLite';

#7.3 merge 优化

频繁插入会产生大量小 segment，影响查询性能。手动触发 merge：

复制-- 自动合并
INSERT INTO articles_fts(articles_fts) VALUES('optimize');

-- 指定合并数量
INSERT INTO articles_fts(articles_fts) VALUES('optimize=100');

#八、性能优化

1. 合理使用内容表：FTS5 只存索引，原始数据用普通表
2. 只索引需要搜索的列：不需要搜索的列不要放进 FTS5
3. 批量插入：在事务中批量插入比逐条快 10 倍以上
4. 定期优化：每插入 1000 条后执行一次 optimize
5. 用触发器自动同步：避免手动维护索引一致性

复制-- 批量插入示例
BEGIN;
INSERT INTO articles_fts VALUES('标题1', '内容1');
INSERT INTO articles_fts VALUES('标题2', '内容2');
-- ...
INSERT INTO articles_fts VALUES('标题100', '内容100');
COMMIT;

-- 优化
INSERT INTO articles_fts(articles_fts) VALUES('optimize');

#九、与 FTS3/FTS4 的对比

• 创建语法：FTS3/FTS4 用 USING fts3()，FTS5 用 USING fts5()
• 自定义排序：FTS3/FTS4 需要外部计算，FTS5 内置 rank 和 bm25()
• 列权重：FTS3/FTS4 不支持，FTS5 用 rank(table, w1, w2, …)
• 并发写入：FTS5 比 FTS3/FTS4 更好
• 错误处理：FTS5 有更清晰的错误信息
• 扩展性：FTS5 支持自定义分词器插件

结论：新项目直接用 FTS5，不需要考虑 FTS3/FTS4。

#十、完整示例：博客搜索系统

#10.1 建表

复制-- 数据表
CREATE TABLE posts(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT NOT NULL,
    tags TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    view_count INTEGER DEFAULT 0
);

-- FTS5 索引表
CREATE VIRTUAL TABLE posts_fts USING fts5(
    title, content, author, tags,
    content='posts',
    content_rowid='id',
    tokenize='porter unicode61'
);

-- 自动同步触发器
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
    INSERT INTO posts_fts(rowid, title, content, author, tags)
    VALUES(new.id, new.title, new.content, new.author, new.tags);
END;

CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, content, author, tags)
    VALUES('delete', old.id, old.title, old.content, old.author, old.tags);
END;

CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, content, author, tags)
    VALUES('delete', old.id, old.title, old.content, old.author, old.tags);
    INSERT INTO posts_fts(rowid, title, content, author, tags)
    VALUES(new.id, new.title, new.content, new.author, new.tags);
END;

#10.2 搜索查询

复制-- 基本搜索：返回文章 + 摘要 + 评分
SELECT
    p.id,
    p.title,
    snippet(posts_fts, 1, '<b>', '</b>', '...', 32) AS excerpt,
    bm25(posts_fts, 1, 2, 1, 1) AS score,
    p.author,
    p.created_at,
    p.view_count
FROM posts_fts
JOIN posts p ON p.id = posts_fts.rowid
WHERE posts_fts MATCH 'SQLite 数据库'
ORDER BY score DESC
LIMIT 20;

#10.3 带筛选条件的搜索

复制SELECT p.*, snippet(posts_fts, 1, '<b>', '</b>', '...', 32) AS excerpt
FROM posts_fts
JOIN posts p ON p.id = posts_fts.rowid
WHERE posts_fts MATCH 'FTS5'
  AND p.author = '张三'
  AND p.created_at >= '2024-01-01'
ORDER BY bm25(posts_fts) DESC;

#十一、常见陷阱

#11.1 FTS5 表不支持 ALTER TABLE

FTS5 虚拟表不支持 ALTER TABLE ADD COLUMN。要添加列，需要备份数据、删除旧表、重建。

#11.2 搜索词太短

FTS5 默认忽略 1-2 个字符的 token。搜中文单字可能返回空结果。解决方案：调整分词器或使用前缀搜索 ‘库*’。

#11.3 特殊字符

搜索词中包含引号、括号、*、AND、OR、NOT 等关键词时需要转义：

复制WHERE articles MATCH '"双引号内容"'
WHERE articles MATCH '"AND"'

#11.4 事务中的部分回滚

FTS5 的操作是原子的，但事务中插入大量数据后回滚可能导致索引与数据不一致。建议用内容表模式 + 触发器同步。

#十二、适用场景

• 博客/文章搜索：非常适合
• 商品搜索（名称+描述）：适合
• 日志搜索：适合
• 代码搜索：需要合适分词器
• 实时搜索建议：需要配合前缀搜索
• 超大数据集（>1亿条）：考虑 Elasticsearch
• 多语言搜索：需要自定义分词器

#总结

FTS5 是 SQLite 最强大的扩展之一。核心要点：

1. 建表：CREATE VIRTUAL TABLE … USING fts5(…)
2. 搜索：WHERE table MATCH ‘query’
3. 排序：ORDER BY rank 或 ORDER BY bm25(table, weights)
4. 高亮：snippet() 和 highlight() 提取匹配片段
5. 内容表：FTS5 只存索引，原始数据用普通表
6. 同步：用触发器自动维护索引一致性

它不能替代 Elasticsearch，但对于中小型项目（百万级数据以内），FTS5 提供了一个零依赖、零运维、性能足够的全文搜索方案。

参考文档：

• SQLite 官方 FTS5 文档