MySQL自定义排序函数FIELD()介绍

最新推荐文章于 2024-09-22 13:58:28 发布

zuojunyuan

最新推荐文章于 2024-09-22 13:58:28 发布

阅读量422

点赞数

分类专栏： MySQL 文章标签：函数

原文链接：https://blog.csdn.net/lxz3000/article/details/6173873

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL可以通过field()函数自定义排序

格式：
field(value,str1,str2,str3,str4)

value与str1、str2、str3、str4比较，返回1、2、3、4，如遇到null或者不在列表中的数据则返回0. 这个函数好像Oracle中没有专门提供（也可能是我没有用到），不过自己实现这样一个函数还是比较简单的。

mysql> select * from driver_log order by name;

+--------+-------+------------+-------+
| rec_id | name  | trav_date  | miles |
+--------+-------+------------+-------+
|      1 | Ben   | 2006-08-30 |   152 |
|      9 | Ben   | 2006-09-02 |    79 |
|      5 | Ben   | 2006-08-29 |   131 |
|      8 | Henry | 2006-09-01 |   197 |
|      6 | Henry | 2006-08-26 |   115 |
|      4 | Henry | 2006-08-27 |    96 |
|      3 | Henry | 2006-08-29 |   300 |
|     10 | Henry | 2006-08-30 |   203 |
|      7 | Suzi  | 2006-09-02 |   502 |
|      2 | Suzi  | 2006-08-29 |   391 |
+--------+-------+------------+-------+

10 rows in set (0.00 sec)

mysql> select * from driver_log order by field(name,'Suzi','Ben','Henry');

+--------+-------+------------+-------+
| rec_id | name  | trav_date  | miles |
+--------+-------+------------+-------+
|      2 | Suzi  | 2006-08-29 |   391 |
|      7 | Suzi  | 2006-09-02 |   502 |
|      1 | Ben   | 2006-08-30 |   152 |
|      9 | Ben   | 2006-09-02 |    79 |
|      5 | Ben   | 2006-08-29 |   131 |
|      8 | Henry | 2006-09-01 |   197 |
|      6 | Henry | 2006-08-26 |   115 |
|      4 | Henry | 2006-08-27 |    96 |
|      3 | Henry | 2006-08-29 |   300 |
|     10 | Henry | 2006-08-30 |   203 |
+--------+-------+------------+-------+

10 rows in set (0.00 sec)

mysql> select * from driver_log order by field(name,'Suzi','Ben');

+--------+-------+------------+-------+
| rec_id | name  | trav_date  | miles |
+--------+-------+------------+-------+
|     10 | Henry | 2006-08-30 |   203 |
|      8 | Henry | 2006-09-01 |   197 |
|      6 | Henry | 2006-08-26 |   115 |
|      4 | Henry | 2006-08-27 |    96 |
|      3 | Henry | 2006-08-29 |   300 |
|      7 | Suzi  | 2006-09-02 |   502 |
|      2 | Suzi  | 2006-08-29 |   391 |
|      5 | Ben   | 2006-08-29 |   131 |
|      9 | Ben   | 2006-09-02 |    79 |
|      1 | Ben   | 2006-08-30 |   152 |
+--------+-------+------------+-------+

10 rows in set (0.00 sec)

mysql> select * from driver_log order by field(name,'Suzi','Ben') desc;

+--------+-------+------------+-------+
| rec_id | name  | trav_date  | miles |
+--------+-------+------------+-------+
|      1 | Ben   | 2006-08-30 |   152 |
|      9 | Ben   | 2006-09-02 |    79 |
|      5 | Ben   | 2006-08-29 |   131 |
|      2 | Suzi  | 2006-08-29 |   391 |
|      7 | Suzi  | 2006-09-02 |   502 |
|      8 | Henry | 2006-09-01 |   197 |
|      6 | Henry | 2006-08-26 |   115 |
|      4 | Henry | 2006-08-27 |    96 |
|      3 | Henry | 2006-08-29 |   300 |
|     10 | Henry | 2006-08-30 |   203 |
+--------+-------+------------+-------+

10 rows in set (0.00 sec)

20180911更新内容：

