Python操作SQLite时，实现集合类型列的标准SQL方案探讨

针对你遇到的用逗号分隔字符串存储集合的痛点，这里有几个标准SQL的优化方案，比原方案更高效，也能避免关联表的磁盘占用问题：

1. 利用SQLite的JSON类型存储数组 #

SQLite从3.9.0版本开始原生支持JSON类型，这是最推荐的方案：

• 创建表时定义列类型为JSON：

  CREATE TABLE data(id int, name text, items_set JSON);
  

• 插入集合：用json_array()函数生成数组，比如：

  INSERT INTO data(id, name, items_set) VALUES(1, 'test', json_array('apple', 'banana', 'ananas'));
  

• 查询包含特定元素的记录：用json_contains()函数精准匹配，不用处理分隔符：

  SELECT * FROM data WHERE json_contains(items_set, '"ananas"');
  

• 新增元素（避免重复）：全程在SQL层面完成，无需加载整个集合到Python：

  UPDATE data 
  SET items_set = json_array_append(items_set, '$', 'orange')
  WHERE id = 1 AND NOT json_contains(items_set, '"orange"');
  

这个方案的磁盘占用远低于关联表，而且不需要预先知道集合的所有可能值，操作也比逗号分隔灵活得多。

2. 用FTS5全文搜索优化字符串集合查询 #

如果你的集合元素都是字符串，且主要需求是快速查询包含特定元素的记录，可以给items_set列搭配FTS5全文索引：

• 创建FTS虚拟表：

  CREATE VIRTUAL TABLE data_fts USING fts5(id UNINDEXED, items_set);
  

• 插入时同步更新FTS表，查询时直接用全文匹配：

  SELECT d.* FROM data d JOIN data_fts f ON d.id = f.id WHERE f.items_set MATCH 'ananas';
  

这个方案查询效率极高，但更新元素时需要同步维护FTS表，适合查询需求远大于更新需求的场景。

3. 临时优化原逗号分隔方案（不推荐长期使用） #

如果暂时不想改表结构，可以优化存储格式来避免LIKE的部分匹配问题：

• 存储时给集合前后都加上逗号，比如,apple,banana,ananas,
• 查询时用LIKE '%,ananas,%'，这样就不会匹配到比如pineananas这类包含目标字符串的元素
  不过这个方案依然解决不了更新时需要加载整个集合的问题，只能作为临时过渡。

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