SQLite 全文搜索：FTS5 虚拟表与 MATCH

LIKE 撑不住大数据量

如果你以前在 SQLite 里搜索过文本，大概用过 LIKE '%word%'。它在小表上能用，到了大表就不行了。没有任何索引能帮上忙：SQLite 必须扫描每一行，转成小写，再检查子字符串。单词边界、排序、多词查询和前缀匹配，全都得你自己实现。

FTS5 就是内置的解决方案。它是一种虚拟表类型，会为你的文本列维护倒排索引，支持一套小型查询语言，并用 BM25 给结果排序。它默认随 SQLite 一起提供，不需要安装扩展。

创建 FTS5 表

用 CREATE VIRTUAL TABLE ... USING fts5(...) 创建 FTS5 表，并列出要建立索引的文本列：

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

INSERT INTO posts (title, body) VALUES
  ('SQLite full-text search', 'FTS5 is fast and built in.'),
  ('Indexes in SQLite', 'B-trees power most lookups in SQLite.'),
  ('Why use SQLite', 'It runs in-process and stores data in one file.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

有三点值得注意。这些列没有类型：FTS5 把一切都当作文本。MATCH 运算符作用于表名（posts MATCH ...），而不是某一列。另外查询不区分大小写并且经过分词，所以 'sqlite' 能在任意一行中找到 SQLite。

MATCH 查询语言

MATCH 接受的不只是单个词。查询字符串有自己的一套小语法：

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Full-text search', 'FTS5 supports phrase and prefix queries.'),
  ('Indexes', 'A B-tree index speeds up equality lookups.'),
  ('Triggers', 'Use triggers to keep FTS in sync with the source table.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

各自的作用：

• 'fts5 AND prefix'：两个词都必须出现（顺序不限，位置不限）。
• '"keep fts"'：精确短语，顺序也必须一致。
• 'trig*'：前缀搜索，匹配 trigger、triggers、trigonometry 等。
• 'index NOT trigger'：包含 index，但不包含 trigger。

你还可以用 column:term 只搜索某一列，比如 'title:sqlite'。完整的语法还支持用括号分组、用 OR 表示“或”，和你对搜索引擎的预期一样。

用 BM25 排序

默认情况下，FTS5 会给每一行附加一个隐藏的 rank 列。它就是 BM25 相关度分数：数字越小，匹配度越高。按它排序，最相关的结果就会排在最前面：

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite tutorial', 'Learn SQLite from scratch.'),
  ('SQLite vs Postgres', 'Comparing SQLite to Postgres for small apps.'),
  ('SQLite SQLite SQLite', 'A post that mentions SQLite many times.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

想让某些列的权重比其他列更高？调用 bm25() 并传入权重，按声明顺序每列一个：

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite indexes', 'A short note about B-trees.'),
  ('Postgres notes', 'Includes a paragraph about sqlite for comparison.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

第一篇文章排在最前，因为 sqlite 出现在 title（权重 10×）中，而不只是在 body（权重 1×）中。按照你的应用真正想要的排序方式来选择权重。

让索引保持同步

最简单的 FTS5 表会自己存一份文本。对于只插入不修改的日志类数据，这没问题；但大多数应用已经有一张真实的表，希望 FTS 跟着它变化。干净的做法是使用“外部内容”FTS 表，再加上三个触发器。

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

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

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

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

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

INSERT INTO articles (title, body) VALUES
  ('SQLite triggers', 'How to use AFTER INSERT triggers.'),
  ('FTS5 patterns', 'External content keeps the data in one place.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' 告诉 FTS5 不要自己存储文本，需要时从 articles 表中读取。触发器把写入操作同步到 FTS 索引中。这样 articles 是唯一的数据来源，而 articles_fts 只是旁边的搜索结构。

看起来有点奇怪的 INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) 是 FTS5 的命令语法，用来通知索引删除某一行。

摘要与高亮

搜索结果通常需要一段预览，并突出显示匹配的词。FTS5 为此提供了两个函数：

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Getting started', 'SQLite is a small, fast, embedded database engine that stores data in a single file on disk.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close) 返回该列的完整文本，匹配的词元会被包裹起来。
• snippet(table, column_index, open, close, ellipsis, token_count) 返回以匹配处为中心的一小段摘录。

列索引按声明顺序从零开始。每个搜索界面都需要的“匹配词标黄”效果，就是用它们搭出来的。

值得了解的坑

有几件事经常让人中招：

• MATCH 只能用在 FTS 表上。 你不能对普通列使用 MATCH。如果需要在已有的表上搜索，就用上面的外部内容模式。
• 别忘了按 rank 排序。 不排序的话，FTS5 会按存储顺序返回行，和相关度毫无关系。
• 分词器很重要。 默认分词器（unicode61）按 Unicode 单词边界拆分并转为小写。如果需要词干提取（run 匹配 running），就用 porter 分词器：USING fts5(body, tokenize='porter')。
• FTS5 不能容忍拼写错误。 它做的是前缀匹配，不是模糊匹配。如果你需要“你是不是要找……”这样的功能，那属于 FTS5 之上的一层。
• 无内容表（content=''）更小，但会丢失信息。 你可以搜索它们，却取不回原始文本，只能拿到 rowid。适合文本存放在别处的情况。

下一篇：窗口函数

FTS5 解决的是文本搜索。下一页介绍另一种高级查询：窗口函数，它让你计算累计总和、排名和分组分析，而不必把行折叠成聚合结果。