如何在SQLAlchemy中对MutableList类型列进行过滤？

你遇到的核心问题是：用MutableList.as_mutable(PickleType)存储的列表，无法通过数据库原生查询语句过滤。原因很简单：PickleType会把Python列表序列化成二进制数据存储，数据库无法解析这串二进制的实际内容，所以直接用in_、==这类数据库查询语法自然匹配不上。

下面是针对不同场景的解决方案：

方案1：改用MariaDB原生JSON类型（推荐） #

MariaDB支持JSON格式存储，SQLAlchemy可以结合MutableList让Python层面仍保持列表操作，同时数据库能解析JSON内容进行查询，性能和可维护性都更好。

修改模型定义 #

from sqlalchemy import Column, String, Integer, JSON
from sqlalchemy.ext.mutable import MutableList

class MyClass(db.Model):
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False, index=True)
    name = Column(String(100), nullable=False)
    # 用MutableList包装JSON列，Python侧仍用列表操作
    name_list = Column(MutableList.as_mutable(JSON), default=[])

对应查询方式 #

• 查询列表中包含'test'的记录：

  # 方式1：用JSON_CONTAINS函数，注意第二个参数是JSON格式字符串
  q = MyClass.query.filter(db.func.json_contains(MyClass.name_list, '"test"')).first()
  
  # 方式2：用json_array构造参数，更严谨
  q = MyClass.query.filter(db.func.json_contains(MyClass.name_list, db.func.json_array('test'))).first()
  

• 查询列表完全等于['test']的记录：

  q = MyClass.query.filter(MyClass.name_list == ['test']).first()
  

• 查询列表与指定值集合有交集（MariaDB 10.3+支持）：

  search_vals = ['test', 'foo']
  q = MyClass.query.filter(db.func.json_overlaps(MyClass.name_list, db.func.json_array(*search_vals))).first()
  

方案2：如果必须保留PickleType（不推荐） #

因为数据库无法解析Pickle二进制，只能先把所有数据查出来，在Python层面过滤：

# 过滤包含'test'的记录
results = [item for item in MyClass.query.all() if 'test' in item.name_list]

# 过滤完全等于['test']的记录
results = [item for item in MyClass.query.all() if item.name_list == ['test']]

⚠️ 注意：这种方法在数据量较大时会严重影响性能，因为要全表扫描并加载所有数据到内存处理。

方案3：改用多对多关联表（适合复杂场景） #

如果列表中的元素是独立业务实体，最规范的做法是用关联表拆分，完全避免数组存储的问题：

定义关联表与模型 #

# 多对多关联表
name_association = db.Table(
    'name_association',
    db.Column('myclass_id', db.Integer, db.ForeignKey('my_class.id'), primary_key=True),
    db.Column('name_id', db.Integer, db.ForeignKey('name.id'), primary_key=True)
)

class Name(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.String(100), unique=True, nullable=False)

class MyClass(db.Model):
    __tablename__ = 'my_class'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False, index=True)
    name = Column(String(100), nullable=False)
    name_list = db.relationship('Name', secondary=name_association, backref='my_classes')

对应查询方式 #

# 查询包含'test'的MyClass记录
q = MyClass.query.join(Name).filter(Name.value == 'test').first()

这种方式能利用数据库索引，查询效率最高，也符合关系型数据库的设计规范。

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