如何编写带有复杂过滤条件的存储过程_通过组合可选参数与WHERE子句-SQL-PHP中文网

MySQL存储过程处理可选WHERE条件的三种主流方式：一是字符串拼接动态SQL（易注入、缓存失效）；二是用ps_name IS NULL OR name = ps_name统一条件（简洁但可能影响索引）；三是IF分支分别写查询（逻辑清晰、索引友好）。

如何编写带有复杂过滤条件的存储过程_通过组合可选参数与where子句

直接拼接 SQL 字符串最省事，但容易被注入、执行计划缓存失效、调试困难。正确做法是用 IS NULL 或 COALESCE 控制条件生效与否，让优化器能复用执行计划。

常见错误是写成这样：WHERE (@name IS NULL OR name = @name)——看起来对，但 SQL Server 很可能放弃索引，走全表扫描，尤其当 @name 为 NULL 时，整个谓词变成 1=1 OR name = ...，优化器难以准确估算行数。

对高基数字段（如 user_id），优先用 AND (@id IS NULL OR id = @id)，配合 OPTION (RECOMPILE) 让每次编译都适配实际参数
对低基数或需要范围过滤的字段（如 status、created_date），拆成独立分支 + IF，避免单条语句里混杂太多 OR
所有字符串参数必须加 LEN(@param) > 0 判断，空字符串和 NULL 是两回事，'' = NULL 永远为 false

当一个存储过程被反复调用，而传入的参数差异极大（比如一次查 10 行，一次查 10 万行），SQL Server 会缓存第一次生成的执行计划，后续调用强行复用，导致性能雪崩。加 OPTION (RECOMPILE) 能让每次执行都重新编译，拿到当前参数下的最优计划。

代价是编译开销略增，但对 OLTP 场景中响应时间敏感的查询，这比跑错计划强得多。

只在 WHERE 子句含多个可选参数且数据分布不均时启用，不是所有存储过程都要加
不能加在语句末尾再套一层 EXEC sp_executesql，那会绕过计划缓存机制但失去类型安全
示例写法：SELECT * FROM orders WHERE status = ISNULL(@status, status) AND user_id = ISNULL(@user_id, user_id) OPTION (RECOMPILE);

PostgreSQL 没有 OPTION (RECOMPILE)，但它的规划器对 OR 和 IS NULL 的处理更友好，尤其是配合 USING 参数化查询时。不过仍要警惕 WHERE col = $1 OR $1 IS NULL 在某些版本中无法使用索引的问题。

推荐用 CASE 显式控制：例如 WHERE ($1::text IS NULL OR name ILIKE $1)，注意类型强制转换避免隐式转换失败
对日期范围，别写 created_at BETWEEN $2 AND $3 OR ($2 IS NULL AND $3 IS NULL)，改用 ($2 IS NULL OR created_at >= $2) AND ($3 IS NULL OR created_at
如果过滤字段上有函数索引（如 lower(name)），参数也得同步用 lower($1)，否则索引失效

开发时图快用 CONCAT 拼出完整 SQL 再 EXEC，上线后大概率遇到注入、权限失控、日志脱敏失效、监控指标错乱等问题。

更隐蔽的坑是：拼接后的语句每次都是新文本，SQL Server 不会重用执行计划，CPU 编译压力陡增；PostgreSQL 的 PREPARE 也无法复用，连接池里大量相似语句占满内存。

拼接前必须对每个输入做白名单校验（比如状态值只能是 'active'/'inactive'），不能只靠 REPLACE 过滤单引号
若真需拼接（如表名动态），必须用 QUOTENAME()（SQL Server）或 format('%%I', $1)（PostgreSQL），绝不用字符串插值
所有拼接路径必须经过审计工具扫描，人工 review 不能跳过

实际中最容易被忽略的是参数默认值与数据库默认行为的冲突——比如把 @status 默认设为 ''，但业务上空字符串和“不限制”语义不同，结果查出一堆脏数据。这类逻辑必须在存储过程开头就显式判断并归一化。