MySQL 8.0+ 和 SQL Server 动态排序必须用白名单 CASE 分支展开字段,禁用 PREPARE 拼接;WHERE 过滤须用 (@param IS NULL OR col = @param) 结构保索引,禁用 IFNULL/COALESCE 包裹列。

MySQL 8.0+ 和 SQL Server 中,动态排序不能靠拼接字段名实现,可选过滤也不能用 ISNULL() 或 COALESCE() 包裹列——这两种写法都会让优化器放弃索引 Seek,直接走全表 Scan。
ORDER BY 动态字段必须用白名单 CASE 展开
MySQL 和 SQL Server 都禁止在 ORDER BY 中直接使用变量字段名(如 @sort_col),PREPARE 拼接也不安全、不可缓存。唯一合规路径是把所有允许排序的字段硬编码进多个 CASE 分支,参数只控制分支跳转。
-
@sort_field类型设为VARCHAR(32),值限定在白名单内(如'id'、'name'、'created_at') - 每个字段对应一个独立
CASE WHEN @sort_field = 'xxx' THEN xxx END,所有分支返回值类型必须一致(数值型统一CAST,字符串型统一CONVERT(VARCHAR, ...)) - 升序/降序不能传
'ASC'/'DESC'字符串,SQL Server 要用@sort_desc BIT控制正负号(仅限数值),MySQL 更稳妥是拆成两个CASE:一个带ASC,一个带DESC,并列写在ORDER BY后 - 别在
CASE里调用NOW()、NEWID()等非确定性函数,否则每行重算,执行计划失效
WHERE 可选条件必须用 OR + IS NULL 结构
想让 @status 为空时不参与过滤,写成 status = IFNULL(@status, status) 看似简洁,实则会让 MySQL/SQL Server 放弃索引。正确结构是让优化器能“短路识别”真实条件。
- 每个可选参数单独一行:
WHERE (@status IS NULL OR status = @status) AND (@min_score IS NULL OR score >= @min_score) - 字段区分度低时(如
status只有 3 个值),执行计划可能退化为 Index Scan;SQL Server 可加OPTION (RECOMPILE),MySQL 8.0.30+ 可设optimizer_switch='condition_fanout_filter=off' - 避免嵌套
CASE WHEN在WHERE里(如CASE WHEN @filter = 'active' THEN status END = 'active'),这强制逐行计算,无法下推索引 - 若条件分支少且逻辑差异大(如按租户查 vs 按角色查),用
UNION ALL拆分比堆OR更易命中索引
MySQL 8.0+ 存储过程里不能用 PREPARE 拼 ORDER BY
哪怕你手动校验了 @sort_field 是否在白名单里,PREPARE + EXECUTE 拼出的 ORDER BY 仍无法被查询缓存复用,每次执行都触发新编译,性能波动大。这不是写法问题,是 MySQL 的执行计划机制决定的。
- PREPARE 只接受完整 SQL 字符串,不支持参数化列名或排序方向——连
WHERE值都得拼进去,更别说字段名 - 所有输入都必须视为不可信源处理,哪怕来自内部系统;白名单校验漏一个字符(比如多空格、大小写混用),就等于开了注入口
- 如果真需要运行时字段名(如日志表按月分表),应改用分区表 +
INFORMATION_SCHEMA查元数据,而非拼 SQL
复杂点常被忽略:NULL 值在 CASE 排序中的位置
当某个排序字段含大量 NULL,而你又用了 CASE WHEN @sort_field = 'name' THEN name END ASC,这些 NULL 行会被排在最前面(DESC 则在最后)。这不是 bug,是 SQL 标准行为,但业务常要求“NULL 当作最小值”或“忽略”,这时必须显式补 COALESCE(name, '') 或 IFNULL(name, 'zzzz') ——注意填充值类型和长度要与原字段兼容,否则隐式转换会报错或打乱顺序。











