【SQLite】给本地笔记加全文搜索Python FTS5 实战解决中文搜不到的问题记得自己写过“数据库索引优化”却想不起放在哪篇笔记里。按文件名搜索没有结果逐个打开 Markdown 又太慢。给正文加一个搜索入口其实用 Python 自带的 sqlite3 就能开始。但第一版很容易卡在一个意外的地方数据明明写进数据库了英文能搜到中文词却搜不到。下面从这个现象出发做一个可以索引本地 Markdown、返回文件路径和命中摘录的小工具再用实际输出解释分词、短词、更新和耗时之间的关系。1. 同一句中文为什么查不到先把问题缩到只有一行文本的实验。建一张默认 FTS5 表插入“数据库索引优化”再搜索“索引优化”。本次环境是 Python 3.13.1、SQLite 3.45.3结果为零。这里没有文件编码错误也没有事务没提交的问题换成完整的“数据库索引优化”它可以匹配。原因可以用词项表直接观察。FTS5 的 unicode61 默认按 Unicode 字符类别和分隔符识别连续片段。它理解 Unicode 字符却不具备现代汉语词语切分能力。在这段不含空格的中文里整句成为了一个词项。搜索引擎建立索引时收录的单位和你在输入框里敲下的单位并不一致。图里展示的是本次实验结果词项表只有“数据库索引优化”没有独立的“索引”或“优化”。判断中文搜索问题时查词项比反复改查询语句更有效。换排序算法、调结果数量、给数据库加普通索引都不会补上根本不存在的词项。换成 trigram 后同一句文本会产生连续三个字符组成的片段例如“数据库”“据库索”“库索引”“索引优”“引优化”。于是四字子串“索引优化”能够命中。但“索引”只有两个字不足以形成一个三字符片段MATCH 仍然返回零。本次两种查询、两种分词器的交叉实验都保存在素材包里。这里选择 trigram是因为笔记搜索经常知道原文中的几个字适合按子串找文件。它不会理解“慢查询”和“执行很慢”的语义关系也没有解决拼音和同义词。要实现这些能力可以再引入中文分词扩展或语义召回这个小工具先把字面搜索的行为做清楚。2. 先跑通一个能返回摘录的例子下面的程序只用标准库在内存中建表不会修改本地笔记。保存为 quickstart.py 后运行预期看到“数据库笔记”和带方括号的命中片段。如果出现 no such module: fts5说明当前 Python 链接的 SQLite 没启用该扩展需要检查运行环境Python 版本号不能代替 SQLite 的功能检测。importsqlite3 dbsqlite3.connect(:memory:)db.execute(CREATE VIRTUAL TABLE notes USING fts5(title, body, tokenizetrigram))db.executemany(INSERT INTO notes VALUES(?, ?),[(数据库笔记,数据库索引优化先查看执行计划。),(接口笔记,请求超时后需要设置重试上限。),])query索引优化phrasequery.replace(,)fortitle,excerptindb.execute(SELECT title, snippet(notes,1,[,],...,24) FROM notes WHERE notes MATCH ? ORDER BY bm25(notes,5.0,1.0) LIMIT ?,(phrase,10),):print(title)print(excerpt)db.close()本机实际输出如下方括号标出的部分来自 snippet 函数数据库笔记 数据库[索引优化]先查看执行计划。这几行代码分工很明确虚拟表负责分词与倒排索引MATCH 负责检索snippet 负责截取命中附近的正文bm25 负责排序。这里标题权重为五、正文权重为一是为了让标题匹配更容易排在前面。它是一个起点参数不能保证所有笔记都符合预期应拿常用搜索词检查排序。FTS5 的 BM25 排序方向也要留意分数更小通常代表更相关示例采用升序。它不是相似度百分比不能把负数解释成“搜索失败”也不能跨不同索引直接比较数值。标题较短、正文较长、词出现频率不同都会影响得分。把 query 替换成一个包含英文 OR 的字符串时本例仍把整个输入当作字面短语。这是故意收窄的产品行为输入框提供“搜索原文”不让普通用户无意中触发全文查询语法。高级布尔搜索可以单独提供入口和字面模式分别测试。3. 从笔记文件到可搜索的索引完整版 search_notes.py 有两个子命令index 接收笔记目录search 接收查询词。可以把文件放进 notes 文件夹用下面的命令操作。Windows 上若 python 指向应用商店占位程序可以使用 py -3其他平台使用自己的 Python 解释器即可。python search_notes.py--dbnotes.db index ./notes python search_notes.py--dbnotes.db search索引优化python search_notes.py--dbnotes.db search索引--limit5索引保存三列相对路径、标题和正文。路径标记为 UNINDEXED只用于找到原文件不参与内容匹配标题取第一行非空文本去掉开头的标题符号正文保留原始文本。这个规则足够处理演示笔记但如果文件带 YAML 元信息正式版本应该单独解析标题避免把元信息当作标题展示。程序只读 UTF-8 编码的 .md 和 .txt单文件上限为一 MiB跳过符号链接和解析后位于指定目录外的文件。超过限制或读取失败会明确报错旧索引保留。这样比悄悄漏掉一批文件更容易排查。上限属于示例的资源约束大文件处理可以改成流式解析与分段索引。图中的原文件与派生数据库是两类资产。原文件仍由编辑器管理notes.db 是可重建的搜索副本。指定 --db 时应该使用这个工具专属的数据库文件index 会替换其中 notes 表的内容它不是把新的目录追加到已有索引里。想管理多个目录可以每个目录一个索引或给表增加来源字段后再实现增量更新。为了让代码保持可读当前版本先读取全部文件再用一个写事务替换索引。优点是文件读取失败不会破坏原有索引删除文件也会在下一次重建时自然消失代价是内存和重建时间随总文本量增长。几百篇笔记可以先使用这个版本数据变大后再按路径、内容哈希和修改时间更新。4. 两字词和特殊符号怎么处理全文索引没有覆盖所有输入。程序把去除首尾空格后不足三个字符的查询交给 instr在标题或正文里做字面子串扫描。这样“索引”可以返回刚才那篇笔记“%”也只会搜索百分号本身。返回结果中会标明 short-scan便于调用方知道走的是较慢路径。这里没有用 LIKE 兜底是为了避免通配符语义“%”在 LIKE 中会匹配任意长度“_”会匹配一个字符处理不好会让一次字面查询变成大量误命中。instr 的行为更直观但不能依靠 trigram 索引加速。限制 LIMIT 只能减少返回行数无法保证扫描成本两字词很多时要考虑专门分词而不是无限加大扫描预算。1至2字符3至200字符空串或过长用户输入关键词去空格后检查长度instr 字面子串扫描封装短语绑定 MATCH按 BM25 升序排序提示调整输入返回路径、标题和摘录长度达到三字符的输入会先把双引号写成两份再整体包成一个 FTS 短语然后通过 SQL 参数传入。这里其实有两层边界SQL 参数绑定负责避免把数据当作 SQL 结构FTS 短语封装负责避免把用户输入当作 AND、OR、列过滤等全文查询表达式。只做第一层语法错误或搜索含义意外改变仍可能出现。这个示例的短词路径使用 instr英文大小写敏感默认 trigram 查询的大小写行为则不同。它们在中文搜索中不明显但混合英文时必须写进产品说明。要统一行为应设计一致的规范化策略并保留用于展示的原文不能临时给查询词 lower 一下就假设所有语言问题都解决了。代码还拒绝空串和超过两百字符的输入把结果数量限制在一到五十。对个人命令行工具这能挡住误操作如果后续开放为服务还需要查询超时、并发限制和用户权限。不要把本地笔记数据库直接放到公开下载目录数据库本身保存了正文副本。5. 两万条记录索引究竟省在哪里为了让“快”有具体含义本次生成两万条合成记录总 UTF-8 文本约十 MB。每隔一千条放入“数据库索引优化”因此“索引优化”恰好命中二十条。普通表建立了正文的 B-tree 索引再分别执行 LIKE ‘%索引优化%’ 和 trigram MATCH 的 count 查询避免结果传输量影响比较。本次 LIKE 的中位数为 43.8369 mstrigram MATCH 为 0.0379 ms两者均命中 20 条对应样本 P95 分别为 49.3267 ms 和 0.04 ms。构建 trigram 索引并插入记录约耗时 0.774 秒。这是同一进程、内存数据库、先预热三次再计时二十五次的结果。它测的是热缓存下、较低命中率的字面查询不包含解析 Markdown、启动 Python、磁盘冷读、结果高亮和接口网络开销。数据是程序生成的重复短文本不是用户真实笔记不能拿这组差距承诺所有搜索都会等比例变快。为什么普通索引没有发挥预期效果B-tree 按键值顺序组织数据很适合等值与某些前缀范围查询前面带百分号没有一个固定的开头来缩小范围。本次 EXPLAIN QUERY PLAN 对普通表返回 SCAN plain。trigram 的计划则包含 VIRTUAL TABLE INDEX 和 MATCH 约束标记交给虚拟表索引处理。注意虚拟表计划里同样可能出现 SCAN 一词不能只搜索这个单词就判定“全表扫描”。要连同表类型、索引描述和实际耗时一起看执行计划展示格式也不适合作为长期稳定的程序接口。确认返回数量一致之后再比较访问方式才不会把语义不相同的两个查询拿来做宣传。代价也存在索引要在写入时构造三字符窗口会增加词项和位置信息初次建库时间与存储开销都比只保存正文更高。想测试自己的场景应把脚本里的记录换成真实文本再加入短词、高频词、无结果词和长句分开观察建库、更新与检索不能只留一条最漂亮的数据。6. 改了笔记为什么搜索还是旧内容搜索结果来自数据库副本文件保存不会自动同步。演示程序用 index 手动刷新先完成读取再在事务里删除旧索引行并批量插入新内容。提交成功后新的连接能读到刷新后的结果如果写入失败则回滚。事务保证的是索引替换的一致性不保证扫描期间每个文件都来自同一时刻。SQLite 派生索引索引程序Markdown 文件SQLite 派生索引索引程序Markdown 文件任意读取失败保持旧索引alt[全部成功][写入失败]枚举允许范围内的文件完整读取 UTF-8 文本开始写事务删除旧行并批量写入新行提交回滚本次回归测试修改了 database.md将“索引优化”替换为“事务隔离测试”刷新后搜索旧词不再命中。接着删除 python.md再刷新“Python”也不再返回。很多工具只测试首次导入和查询漏掉更新与删除最后搜索框就成为过期信息的入口。改成增量同步时可以保存文件相对路径、大小、修改时间和内容哈希。修改时间适合快速筛选候选变更内容哈希用来确认正文是否真的变化文件重命名和删除需要单独检测。不要只遍历“现在存在的文件”否则数据库里的旧路径会永久残留。FTS5 还有 external-content 用法能把原文与索引拆开但同步职责需要显式设计。这篇先用普通 FTS5 内容表减少状态数量。等到确实需要与业务主表共享内容时再考虑触发器、回填和重建避免为了省一份正文把入门小工具变成同步系统。7. 六个容易踩到的坑第一把支持 Unicode 当作会中文分词。判断依据是词项和查询结果。拿一段不带空格的中文测试完整句、句中三四字和两字词比看到“unicode”这个名字就放心更可靠。第二只换成 trigram却没有短词策略。两个中文字符仍然不够。要么提示补充输入要么明确走慢扫描要么使用支持目标语言的分词扩展不能把零结果直接解释成“笔记不存在”。第三以为参数绑定会屏蔽所有搜索语法。绑定参数能保护 SQL 边界MATCH 内部还有一层语言。字面模式和高级模式最好在界面上分开分别测试引号、减号、布尔词、空白和标点。第四BM25 倒序导致排序反了。先准备几篇人工可判断相关性的文档确认最相关的标题与正文出现在预期位置再尝试调权重。文档顺序与分数并列时的稳定排序也要固定避免每次查询跳动。第五刷新只增加不删除。文件删了还出现在搜索结果里比查询稍慢更影响信任。完整生命周期至少覆盖首次导入、内容修改、重命名、删除、失败回滚和重复刷新。第六把数据库里的摘录直接当 HTML 插进去。笔记内容也可能含标签。当前程序返回纯文本并用方括号显示命中若改成网页先按文本转义再用受控结构渲染高亮避免让笔记中的标签成为脚本入口。8. 这个小工具适合做到哪一步如果目标是个人笔记、离线帮助文档和单机应用中的关键词搜索SQLite 的部署成本很低一个数据库文件没有独立搜索服务。把 FTS 放在 Python 标准库后面也容易做成命令行、桌面应用或仅本机监听的接口。真正决定使用体验的往往是提取标题、处理短词、展示命中上下文和及时更新。如果文档数量大到全量读取占用太多内存先改索引构建流程如果两字词占据大多数查询优先改分词如果用户总输入同义表达考虑语义召回如果需要跨机器扩容、复杂权限过滤和高并发写入再评估独立检索服务。升级应由观测到的瓶颈推动而不是因为“搜索”两个字就套用完整平台。参数可以分开调整。先把返回数量维持在十条观察第一条的可用性而不是一次返回几百条让读者继续手工筛选。标题权重设五只表达相对偏好如果每篇标题都只有“学习记录”加权几乎没有帮助应先改善标题提取和命名。正文片段长度使用二十四个词项作为起点在三字符分词下它不等同于二十四个汉字最终仍要目视检查摘录长度。短词路径还保留了一个简化摘录取正文前一百个字符未必包含很靠后的命中位置。如果搜索“事务”找到了正确文件但摘要没有这个词不能认定结果造假要检查展示层。下一步可以找到首个匹配位置向前后各取一段上下文同时保留多个命中位置的选择规则避免标题命中和正文命中互相混淆。出现零结果时排错顺序可以固定下来确认本次连接的数据库绝对路径再核对导入文件数查看那篇原文是否存在检查查询长度对应的模式最后检查词项与输入是否一致。如果旧内容仍然出现优先核对是否对同一个数据库执行了刷新。性能问题则先比较短词扫描和长词索引的比例再看命中数量、文本体积和是否重复启动进程。本示例没有后台任务索引期间也没有承诺搜索一定不受阻塞。如果把它接到网页索引任务和查询连接需要分开管理并确认 SQLite 的事务与锁行为多名用户同时编辑时还要考虑文件扫描的一致性。对个人工具而言先采用手动刷新按钮和清楚的更新时间通常就能让搜索副本的状态可见减少“刚写完为什么找不到”的困惑。发布前可以用自己的十个常搜词验收能否找到文件第一条是否合理摘录是否帮助判断编辑后是否刷新删除后是否消失短词是否提示慢路径。代码包包含最小示例、完整命令行工具、回归与基准脚本以及实际输出。你自己的笔记里更常搜两个字的术语还是记得三四个字的原句这会直接影响下一版的分词选择。附录完整命令行工具保存为 search_notes.py使用文中的 index 与 search 命令。Local UTF-8 Markdown/text search. Python standard library only.importargparseimportjsonimportsqlite3frompathlibimportPathdefconnect(path):dbsqlite3.connect(path)db.execute(CREATE VIRTUAL TABLE IF NOT EXISTS notes USING fts5(path UNINDEXED,title,body,tokenizetrigram))returndbdefindex(db,folder):folderPath(folder).resolve(strictTrue)ifnotfolder.is_dir():raiseValueError(folder must be a directory)docs[]forfileinsorted(folder.rglob(*)):ifnotfile.is_file()orfile.suffix.lower()notin{.md,.txt}:continueiffile.is_symlink()ornotfile.resolve().is_relative_to(folder):continueiffile.stat().st_size1024*1024:raiseValueError(ffile exceeds 1 MiB:{file})contentfile.read_text(encodingutf-8-sig)titlenext((x.lstrip(# ).strip()forxincontent.splitlines()ifx.strip()),file.stem)docs.append((file.relative_to(folder).as_posix(),title,content))# Read all files before replacing this derived index; transaction rolls back on error.withdb:db.execute(DELETE FROM notes)db.executemany(INSERT INTO notes(path,title,body) VALUES(?,?,?),docs)returnlen(docs)defsearch(db,query,limit10):queryquery.strip()ifnotqueryorlen(query)200:raiseValueError(query must contain 1 to 200 characters)ifnot1limit50:raiseValueError(limit must be between 1 and 50)iflen(query)3:# Literal short substring search. Bounded output does not bound scan cost.rowsdb.execute(SELECT path,title,substr(body,1,100) FROM notes WHERE instr(title,?)0 OR instr(body,?)0 ORDER BY path LIMIT ?,(query,query,limit)).fetchall()modeshort-scanelse:phrasequery.replace(,)rowsdb.execute(SELECT path,title,snippet(notes,2,[,],...,24) FROM notes WHERE notes MATCH ? ORDER BY bm25(notes,0.0,5.0,1.0),path LIMIT ?,(phrase,limit)).fetchall()modetrigram-matchreturn{mode:mode,results:[dict(zip((path,title,excerpt),row))forrowinrows]}defmain():pargparse.ArgumentParser()p.add_argument(--db,defaultnotes.db,helpdedicated derived index; index replaces its notes table)subp.add_subparsers(destcommand,requiredTrue)buildsub.add_parser(index)build.add_argument(folder)findsub.add_parser(search)find.add_argument(query)find.add_argument(--limit,typeint,default10)argsp.parse_args()dbconnect(args.db)try:value{indexed:index(db,args.folder)}ifargs.commandindexelsesearch(db,args.query,args.limit)print(json.dumps(value,ensure_asciiFalse,indent2))finally:db.close()if__name____main__:main()参考资料SQLite FTS5 官方文档Python sqlite3 参数绑定SQLite 执行计划说明SQLite LIKE 优化条件