我们都非常习惯通过 MySQL 的 IN 函数来查询特定集合的数据,比如为了在 books 表中找出李雷、韩梅梅和安华写的书,我们可以有如下的 SQL(可以通过 SQL Fiddle查看示例):
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
排序条件中,就可以根据指定字段的值在给定参数列表中的索引数值,进而将查询结果按照参数列表排序了(示例请访问 SQL Fiddle):
SELECT * FROM books WHERE `books`.`author` IN ('李雷','韩梅梅','安华') ORDER BY FIELD(author, '李雷','韩梅梅','安华');
这一次,返回的结果则是:
author | title |
---|---|
李雷 | 蓝色生死恋 |
李雷 | 这个杀手不太冷 |
韩梅梅 | 冰与火之歌 |
韩梅梅 | 天国的阶梯 |
韩梅梅 | 阿甘正传 |
安华 | 暴走漫画 |
可以发现,这一次,我们得到的结果就是按照条件参数列表 '李雷','韩梅梅','安华'
进行排序后得到了。
应用层面的思考
-
兼容性
本文提到的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)的方式。