嘿，我明白你想替换低效的通配符搜索，改用SQLite的全文搜索（FTS）功能——这确实是个明智的选择！下面我给你一个可直接复用的实现方案，基于FTS5（SQLite最新、性能最好的全文搜索版本），结合你的Flask-SQLAlchemy代码来适配。

核心思路 #

SQLite的全文搜索依赖虚拟FTS表，我们需要创建一个和你的Person表对应的FTS表，然后通过事件监听自动同步两者的数据，最后用FTS特有的MATCH语法执行高效搜索。

完整实现代码 #

1. 定义模型（原模型+FTS模型） #

保留你原来的Person模型，新增一个用于全文搜索的PersonFTS模型：

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import event

db = SQLAlchemy()

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text, unique=True, nullable=False)
    # 这里可以添加你的其他字段，比如age、email等

class PersonFTS(db.Model):
    __tablename__ = 'person_fts'  # FTS表的名称，后缀加fts方便识别
    # FTS5表不需要手动定义主键，SQLite会自动生成rowid字段
    name = db.Column(db.Text)  # 只需要包含你想搜索的字段

    # 快捷属性：通过FTS记录的rowid关联回原Person对象
    @property
    def person(self):
        return Person.query.get(self.rowid)

2. 设置数据同步（自动同步原表和FTS表） #

为了保证FTS表的数据和原Person表一致，我们用SQLAlchemy的事件监听，在原表数据增、删、改时自动同步到FTS表：

# 监听Person插入事件：新增Person时同步到FTS表
@event.listens_for(Person, 'after_insert')
def sync_insert_to_fts(mapper, connection, target):
    connection.execute(
        PersonFTS.__table__.insert().values(name=target.name)
    )

# 监听Person更新事件：修改Person时同步更新FTS表
@event.listens_for(Person, 'after_update')
def sync_update_to_fts(mapper, connection, target):
    connection.execute(
        PersonFTS.__table__.update()
        .where(PersonFTS.rowid == target.id)
        .values(name=target.name)
    )

# 监听Person删除事件：删除Person时同步删除FTS表对应记录
@event.listens_for(Person, 'after_delete')
def sync_delete_from_fts(mapper, connection, target):
    connection.execute(
        PersonFTS.__table__.delete()
        .where(PersonFTS.rowid == target.id)
    )

3. 执行全文搜索 #

现在你可以用MATCH语法执行高效的全文搜索了，支持多种搜索语法：

# 基础搜索：匹配包含"john"的名字
results = PersonFTS.query.filter(PersonFTS.name.match('john')).all()

# 获取对应的完整Person对象
for fts_record in results:
    print(f"找到用户：ID={fts_record.person.id}，姓名={fts_record.person.name}")

# 前缀搜索：匹配以"joh"开头的名字
prefix_results = PersonFTS.query.filter(PersonFTS.name.match('joh*')).all()

# 短语搜索：精确匹配"john doe"这个完整短语
phrase_results = PersonFTS.query.filter(PersonFTS.name.match('"john doe"')).all()

# 逻辑搜索：匹配包含"john"或者"smith"的名字
logic_results = PersonFTS.query.filter(PersonFTS.name.match('john OR smith')).all()

# 关联原表直接查询完整数据（更高效的写法）
from sqlalchemy.orm import joinedload
full_person_results = db.session.query(Person)\
    .join(PersonFTS, Person.id == PersonFTS.rowid)\
    .filter(PersonFTS.name.match('john'))\
    .options(joinedload(Person))\
    .all()

关键注意事项 #

• FTS版本：我用的是FTS5，现在绝大多数SQLite版本都默认支持；如果你的环境比较旧，可以改用FTS4（只需把__tablename__改成person_fts USING fts4）。
• 多字段搜索：如果需要搜索多个字段（比如name+email），只需在PersonFTS里添加对应的字段，搜索时用PersonFTS.match('john OR example@mail.com')即可。
• 表初始化：运行db.create_all()时，FTS表会自动被创建，不需要额外操作。

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