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

朱雀 2022-02-24 08:14 352阅读 0赞

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

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

###### 如下例子： ######

表名 tb

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>age</th> 
   <th>class</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>iris</td> 
   <td>16</td> 
   <td>a0</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>iris</td> 
   <td>15</td> 
   <td>a1</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>seiki</td> 
   <td>15</td> 
   <td>a2</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>seiki</td> 
   <td>16</td> 
   <td>a2</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>xuding</td> 
   <td>16</td> 
   <td>a1</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Ben</td> 
   <td>16</td> 
   <td>a0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Ben</td> 
   <td>16</td> 
   <td>a3</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>xuding</td> 
   <td>16</td> 
   <td>a3</td> 
  </tr> 
 </tbody> 
</table>

上述这张表，假如我们想按照’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查询的结果为：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>age</th> 
   <th>class</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>5</td> 
   <td>xuding</td> 
   <td>16</td> 
   <td>a1</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>xuding</td> 
   <td>16</td> 
   <td>a3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>seiki</td> 
   <td>15</td> 
   <td>a2</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>seiki</td> 
   <td>16</td> 
   <td>a2</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>iris</td> 
   <td>16</td> 
   <td>a0</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>iris</td> 
   <td>15</td> 
   <td>a1</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Ben</td> 
   <td>16</td> 
   <td>a0</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Ben</td> 
   <td>16</td> 
   <td>a3</td> 
  </tr> 
 </tbody> 
</table>

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

#### 应用层面的思考 ####

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)的方式。