SQLite FTS4外部内容表触发器未更新索引问题求助

问题根源 #

你混淆了FTS4的两种使用模式：外部内容虚拟表（指定content参数）和独立存储的虚拟表。当创建FTS表时指定content='real_table'，意味着该虚拟表本身不存储数据，仅基于真实表real_table的内容构建索引。此时直接通过触发器向虚拟表插入/删除行的写法完全错误，无法触发FTS索引的正确构建，导致MATCH查询无结果。

修正步骤 #

1. 清理原有错误配置 #

先删除现有的虚拟表和触发器：

DROP TRIGGER IF EXISTS real_table_bu;
DROP TRIGGER IF EXISTS real_table_bd;
DROP TRIGGER IF EXISTS real_table_au;
DROP TRIGGER IF EXISTS real_table_ai;
DROP TABLE IF EXISTS virtual_table;

2. 重新创建FTS4外部内容虚拟表 #

保持原有的配置参数不变：

CREATE VIRTUAL TABLE virtual_table USING fts4(content='real_table', tokenize=unicode61, b, c);

3. 创建正确的索引更新触发器 #

使用FTS4专属的delete/insert命令触发索引更新，而非直接操作虚拟表的行：

-- 更新前删除旧数据对应的索引
CREATE TRIGGER real_table_bu BEFORE UPDATE ON real_table BEGIN
  INSERT INTO virtual_table(virtual_table, docid) VALUES('delete', old.rowid);
END;

-- 删除前移除对应数据的索引
CREATE TRIGGER real_table_bd BEFORE DELETE ON real_table BEGIN
  INSERT INTO virtual_table(virtual_table, docid) VALUES('delete', old.rowid);
END;

-- 更新后插入新数据的索引
CREATE TRIGGER real_table_au AFTER UPDATE ON real_table BEGIN
  INSERT INTO virtual_table(virtual_table, docid) VALUES('insert', new.rowid);
END;

-- 插入后新增数据的索引
CREATE TRIGGER real_table_ai AFTER INSERT ON real_table BEGIN
  INSERT INTO virtual_table(virtual_table, docid) VALUES('insert', new.rowid);
END;

4. 初始化现有数据的索引 #

触发器仅对后续的增删改操作生效，已存在的真实表数据需要手动构建索引：

INSERT INTO virtual_table(virtual_table) VALUES('rebuild');

额外验证 #

• 完成上述操作后，向real_table插入一条含测试关键词的数据，执行SELECT * FROM virtual_table WHERE virtual_table MATCH '测试关键词';，应能正常返回结果。
• 如果real_table使用了自定义主键而非默认的rowid，需要在创建虚拟表时添加content_rowid='你的主键字段名'参数，同时将触发器中的old.rowid/new.rowid替换为对应的主键字段。

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