【MyBatis】在 ORDER BY 中的 #{} 占位符的问题

前言

在使用 Mybatis 时，有需求前端指定按某一列进行排序，于是尝试在 Order by 子句中使用 #{} 实现动态地改变排序的列名和排序方向，但是 SQL 的结果不符合预期。经查阅资料了解到 MyBatis 在预处理 SQL 语句时，会将 #{} 占位符替换为参数值的预处理形式，所以不可以在Order by 中使用 #{} 占位符，遂记录该问题和解决方案。

错误的代码

SELECT * FROM `table`

ORDER BY #{prop} #{order}

解决方案

方案一：使用 ${} 代替 #{}

SELECT * FROM `table`

ORDER BY ${prop} ${order}

这里使用了 ${prop} 而不是 #{prop}，因为 ${prop} 会直接替换为参数值，不进行任何转义或预处理，这样就解决了 #{prop} 被替换为参数值的预处理形式的问题。但是这种方式会导致SQL注入风险，在调用之前务必确认 prop 和 order 的值合理。

方案二：使用 <choose> 标签

使用 <choose> 和 <when> 实现动态SQL:

SELECT * FROM `table`

<if test="prop != null and prop != ''">
ORDER BY
    <choose>
        <when test="prop == 'priority'">
            priority
        </when>
        <when test="prop == 'task_responsible'">
            task_responsible
        </when>
        <when test="prop == 'status'">
            status
        </when>
        <when test="prop == 'end_time'">
            end_time
        </when>
        <!-- 可以添加更多的 when 条件来支持更多的列 -->
        <otherwise>
        </otherwise>
    </choose>
    <choose>
        <when test="order == 'DESC'">
            DESC,
        </when>
        <otherwise>
            ASC,
        </otherwise>
    </choose>
</if>

使用when标签根据prop和order的值将固定的参数写入SQL中，能够避免SQL注入攻击的风险。

但是这种方式在可选列较多、数据库结构变动频繁时（比如列名）编写和维护会比较复杂。