如何在SQLAlchemy ORM中使用MySQL全文搜索、索引与MATCH AGAINST？

一、调整ORM模型，创建MySQL FULLTEXT索引 #

MySQL的普通索引（index=True）不支持全文搜索，必须显式创建FULLTEXT联合索引，且仅支持CHAR/VARCHAR/TEXT类型字段（JSON字段需特殊处理）。修改你的Publication模型如下：

from sqlalchemy import Column, Integer, String, Text, JSON, Index
from sqlalchemy.ext.declarative import declarative_base

ORMBase = declarative_base()

class Publication(ORMBase):
    __tablename__ = 'publications'
    
    id = Column(Integer, primary_key=True, index=True)
    paperId = Column(String(200), nullable=False, index=True, unique=True)
    url = Column(Text, nullable=True)
    title = Column(String(255), nullable=False)
    abstract = Column(Text(length=65535), nullable=True)
    venue = Column(String(255), nullable=True)
    authors = Column(JSON, nullable=True)

    # 创建title、abstract、venue的联合FULLTEXT索引
    __table_args__ = (
        Index('ft_publications_main', 'title', 'abstract', 'venue', mysql_prefix='FULLTEXT'),
    )

针对JSON字段authors的特殊处理 #

MySQL 8.0.17及以上版本支持对JSON字段创建FULLTEXT索引，但需要指定JSON路径提取字符串内容。如果你的authors格式是[{"name": "张三"}, ...]，可以添加虚拟列并创建索引：

class Publication(ORMBase):
    # ... 其他字段不变 ...
    
    # 生成虚拟列，提取authors中的name字段并拼接为字符串
    authors_names = Column(Text, nullable=True, computed="JSON_UNQUOTE(JSON_EXTRACT(authors, '$[*].name'))", persisted=True)
    
    __table_args__ = (
        Index('ft_publications_main', 'title', 'abstract', 'venue', mysql_prefix='FULLTEXT'),
        # 给虚拟列创建FULLTEXT索引
        Index('ft_publications_authors', 'authors_names', mysql_prefix='FULLTEXT'),
    )

注意：如果使用旧版MySQL（<8.0.17），无法直接对JSON字段创建全文索引，建议在插入/更新数据时，手动将authors的名称拼接为字符串存入单独的authors_names字段，再创建FULLTEXT索引。

二、修改查询语句，使用MATCH AGAINST替代ilike #

SQLAlchemy支持通过func调用MySQL的MATCH...AGAINST语法，替换原来的ilike模糊查询，大幅提升性能：

1. 针对主字段（title/abstract/venue）的搜索 #

from sqlalchemy import func

search_query = "你的搜索关键词"
# 基础全文搜索（自然语言模式，MySQL默认）
query = query.where(
    func.MATCH(Publication.title, Publication.abstract, Publication.venue)
    .AGAINST(search_query, 'IN NATURAL LANGUAGE MODE')
)

2. 包含authors字段的联合搜索 #

如果已经创建了authors_names的FULLTEXT索引，可以合并查询：

query = query.where(
    # 主字段搜索
    func.MATCH(Publication.title, Publication.abstract, Publication.venue)
    .AGAINST(search_query, 'IN NATURAL LANGUAGE MODE')
    |
    # authors名称搜索
    func.MATCH(Publication.authors_names)
    .AGAINST(search_query, 'IN NATURAL LANGUAGE MODE')
)

3. 可选：布尔模式搜索（支持精确匹配、排除关键词等） #

如果需要更精准的搜索规则（比如+关键词必须包含，-关键词排除），可以使用布尔模式：

query = query.where(
    func.MATCH(Publication.title, Publication.abstract, Publication.venue)
    .AGAINST('+机器学习 -深度学习', 'IN BOOLEAN MODE')
)

三、解决常见问题 #

• 报错Can't find FULLTEXT index matching the column list：
  确保MATCH后的字段列表和FULLTEXT索引包含的字段完全一致（顺序可调整，但必须属于同一组索引）；检查表的存储引擎是否为InnoDB（MySQL 5.6+支持InnoDB的FULLTEXT索引）。
• 最大递归深度超出错误：
  避免在查询构建中嵌套过多复杂逻辑，建议将全文搜索逻辑单独封装为函数；排查是否存在模型循环引用或查询构建时的递归调用问题。
• 搜索结果相关性排序：
  MATCH...AGAINST会返回相关性分数，可直接用于排序：

  query = query.order_by(
      func.MATCH(Publication.title, Publication.abstract, Publication.venue)
      .AGAINST(search_query).desc()
  )
  

内容的提问来源于stack exchange，提问作者Vahidin Bajrić