MySQL使用FIELD()函数自定义排序

最新推荐文章于 2024-06-17 14:39:15 发布

初默

最新推荐文章于 2024-06-17 14:39:15 发布

阅读量1.3k

点赞数 1

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

本文链接：https://blog.csdn.net/qq_34292479/article/details/88998048

版权

MySQL 专栏收录该内容

4 篇文章 3 订阅

订阅专栏

自定义排序函数FIELD()的用法

在日常开发过程中，排序是经常用到的，有时候有这样的需求：需要在查询结果中根据某个字段的具体值来排序

如下例子：

表名 tb

id	name	age	class
1	iris	16	a0
2	iris	15	a1
3	seiki	15	a2
4	seiki	16	a2
5	xuding	16	a1
6	Ben	16	a0
7	Ben	16	a3
8	xuding	16	a3

上述这张表，假如我们想按照’seiki’,‘iris’,来排序。也就是name=‘seiki’,‘iris’,'Ben’的来排序，这是要怎么实现呢？这就要FIELD()函数自定义排序。

语法

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

含义

1、FIELD()函数是将其应用到ORDER BY中的；
2、str指的是字段名字，str与str1、str2、str3…比较；
3、字段str按照字符串str1、str2、str3的顺序返回查询到的结果集；
4、如果表中str字段值不存在于str1、str2、str3中的记录，放在结果集最前面返回。

下面我们就使用FIELD()函数对其按照自定义的的方式进行排序

使用

mysql>SELECT * FROM tb WHERE ORDER BY FIELD(name,'seiki','iris','Ben');

mysql中默认的排序为升序，既ASC，如果想要降序就需要使用DESC。
使用上述SQL查询的结果为：

id	name	age	class
5	xuding	16	a1
8	xuding	16	a3
3	seiki	15	a2
4	seiki	16	a2
1	iris	16	a0
2	iris	15	a1
6	Ben	16	a0
7	Ben	16	a3

可以发现，我们得到的结果就是按照条件 ‘seiki’,‘iris’,‘Ben’ 进行排序后得到了。

应用层面的思考

兼容性
本文提到的 FIELD 函数，毕竟只是 MySQL 数据库内置提供的一种函数，除非你非常明确你的项目就是只用 MySQL 数据库，否则，你的 SQL 代码在未来迁移到其他数据库的过程中就会遇到语法兼容性问题（只是 PostgreSQL 数据库不支持 FIELD）。
性能问题
我们都知道，数据库在进行 ORDER BY 排序的时候，除非它是按照某个已经存在索引的键的值进行排序，否则数据库则需要通过计算 ORDER BY 中表达式的值并且按照查询结果建立新的临时表，这个过程会带来额外的时间开销跟内存开销，对数据库本身就是一种性能负担。这样的方式在单一数据库多个数据库客户端连接的时候，可能对数据库造成太大负担。
与应用层代码的结合
尽管使用 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)的方式。