SQLite FTS5触发器仅索引页面最新版本问题排查

我要基于SQLite做一个类Wiki的笔记系统，用pages表存储页面的所有修订版本，每个条目包含：

• 页面标题
• 修订时间戳
• 页面文本
  页面删除操作很少，但需要支持。核心要求是只对每个页面的最新修订版做FTS5索引和搜索，也就是FTS5索引里只能保留每个页面的最新版本。我尝试用触发器管理索引，但搜索时还是会返回所有修订版的结果。

测试代码 #

-- 页面表
CREATE TABLE IF NOT EXISTS pages (
    id INTEGER PRIMARY KEY,
    revision TEXT NOT NULL,
    title TEXT NOT NULL,
    body TEXT
);

-- FTS5表
CREATE virtual TABLE pages_fts USING FTS5(
    id,
    title,
    body,
    content='pages',
    content_rowid=id
);

-- 插入前删除旧版索引
CREATE TRIGGER pages_before_insert BEFORE INSERT ON pages
BEGIN
    INSERT INTO pages_fts (pages_fts, id, title, body)
        SELECT 'delete', id, title, body
        FROM pages
        WHERE title = new.title
        ORDER BY revision
        DESC
        LIMIT 1;
END;

-- 插入后添加新版索引
CREATE TRIGGER pages_after_insert AFTER INSERT ON pages
BEGIN
    INSERT INTO pages_fts (id, title, body)
    VALUES (new.id, new.title, new.body);
END;

-- 删除页面后移除对应索引
CREATE TRIGGER pages_after_delete AFTER DELETE ON pages
BEGIN
    INSERT INTO pages_fts (pages_fts, id, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

-- 测试数据
INSERT INTO pages (revision, title, body) VALUES ('2023-04-26T22:48:35.582797', 'home', 'body version one');
INSERT INTO pages (revision, title, body) VALUES ('2023-04-26T22:48:40.250981', 'home', 'body version two');
INSERT INTO pages (revision, title, body) VALUES ('2023-04-26T22:48:46.205782', 'home', 'body version one');

-- 完整性检查
INSERT INTO pages_fts(pages_fts, rank) VALUES ('integrity-check', 1);

-- 标题搜索（预期1条结果）
SELECT title, snippet(pages_fts, 1, '>', '<', '...', 10)
AS snippet
FROM pages_fts
WHERE pages_fts
MATCH 'title:home'
ORDER BY rank
LIMIT 50;

-- 内容搜索（预期1条结果）
SELECT title, snippet(pages_fts, 2, '>', '<', '...', 64)
AS snippet
FROM pages_fts
WHERE pages_fts
MATCH 'body:version'
ORDER BY rank
LIMIT 50;

问题现象 #

上述两次搜索本应各返回1条结果，但实际返回了多条，输出如下：

home|>home<
home|>home<
home|>home<
home|body >version< one
home|body >version< two
home|body >version< one

问题原因 #

当FTS5表指定content='pages'时，SQLite会自动创建默认的同步触发器，负责把pages表的所有行都同步到FTS索引里。手动创建的触发器和默认触发器重复工作，导致所有修订版都被加入了索引，没有被正确清理。

解决方案 #

1. 去掉FTS5表的content和content_rowid参数，放弃自动同步，完全手动控制索引内容。
2. 修改触发器逻辑：插入新修订版时，先删除该页面所有旧的索引条目，再插入新的；删除页面时，检查该页面是否还有剩余修订版，若有则将最新版加入索引，否则删除对应索引条目。

修正后的完整代码 #

-- 页面表
CREATE TABLE IF NOT EXISTS pages (
    id INTEGER PRIMARY KEY,
    revision TEXT NOT NULL,
    title TEXT NOT NULL,
    body TEXT
);

-- 不使用自动同步的FTS5表
CREATE VIRTUAL TABLE pages_fts USING FTS5(title, body);

-- 插入新修订版前，删除该页面所有旧索引
CREATE TRIGGER pages_before_insert BEFORE INSERT ON pages
BEGIN
    DELETE FROM pages_fts WHERE title = new.title;
END;

-- 插入后添加新版索引
CREATE TRIGGER pages_after_insert AFTER INSERT ON pages
BEGIN
    INSERT INTO pages_fts (title, body) VALUES (new.title, new.body);
END;

-- 删除页面后处理索引
CREATE TRIGGER pages_after_delete AFTER DELETE ON pages
BEGIN
    -- 先删除该页面的现有索引
    DELETE FROM pages_fts WHERE title = old.title;
    -- 如果该页面还有其他修订版，把最新的那个加入索引
    INSERT INTO pages_fts (title, body)
    SELECT title, body
    FROM pages
    WHERE title = old.title
    ORDER BY revision DESC
    LIMIT 1;
END;

-- 测试数据
INSERT INTO pages (revision, title, body) VALUES ('2023-04-26T22:48:35.582797', 'home', 'body version one');
INSERT INTO pages (revision, title, body) VALUES ('2023-04-26T22:48:40.250981', 'home', 'body version two');
INSERT INTO pages (revision, title, body) VALUES ('2023-04-26T22:48:46.205782', 'home', 'body version one');

-- 完整性检查
INSERT INTO pages_fts(pages_fts, rank) VALUES ('integrity-check', 1);

-- 标题搜索（现在返回1条结果）
SELECT title, snippet(pages_fts, 1, '>', '<', '...', 10)
AS snippet
FROM pages_fts
WHERE pages_fts
MATCH 'title:home'
ORDER BY rank
LIMIT 50;

-- 内容搜索（现在返回1条结果）
SELECT title, snippet(pages_fts, 2, '>', '<', '...', 64)
AS snippet
FROM pages_fts
WHERE pages_fts
MATCH 'body:version'
ORDER BY rank
LIMIT 50;

说明 #

• 去掉content参数后，FTS5不再自动同步pages表的内容，完全由我们的触发器控制。
• 插入新修订版时，先清空该页面的所有索引，再插入最新版，确保索引里只有当前最新的内容。
• 删除页面时，先清空该页面的索引，然后检查是否还有剩余修订版，如果有就把最新的重新加入索引，保证索引始终只保留每个页面的最新状态。

内容的提问来源于stack exchange，提问作者eil