SQLite FTS5 全文搜索如何建表、查询和优化？

SQLite FTS5 解决什么问题？

SQLite FTS5 是内置的全文搜索模块，适合在本地数据库里搜索文章、笔记、日志、商品名和离线文档。它和 LIKE '%关键词%' 不一样：LIKE 经常扫表，FTS5 会把文本拆成 token 并建立倒排索引，所以长文本检索更快。代价也明显，FTS 表会多占磁盘，写入时还要维护索引；如果只是按 id、状态、邮箱精确查询，普通索引更合适。

如何创建和查询 FTS 表？

sql
CREATE VIRTUAL TABLE articles_fts USING fts5(title, content);

INSERT INTO articles_fts(title, content)
VALUES ('SQLite Tutorial', 'SQLite is a lightweight database engine.');

SELECT rowid, title
FROM articles_fts
WHERE articles_fts MATCH 'SQLite';

短语搜索要加双引号，前缀搜索用 *，布尔搜索可以写 AND、OR、NOT。但不要把用户输入原样拼进 MATCH，特殊字符会触发语法错误，也可能让用户构造出你没预期的查询。实际项目通常会限制搜索语法，或者把普通关键词和高级搜索分开处理。

sql
SELECT rowid, title FROM articles_fts
WHERE articles_fts MATCH '"lightweight database"';

SELECT rowid, title FROM articles_fts
WHERE articles_fts MATCH 'data*';

外部内容表怎么同步？

更常见的做法是普通表保存真实数据，FTS 表只保存索引。这样业务字段、约束和状态仍然在主表里，搜索结果再通过 rowid 回表。

sql
CREATE TABLE articles(
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL
);
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, content, content='articles', content_rowid='id'
);
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

后续要用触发器同步插入、更新和删除。最容易踩坑的是只更新主表，忘了更新 FTS 索引，搜索结果就会出现旧内容或幽灵数据。

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

排名和优化怎么做？

FTS5 可以用 bm25() 排序，分数通常越小越相关。页面展示时可以用 snippet() 截取摘要，用 highlight() 标记命中词，但输出到前端前仍要考虑 XSS。

sql
SELECT a.id, a.title, bm25(articles_fts) AS rank
FROM articles_fts JOIN articles a ON a.id = articles_fts.rowid
WHERE articles_fts MATCH 'sqlite search'
ORDER BY rank
LIMIT 20;

INSERT INTO articles_fts(articles_fts) VALUES('optimize');

中文搜索是边界问题。SQLite 默认 tokenizer 更适合英文，中文没有天然空格，可能需要应用层分词后写入搜索字段。FTS5 适合单机、嵌入式、中小型搜索；如果要拼写纠错、同义词、多节点和复杂权限过滤，就该考虑专门搜索引擎。还有一个实际边界是备份和迁移：FTS 虚拟表可以重建，真正重要的是主表数据和建表 SQL。大库迁移时先导入主表，再执行 rebuild，通常比搬运一份已经膨胀的索引更稳。

追问

FTS5 和 LIKE 查询有什么区别？

FTS5 建倒排索引，适合长文本关键词检索；LIKE '%词%' 很容易扫表。取舍是 FTS5 写入更重，也会额外占空间。边界是短字段精确查询不必用 FTS。踩坑是把所有模糊查询都迁到 FTS，反而让简单查询变复杂。

外部内容表为什么要触发器？

外部内容表不会自动和主表同步。触发器能在插入、更新、删除时维护索引，避免搜索结果过期。取舍是同步自动化了，但写入路径更难调试。最常见的坑是更新时没先删除旧索引，导致命中重复或内容错位。

SQLite FTS5 能直接做好中文搜索吗？

能用，但默认效果通常不如英文。中文分词需要额外处理，可以在应用层分词后写入搜索字段。取舍是实现更复杂，但召回更稳定。边界是如果还要拼音、纠错、同义词，FTS5 会比较吃力。

bm25 分数越高越相关吗？

在 SQLite FTS5 里通常不是，bm25() 分数越小越相关。这个细节用反后，排序会把不相关内容排前面。取舍是 bm25 很轻量，但不懂业务热度和发布时间。实际项目常把 bm25 和业务权重一起计算。