问题场景

在桌面或移动端的本地笔记应用中，搜索通常只需覆盖标题和正文。引入 Elasticsearch 这类外部服务会增加部署和运维成本；SQLite 自带 FTS5 虚拟表，适合在单机场景做轻量全文检索。实际落地时有四个细节容易出错：主表与 FTS 表的数据同步、删除和更新时的索引维护、按相关度排序、以及空查询和特殊字符的处理。

本文用一个最小可运行示例，演示 external content 模式下的 FTS5 表、三个触发器、MATCH 查询、bm25 排序和 snippet 高亮。

表结构与同步触发器

主表 notes 保存业务数据，FTS5 表 notes_fts 通过 content='notes' 和 content_rowid='id' 指向主表。这样 FTS 表不重复保存正文，只保存索引；查询时再 JOIN 回主表。注意 external content 模式下，DELETE 和 UPDATE 触发器必须把旧值传给 FTS5 的 delete 命令，否则索引会残留。

PRAGMA foreign_keys = ON;

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE VIRTUAL TABLE notes_fts USING fts5(
  title,
  body,
  content='notes',
  content_rowid='id',
  tokenize='unicode61'
);

CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN
  INSERT INTO notes_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER notes_ad AFTER DELETE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER notes_au AFTER UPDATE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO notes_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

更新 notes 时，应用层同时更新 updated_at。不要把 updated_at 的维护放进触发器里再更新主表，否则容易产生递归触发和语义混乱。

查询、排序与高亮

查询用 MATCH 走 FTS5 索引，bm25 返回的是负值，按升序排列即可让更相关的结果靠前。第一个参数是 FTS 表名，后面的参数是列权重；示例给标题更高权重。snippet 的列索引从 0 开始：0 是 title，1 是 body。末尾的数字控制片段长度。

SELECT
  n.id,
  n.title,
  snippet(notes_fts, 1, '<mark>', '</mark>', '…', 16) AS body_snippet,
  bm25(notes_fts, 2.0, 1.0) AS rank
FROM notes_fts
JOIN notes n ON n.id = notes_fts.rowid
WHERE notes_fts MATCH ?
ORDER BY rank ASC
LIMIT 20;

snippet 返回的是带标记的原文片段，不是转义后的 HTML。渲染到页面前必须对原文做 HTML 转义，再插入高亮标签，避免把笔记内容当成 HTML 执行。

可运行的最小示例

下面用 Python 标准库 sqlite3 建内存库并插入三条笔记。search 函数先判断空查询：如果去掉首尾空白后为空，就返回最近更新的笔记，不执行 MATCH。非空查询才走 FTS5。

import sqlite3

SCHEMA = '''
PRAGMA foreign_keys = ON;
CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE VIRTUAL TABLE notes_fts USING fts5(
  title, body,
  content='notes',
  content_rowid='id',
  tokenize='unicode61'
);
CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN
  INSERT INTO notes_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER notes_ad AFTER DELETE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER notes_au AFTER UPDATE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO notes_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;
'''

def init_db():
    conn = sqlite3.connect(':memory:')
    conn.executescript(SCHEMA)
    return conn

def add_note(conn, title, body):
    conn.execute(
        'INSERT INTO notes(title, body, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP)',
        (title, body),
    )

def search(conn, query, limit=20):
    q = (query or '').strip()
    if not q:
        return conn.execute(
            'SELECT id, title, body, substr(body, 1, 80) AS body_snippet FROM notes '
            'ORDER BY updated_at DESC LIMIT ?',
            (limit,),
        ).fetchall()
    sql = '''
    SELECT n.id, n.title,
           snippet(notes_fts, 1, '<mark>', '</mark>', '…', 16) AS body_snippet
    FROM notes_fts
    JOIN notes n ON n.id = notes_fts.rowid
    WHERE notes_fts MATCH ?
    ORDER BY bm25(notes_fts, 2.0, 1.0) ASC
    LIMIT ?
    '''
    return conn.execute(sql, (q, limit)).fetchall()

if __name__ == '__main__':
    conn = init_db()
    add_note(conn, 'SQLite 触发器', '用触发器维护 FTS5 索引，避免手动同步。')
    add_note(conn, '空查询处理', '当搜索词为空时，回到最近更新的笔记列表。')
    add_note(conn, 'bm25 排序', 'FTS5 的 bm25 函数返回负值，按升序得到更相关结果。')
    conn.commit()
    for row in search(conn, '触发器'):
        print(row)
    print('--- 空查询 ---')
    for row in search(conn, '   '):
        print(row[:2])

运行方法：将代码保存为 fts_demo.py，执行 python fts_demo.py。需要当前 Python 的 sqlite3 模块启用了 FTS5；若报 no such module: fts5，说明该 SQLite 构建未包含 FTS5，应换用带 FTS5 的构建。示例使用内存库，重启后数据消失；生产环境把 ':memory:' 换成文件路径即可。

边界与限制

• 默认 unicode61 分词器对中文的支持需要按实际构建测试，连续中文的切词结果可能不符合预期；可评估 trigram 分词器，但短查询和前缀匹配行为会不同，且要先确认当前 SQLite 构建包含该分词器。
• external content 表要求主表与 FTS 表一致。批量导入大量数据时，逐行触发器会增加写入开销；可在事务内先按需禁用触发器，导入后执行 rebuild。重建命令为 INSERT INTO notes_fts(notes_fts) VALUES('rebuild');。
• bm25 返回负值，排序用 ASC；不要用大于 0 过滤。权重可按标题、正文的重要性调整。
• 空查询要在应用层短路，避免把空字符串传给 MATCH。用户输入含 FTS5 特殊字符时，应做查询语法转义，或把词包成引号短语，防止语法错误。
• snippet 返回原始文本片段，插入 HTML 前必须转义，再添加高亮标签，避免笔记内容中的标签被执行。
• 示例用内存库，生产环境改为文件路径；更新笔记时由应用层维护 updated_at，避免触发器递归更新主表。