MySQL 中 FIELD() 自定义排序详解

目录

大佬地址：

MySQL 中 FIELD() 自定义排序

---

在 MySQL 中，你可以使用 ORDER BY FIELD() 来自定义排序顺序。这个函数允许你指定字段的自定义排序顺序，而不是默认的升序或降序排序。

函数使用格式如下：

order by field(field_name, str1, str2, str3, str4……)

• field_name：字段名称
• str1，str2，str3，str4：待比较值

字段field_name按照字符串str1，str2，str3，str4的顺序返回查询到的结果集。如果表中field_name字段值不存在于str1，str2，str3，str4中的记录，放在结果集最前面返回。

• 表数据如下：

mysql> select * from user;
+----+----------+------+-------+
| id | name     | age  | class |
+----+----------+------+-------+
|  1 | zhangsan |   11 | a1    |
|  2 | zhangsan |   22 | a2    |
|  3 | lisi     |   33 | a3    |
|  4 | lisi     |   44 | a4    |
|  5 | wangwu   |   55 | a5    |
|  6 | zhangsan |   66 | a6    |
|  7 | zhangsan |   34 | a2    |
|  8 | taoqi    |   45 | a6    |
|  9 | lisi     |   59 | a8    |
| 10 | lisi     |   45 | a3    |
| 11 | nbclass  |   45 | a7    |
| 12 | wangwu   |   77 | a0    |
+----+----------+------+-------+
12 rows in set (0.00 sec)

查询user表，字段name值按'lisi', 'zhangsan', 'wangwu'顺序排序返回结果。不在排序字段值中的内容，如：'taoqi'、'nbclass' 放在最前面返回，name字段值相同按照主键的顺序排序。

mysql> select * from user order by field(name, 'lisi', 'zhangsan', 'wangwu');
+----+----------+------+-------+
| id | name     | age  | class |
+----+----------+------+-------+
|  8 | taoqi    |   45 | a6    |
| 11 | nbclass  |   45 | a7    |
|  3 | lisi     |   33 | a3    |
|  4 | lisi     |   44 | a4    |
|  9 | lisi     |   59 | a8    |
| 10 | lisi     |   45 | a3    |
|  1 | zhangsan |   11 | a1    |
|  2 | zhangsan |   22 | a2    |
|  6 | zhangsan |   66 | a6    |
|  7 | zhangsan |   34 | a2    |
|  5 | wangwu   |   55 | a5    |
| 12 | wangwu   |   77 | a0    |
+----+----------+------+-------+
12 rows in set (0.00 sec)

查询user表，字段name值按'wangwu','zhangsan','lisi' desc 降序来排序。倒序时，按照'wangwu','zhangsan','lisi'的顺序逆向排序，不在'wangwu','zhangsan','lisi'中的记录放到最后，如：'nbclass'、'taoqi'，name值相同按主键顺序排列。

mysql> select * from user order by field(name, 'wangwu', 'zhangsan', 'lisi') desc;
+----+----------+------+-------+
| id | name     | age  | class |
+----+----------+------+-------+
|  3 | lisi     |   33 | a3    |
|  4 | lisi     |   44 | a4    |
|  9 | lisi     |   59 | a8    |
| 10 | lisi     |   45 | a3    |
|  1 | zhangsan |   11 | a1    |
|  2 | zhangsan |   22 | a2    |
|  6 | zhangsan |   66 | a6    |
|  7 | zhangsan |   34 | a2    |
|  5 | wangwu   |   55 | a5    |
| 12 | wangwu   |   77 | a0    |
|  8 | taoqi    |   45 | a6    |
| 11 | nbclass  |   45 | a7    |
+----+----------+------+-------+
12 rows in set (0.00 sec)

查询user表，字段name值按'wangwu','zhangsan' desc 降序来排序，且字段 class 升序排序。倒序时，按照'wangwu','zhangsan'的顺序逆向排序且class字段升序排序，根据如下结果可以看出'wangwu','zhangsan'符合条件放在前面返回，且id字段按升序排序，不在'wangwu','zhangsan'中的记录放到最后，id字段按升序排序，对比id desc降序的即可比对结果。

mysql> select * from user order by field(name, 'wangwu', 'zhangsan') desc, id asc;
+----+----------+------+-------+
| id | name     | age  | class |
+----+----------+------+-------+
|  1 | zhangsan |   11 | a1    |
|  2 | zhangsan |   22 | a2    |
|  6 | zhangsan |   66 | a6    |
|  7 | zhangsan |   34 | a2    |
|  5 | wangwu   |   55 | a5    |
| 12 | wangwu   |   77 | a0    |
|  3 | lisi     |   33 | a3    |
|  4 | lisi     |   44 | a4    |
|  8 | taoqi    |   45 | a6    |
|  9 | lisi     |   59 | a8    |
| 10 | lisi     |   45 | a3    |
| 11 | nbclass  |   45 | a7    |
+----+----------+------+-------+
12 rows in set (0.00 sec)

mysql> select * from user order by field(name, 'wangwu', 'zhangsan') desc, id desc;
+----+----------+------+-------+
| id | name     | age  | class |
+----+----------+------+-------+
|  7 | zhangsan |   34 | a2    |
|  6 | zhangsan |   66 | a6    |
|  2 | zhangsan |   22 | a2    |
|  1 | zhangsan |   11 | a1    |
| 12 | wangwu   |   77 | a0    |
|  5 | wangwu   |   55 | a5    |
| 11 | nbclass  |   45 | a7    |
| 10 | lisi     |   45 | a3    |
|  9 | lisi     |   59 | a8    |
|  8 | taoqi    |   45 | a6    |
|  4 | lisi     |   44 | a4    |
|  3 | lisi     |   33 | a3    |
+----+----------+------+-------+
12 rows in set (0.00 sec)