怎样在SQL存储过程中编写动态多条件组合查询-SQL-PHP中文网

最安全高效的sql动态where写法是使用(@param is null or column = @param)模式。它避免动态拼接、防止注入、利于索引复用；跨字段or必须拆union all+option(recompile)；各数据库需严格区分值绑定与字段名白名单校验。

怎样在sql存储过程中编写动态多条件组合查询

SQL Server 存储过程里别拼 `@WhereStr`，用 `(@param IS NULL OR column = @param)` 模式

这是最轻量、最安全、也最容易被索引覆盖的写法。它不依赖动态 SQL 解析，也不触发参数嗅探恶化，执行计划能稳定复用。

常见错误现象：WHERE 后硬拼字符串，比如 ' AND status = ''' + @status + ''''——值含单引号就报错，且优化器无法识别 SARG 性，常退化为 Clustered Index Scan。

每个条件都写成 (@user_id IS NULL OR user_id = @user_id)，NULL 表示“不参与过滤”
字符串字段注意空串处理：(@name = '' OR name LIKE '%' + @name + '%')，避免 @name = NULL 时整个条件失效
日期范围慎用 IS NULL：若传入 NULL 是想查全部，就用 (@start_date IS NULL OR created_at >= @start_date)；若传空字符串，需先 SET @start_date = NULLIF(@start_date, '')
该模式对复合索引友好，但要求条件字段在索引中位置合理（如索引是 (status, user_id)，那 status 条件必须存在，否则可能跳过索引）

跨字段 `OR` 场景下必须拆成 `UNION ALL` + `OPTION (RECOMPILE)`

当业务明确要求“查 status=1 或 customer_id=101 或 amount > 500”的任意匹配时，(@s IS NULL OR status = @s) OR (@c IS NULL OR customer_id = @c) 这种写法会让优化器放弃所有索引，直接 Table Scan。

根本原因是 SQL Server 无法估算多分支 OR 的选择率，加上参数嗅探固化低效计划。

每个分支只保留一个等值/范围条件，且该字段必须是对应索引的最左列（如查 customer_id，索引得是 (customer_id) 或 (customer_id, status)）
所有 SELECT 列表必须完全一致（字段名、类型、顺序），否则 UNION ALL 报错
每个子查询末尾加 OPTION (RECOMPILE)，防止缓存错的执行计划
跳过空值分支：用 IF 判断，不要生成 SELECT ... WHERE customer_id = NULL 这种恒假语句

MySQL 存储过程中必须用 `PREPARE + EXECUTE USING`，禁用 `CONCAT()` 拼值

MySQL 不支持 sp_executesql 那样的参数化执行，但 EXECUTE USING 能绑定变量，是唯一防注入、保索引的路径。

错误做法是 CONCAT('WHERE status = ''', in_status, '''')——用户输 O'Reilly 就语法爆炸，且字段被隐式转成字符串，索引失效。

所有条件判断独立写：IF in_user_id IS NOT NULL THEN SET @sql = CONCAT(@sql, ' AND user_id = ?');
占位符 ? 出现顺序必须和 USING 后变量顺序严格一致
最终 EXECUTE stmt USING @user_id, @status, @min_amount;，不能漏、不能换序
字段名仍需白名单校验（如用 CASE in_field WHEN 'status' THEN 'status = ?'），不可直接拼接用户输入

PostgreSQL 动态 WHERE 必须用 `format()` + `USING`，字段名要硬编码或白名单

PostgreSQL 支持 EXECUTE ... USING 绑定值，但字段名（如 WHERE <field> = $1</field>）无法参数化，拼错就是 SQL 注入口。

你看到的 EXECUTE 'SELECT * FROM t WHERE ' || in_field || ' = $1' USING in_value 是高危写法，哪怕加了 quote_ident() 也难防逻辑绕过。

字段名必须显式限定：IF in_filter = 'status' THEN cond := 'status = $1'; ELSIF in_filter = 'amount' THEN cond := 'amount > $1'; END IF;
主 SQL 用 format('SELECT * FROM orders WHERE %s', cond) 拼接，保证结构可控
USING 只传值，不传字段、不传操作符、不传表名
NULL 条件默认跳过，避免生成 WHERE status = NULL（这永远为 UNKNOWN）

实际最难的不是写法，是判断什么时候该用布尔收敛、什么时候必须拆 UNION ALL——关键看 OR 是否跨字段。同一字段上的 OR（如 status IN ('a','b','c')）走 IN 就行；一旦涉及 status = ? OR user_id = ?，就得切分支。这个边界不厘清，索引就一直在梦游。