我们都非常习惯通过 MySQL 的 IN 函数来查询特定集合的数据，比如为了在 books 表中找出李雷、韩梅梅和安华写的书，我们可以有如下的 SQL：

SELECT * FROM books WHERE `books`.`author` IN ('李雷','韩梅梅','安华');

数据库返回如下结果：

author	title
安华	暴走漫画
李雷	蓝色生死恋
韩梅梅	冰与火之歌
韩梅梅	天国的阶梯
李雷	这个杀手不太冷
韩梅梅	阿甘正传

虽然这样确实能够返回所有李雷、韩梅梅和安华写过的书，但是返回的数据的排序方式是默认按照数据在数据库中的存储顺序，假如我们需要的返回结果是同时按照 IN 查询条件里边的参数顺序来排序呢？这个时候我们就需要利用到 MySQL FIELD 这个函数了，FIELD 函数本来是 MySQL 提供用来查询某一个字符串在给定字符串元组中的索引位置的，比如这个官方例子：

SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); # -> 2

如果将其应用在 ORDER BY 排序条件中，就可以根据指定字段的值在给定参数列表中的索引数值，进而将查询结果按照参数列表排序了

SELECT * FROM books WHERE `books`.`author` IN ('李雷','韩梅梅','安华') ORDER BY FIELD(author, '李雷','韩梅梅','安华');

author	title
李雷	蓝色生死恋
李雷	这个杀手不太冷
韩梅梅	冰与火之歌
韩梅梅	天国的阶梯
韩梅梅	阿甘正传
安华	暴走漫画

可以发现，这一次，我们得到的结果就是按照条件参数列表 ‘李雷’,’韩梅梅’,’安华’ 进行排序后得到了。

应用层面的思考

1. 兼容性
本文提到的 FIELD 函数，毕竟只是 MySQL 数据库内置提供的一种函数，除非你非常明确你的项目就是只用 MySQL 数据库，否则，你的 SQL 代码在未来迁移到其他数据库的过程中就会遇到语法兼容性问题（只是 PostgreSQL 数据库不支持 FIELD）。

2. 性能问题
我们都知道，数据库在进行 ORDER BY 排序的时候，除非它是按照某个已经存在索引的键的值进行排序，否则数据库则需要通过计算 ORDER BY 中表达式的值并且按照查询结果建立新的临时表，这个过程会带来额外的时间开销跟内存开销，对数据库本身就是一种性能负担。这样的方式在单一数据库多个数据库客户端连接的时候，可能对数据库造成太大负担。

3. 与应用层代码的结合
尽管使用 FIELD 函数可能带来兼容性以及性能方面的隐患，但是 FIELD 的使用并非全是有损之处。

比如在与 Ruby 的 active_record 结合时，这种通过数据库直接完成排序等 SQL 语句可以方便我们构建 ActiveRecord::Relation 对象，因为我们不再需要先将查询结果集从内存中转为数组排序，再进行二次查询，可以帮助我们减少 N+1 查询问题，后者也是常见的影响数据库服务器性能的现象之一。除此之外，这样的写法也可以有效地帮助我们简化代码，保持代码简洁。

但是在不需要对数据进行二次查询或者查询数据量太大的情况下，我反而建议可以通过 Ruby 的 Array#sort_by 方法对数据进行排序，这样的话，排序的任务就转移给了客户端代码，排序任务的压力就自然分散，减轻了服务器端的压力。

总结

FIELD 函数结合 ORDER BY 可以帮助我们将查询结果集按照参数列表顺序返回；
FIELD 函数结合 ORDER BY 的方式可以帮助我们在数据库层面完成排序，简化了业务代码逻辑；
FIELD 函数结合 ORDER BY 可能带来 SQL 兼容性以及性能方面的问题；
在确认项目数据库不大可能为 MySQL 之外的数据库的前提下，查询数据量少或者需要保持业务代码简洁的场景下，我建议可以采用 FIELD 函数排序；而在数据量庞大的情况下，或者不大可能出现大量 N+1 查询的情况下，我建议可以采用先在数据库中查询数据集（只查询 IN 条件，不排序）再到内存中通过业务代码排序(比如 Ruby 的 Array#sort_by)的方式。