如何使用SQL实现动态字符串列表查询？FastAPI场景技术问询

问题背景 #

现有Customers表，含state字段，需基于FastAPI请求参数stateInput实现动态查询：

• 当stateInput为空字符串时，返回所有客户；
• 当stateInput为逗号分隔的州值（如'NY,CA'）或单个州值时，返回匹配州的客户。

参数获取方式：

stateInput = request.query_params['stateInput']

此前尝试的4种查询均存在问题：

Query 1（IN子句参数解析错误） #

select * 
from Customers 
where (:stateInput = '' or state in (:stateInput))

问题：stateInput是单个字符串（如'NY,CA'），IN子句会将其作为单一值匹配，而非拆分后的多个州。

Query 2（列表参数绑定失败） #

stateInputList = request.query_params['stateInput'].split(',')

select * 
from Customers 
where (:stateInput = '' or state in (:stateInputList))

问题：多数数据库驱动不支持直接将列表作为IN子句的参数，会触发参数绑定异常。

Query 3（元组参数处理错误） #

stateInputTuple = tuple(request.query_params['stateInput'].split(','))

select * 
from Customers 
where (:stateInput = '' or state in (:stateInputTuple))

问题：参数化查询通常无法直接识别元组为IN子句的多个独立参数，会将其解析为复合值。

Query 4（函数兼容性问题） #

select * 
from Customers 
where FIND_IN_SET(state, :stateInput)

问题：FIND_IN_SET是MySQL专属函数，在SQL Server、PostgreSQL等数据库中无法识别，且无法利用state字段索引，性能较差。

正确解决方案 #

方案1：动态生成参数化SQL（推荐） #

根据stateInput是否为空，动态构建WHERE条件并绑定参数，彻底避免SQL注入风险，且兼容所有数据库。

示例代码（FastAPI + SQLAlchemy）：

from fastapi import FastAPI, Request
from sqlalchemy import text, create_engine

app = FastAPI()
# 替换为你的数据库连接字符串
engine = create_engine("mysql+pymysql://user:password@localhost/dbname")

@app.get("/customers")
async def fetch_customers(request: Request):
    state_input = request.query_params.get("stateInput", "")
    base_sql = "SELECT * FROM Customers"
    params = {}

    if state_input.strip():
        states = state_input.split(",")
        # 生成对应数量的参数占位符
        placeholders = [f":state_{idx}" for idx in range(len(states))]
        base_sql += f" WHERE state IN ({','.join(placeholders)})"
        # 逐个绑定参数
        for idx, state in enumerate(states):
            params[f"state_{idx}"] = state

    # 执行查询
    with engine.connect() as conn:
        result = conn.execute(text(base_sql), params)
        customers = [dict(row) for row in result.mappings()]
    
    return {"customers": customers}

方案2：使用数据库内置拆分函数（特定数据库兼容） #

如果使用的数据库支持字符串拆分函数，可直接在SQL中处理参数拆分：

SQL Server 版本：

SELECT * 
FROM Customers 
WHERE :stateInput = '' 
   OR state IN (SELECT value FROM STRING_SPLIT(:stateInput, ','))

PostgreSQL 版本：

SELECT * 
FROM Customers 
WHERE :stateInput = '' 
   OR state = ANY(STRING_TO_ARRAY(:stateInput, ','))

注意：该方案依赖数据库特定函数，且部分场景下无法利用state字段的索引，性能略逊于方案1。

核心注意事项 #

• 禁止直接拼接SQL字符串：不要将stateInput直接插入SQL语句，否则会引发SQL注入漏洞。
• 坚持参数化查询：所有用户输入必须通过参数绑定传递，确保查询安全性。
• 适配数据库特性：选择方案时需结合使用的数据库类型，优先考虑兼容性和性能。

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