MySQL小知识点-自定义排序FIELD函数

问题背景

今天需要处理一个问题，需求方是给了我一份Excel文档，然后让我去数据库里去查一下几个属性字段，然后补充到Excel上面，我是把Excel中的标识列拿出来，然后拼接到in的括号里面，然后再去表中查询，例如select a1,a2,a_code from a_test where a_code in ('牛','猪','狗')，但是这样查询出来结果跟Excel列的顺序对应不起来了，我就不能直接把结果复制出来然后黏贴到Excel中。

使用MySQL自定义排序

后面通过查资料得知可以通过MySQL的自定义函数来解决这个问题，就是使用FIELD函数，通过改造原来的SQL语句，例如

select a1,a2,a_code from a_test where a_code in ('牛','猪','狗') 
order by field(
    a_code,
    '牛','猪','狗');

这样就能够解决问题，按照输入的顺序输出，解决我的问题。

FIELD函数详解

再来拓展一下这个函数的使用，增加自己的一点小知识。

在 MySQL 中，FIELD() 函数是一个非常实用但常被忽略的函数。它的核心作用是返回某个值在参数列表中第一次出现的位置（索引） 。如果该值不在列表中，则返回 0。

这个函数常用于自定义排序（Custom Sorting），即不按照字母或数字顺序，而是按照你指定的特定顺序对数据进行排序。

基本语法

FIELD(str, str1, str2, str3, ...)

• str: 你要查找的目标值（通常是表中的某一列）。
• str1, str2, ... : 一个值列表。
• 返回值:
  • 如果 str 等于 str1，返回 1。
  • 如果 str 等于 str2，返回 2。
  • 以此类推。
  • 如果 str 不在列表中，或者 str 为 NULL，返回 0。

因为存在如果匹配不到返回0，就会导致排到最前面，所以我们还要考虑0的问题，我是使用in函数所以避免了，我还可以使用> 0 方式，例如

select a1,a2,a_code from a_test
having field(
    a_code,
    '牛','猪') > 0
order by field(
    a_code,
    '牛','猪');

这样也能输出圈定的几个排序值，这里面只排序牛、猪。

也支持多字段排序

select a1,a2,a_code from a_test 
having field(
    a_code,
    '牛','猪') > 0
order by field(
    a_code,
    '牛','猪'),
    a1 desc;

注意事项

1. 值匹配严格性：FIELD() 函数区分字符串的大小写（取决于数据库的字符集排序规则，如 utf8_general_ci 不区分，utf8_bin 区分），需确保列表值与字段值完全一致（包括标点、空格）。
2. 性能说明：FIELD() 函数适用于 “指定少量值排序” 的场景，若排序值过多（如上千个），建议通过关联字典表 + 排序字段实现，避免性能损耗。