如何从SQLite全文检索中获取单文档的多匹配结果？

我用SQLite全文检索存储书籍内容，每本书作为FTS表的单个文档。当前查询时，哪怕一本书里有多个匹配项，每本书最多只返回一条结果，但我需要每个匹配项单独返回一条结果。试过FTS4、FTS5和相关辅助函数都没解决。

将整本书存成单条记录的原因是避免跨页面边界的短语查询问题，另外还有映射表存储拼接后书籍数据的内容偏移量。

当前查询语句 #

select offsets(contents) from contents where data match '<search keyword>'

当前输出 #

1 0 2450 8
1 0 11648 8 1 0 11728 8
1 0 2637 8
1 0 1150 8 1 0 2206 8 1 0 6753 8 1 0 10796 8 1 0 11211 8 1 0 12095 8 1 0 15020 8
1 0 647 8 1 0 2024 8 1 0 4990 8 1 0 6024 8 1 0 8255 8
1 0 5101 8 1 0 6804 8 1 0 7663 8 1 0 7977 8 1 0 8154 8 1 0 9880 8
1 0 207 8 1 0 3116 8

当前问题是丢失了排序信息——同一条结果里的多个匹配项可能质量更优，但因为整个文档被视为单条记录，无法区分这些差异。

期望输出 #

1 0 2450 8
1 0 11648 8
1 0 11728 8
1 0 2637 8
1 0 1150 8
1 0 2206 8
1 0 6753 8
1 0 10796 8
1 0 11211 8
1 0 12095 8
1 0 15020 8
1 0 647 8
1 0 2024 8
1 0 4990 8
1 0 6024 8
1 0 8255 8
1 0 5101 8
1 0 6804 8
1 0 7663 8
1 0 7977 8
1 0 8154 8
1 0 9880 8
1 0 207 8
1 0 3116 8

解决方案 #

使用SQLite的递归CTE拆分offsets返回的多组匹配数据，实现每行一个匹配项的输出：

WITH split_offsets AS (
  -- 初始步骤：获取匹配文档的offsets结果及初始位置
  SELECT 
    rowid,
    offsets(contents) AS offset_str,
    0 AS pos
  FROM contents 
  WHERE data match '<search keyword>'
  UNION ALL
  -- 递归拆分：每次截取一组匹配数据，直到处理完所有内容
  SELECT 
    rowid,
    SUBSTR(offset_str, INSTR(offset_str, ' ') + 1),
    pos + 1
  FROM split_offsets
  WHERE offset_str != ''
)
-- 提取每组有效匹配数据，过滤空行
SELECT SUBSTR(offset_str, 1, INSTR(offset_str || ' ', ' ') - 1) AS single_offset
FROM split_offsets
WHERE offset_str != '';

原理说明 #

1. 递归CTE的初始部分先获取所有匹配文档的offsets结果，同时记录初始位置标记。
2. 递归部分每次从当前的offset_str中移除第一个空格前的内容，逐步拆分出所有独立匹配项。
3. 最后从拆分后的结果中提取每组完整的匹配偏移数据，得到符合期望的单行单匹配项输出。

如果使用FTS5，也可以借助fts5_poslist函数更高效地处理匹配位置，但上述递归CTE方案对FTS4和FTS5均适用。

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