SQLite FTS5虚拟表与原表关联查询结果不一致问题咨询

问题原因 #

你创建的是外部内容模式的FTS5虚拟表（通过content="TRANSACTIONS"指定），这种模式下FTS5不存储实际数据，仅依赖源表TRANSACTIONS的内容建立索引。但你用手动插入的方式填充FTS5表：

INSERT INTO TRANSACTIONS_FTS (rowid, DESC, COMMENT, INVOICE) SELECT ID, DESC, COMMENT, INVOICE FROM TRANSACTIONS;

这是错误操作——外部内容模式的FTS5表无需手动插入数据，手动插入会导致FTS5内部的rowid与源表的ID列无法正确映射，最终关联查询时T.ID = T_MATCH.rowid无匹配项。

修复与调试步骤 #

1. 验证rowid不匹配问题
   执行以下查询，确认FTS5表的rowid和源表ID是否存在对应关系：

   SELECT tf.rowid, t.ID 
   FROM TRANSACTIONS_FTS tf
   LEFT JOIN TRANSACTIONS t ON tf.rowid = t.ID
   LIMIT 20;
   

   你会发现大部分tf.rowid与t.ID不匹配，甚至t.ID为NULL，这就是关联查询无结果的直接原因。

2. 重新正确同步FTS5索引
   先清空错误填充的FTS5表，再用FTS5内置的重建命令同步源表数据：

   -- 清空FTS5表
   DELETE FROM TRANSACTIONS_FTS;
   -- 自动从源表同步数据并建立索引
   INSERT INTO TRANSACTIONS_FTS(TRANSACTIONS_FTS) VALUES('rebuild');
   

   该命令会让FTS5自动遍历TRANSACTIONS表，依据content_rowid="ID"的设置，将源表的ID作为FTS5表的rowid，确保两者一一对应。

3. 验证关联查询结果
   再次执行你的关联查询：

   SELECT DISTINCT T.* 
   FROM TRANSACTIONS AS T 
   JOIN ( 
       SELECT * FROM TRANSACTIONS_FTS WHERE TRANSACTIONS_FTS MATCH 'cabana' 
   ) AS T_MATCH ON T.ID = T_MATCH.rowid;
   

   此时应该能得到和直接搜索FTS5一致的27条结果。

4. 后续数据同步注意事项
   外部内容模式的FTS5表不会自动同步源表的新增/修改/删除数据，若源表数据更新，需再次执行INSERT INTO TRANSACTIONS_FTS(TRANSACTIONS_FTS) VALUES('rebuild');重建索引，或创建触发器实现自动同步。

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