如何在SQLite+Python3的FTS5查询中处理字符串形式的列表列？

好问题！这种把列表存成字符串的场景确实容易卡壳，但咱们完全可以在SQLite查询过程中完成解析和检索，结合Python自定义函数和FTS5就能搞定。下面是具体的实现方案：

核心思路 #

SQLite本身没有内置解析字符串列表的函数，所以我们可以给SQLite注册一个Python自定义函数，用来把字符串形式的列表转换成适合FTS5检索的格式（比如用空格分隔的元素字符串），然后在查询时直接调用这个函数配合FTS5的MATCH语法。

步骤1：注册自定义解析函数 #

先用Python写一个安全解析字符串列表的函数，然后注册给SQLite，让SQL语句能直接调用它：

import sqlite3
import ast

def parse_string_list(str_list):
    """把字符串形式的列表解析成空格分隔的字符串，方便FTS5检索"""
    try:
        # 用ast.literal_eval安全解析，避免eval的安全风险
        lst = ast.literal_eval(str_list)
        # 去除每个元素的前后空格，再拼接成空格分隔的字符串
        return ' '.join(item.strip() for item in lst)
    except (ValueError, SyntaxError):
        # 处理解析失败的情况（比如格式错误的字符串），返回空串不影响检索
        return ''

# 连接数据库
conn = sqlite3.connect('your_database.db')
# 注册自定义函数，参数分别是函数名、参数个数、对应的Python函数
conn.create_function('parse_list', 1, parse_string_list)
cursor = conn.cursor()

步骤2：结合FTS5进行查询 #

假设你已经有一个FTS5虚拟表fts_data，包含需要检索的普通列content和存储字符串列表的列list_column，可以这样写查询语句：

方式1：分别匹配普通列和解析后的列表列

SELECT * FROM fts_data
WHERE fts_data MATCH 'your_search_keyword'
OR parse_list(list_column) MATCH 'your_search_keyword';

方式2：合并所有检索内容后匹配

如果想让关键词同时匹配普通列和列表元素，也可以把两者拼接后再匹配：

SELECT * FROM fts_data
WHERE (content || ' ' || parse_list(list_column)) MATCH 'your_search_keyword';

性能优化建议 #

如果你的数据量很大，每次查询都动态解析字符串列表会有性能损耗。这种情况下，推荐用SQLite的生成列提前把解析后的内容存起来，再让FTS5索引这个生成列：

1. 先创建原始表，定义生成列：

CREATE TABLE raw_data (
    id INTEGER PRIMARY KEY,
    content TEXT,
    list_str TEXT,
    -- 自动生成解析后的字符串，STORE表示存储在磁盘上
    parsed_list TEXT GENERATED ALWAYS AS (parse_list(list_str)) STORED
);

2. 创建FTS5虚拟表，索引生成列：

CREATE VIRTUAL TABLE fts_data USING fts5(
    content,
    parsed_list,
    content=raw_data,  -- 关联原始表
    content_rowid=id   -- 关联原始表的主键
);

这样插入数据时，parsed_list会自动生成，FTS5直接索引这个列，查询时就不用再动态解析了，速度会快很多。

注意事项 #

• 一定要用ast.literal_eval而不是eval，前者会安全地解析字符串，避免执行恶意代码的风险。
• 确保存储的字符串列表格式正确（比如用单引号/双引号包裹元素，逗号分隔），否则解析会失败。
• 可以根据自己的需求调整解析函数，比如如果列表元素包含空格，可能需要用其他分隔符，或者调整FTS5的分词规则。

内容的提问来源于stack exchange，提问作者Mr. Hax