Mysql之按照指定顺序排序

问题场景

    select
    <include refid="Base_Column_List" />
    from student
    <where>
      id in
      <foreach collection="ids" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
      and deleted = 0 
    </where>

写sql用到in 的时，有时候传入的ids是有特定的顺序的，然而一般查询后返回的结果会丢失掉这种顺序性，例如

select * from student where id in (2, 3, 4, 1)

结果为

解决方案

当然我们可以在业务代码中进行后续矫正，但如果要在sql中保持原本的顺序性不变，可以用到MySQL自定义排序函数FIELD()

select * from student where id in (2, 3, 4, 1) ORDER BY FIELD(id, 2, 3, 4, 1)

结果

mybatis里加上order by field即可

    select
    <include refid="Base_Column_List" />
    from student
    <where>
      id in
      <foreach collection="ids" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
      and deleted = 0 
    </where>
    ORDER BY FIELD
      <foreach collection="ids" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>

函数FIELD(value,str1,str2,str3,…)

原理： 返回value 在列表(s1,s2…)中的位置下标，等于str1返回1，等于str2返回2，若都没命中则返回0

SELECT FIELD(5, 5, 4, 3);  #结果为1

SELECT FIELD("c", "a", "b", "c", "d", "e");  #结果为3

SELECT FIELD("f", "a", "b", "c", "d", "e");   #结果为0

所以order by field()如果用于 结果集 不包含于 排序集的情况，结果可能会和我们期望的不一样，例如

select * from student  ORDER BY FIELD(id, 5, 4, 3) 

结果为

带入field函数功能，其实就很好理解了，默认升序，各数据field得分如下：

性能： 对性能有一定要求的话肯定得避函数而远之，根据自己的情况选择

explain select * from student ORDER BY FIELD(id, 2, 3, 4, 1); 
# ALL

explain select * from student where id in (2, 3, 4, 1)ORDER BY FIELD(id, 2, 3, 4, 1);
# range

ELT()函数和FIELD()函数类似，大家有兴趣也可以一块学习下。