MySQL 自定义ORDER BY忽略“the”

在MySQL中，经常需要对结果集进行排序。一般情况下，我们可以通过ORDER BY语句来对结果集进行排序。但是有时候，我们希望对结果集进行自定义排序，例如忽略英语中的冠词“the”，把“The Beatles”放在“Beatles”前面。本文将介绍如何在MySQL中自定义ORDER BY语句，以忽略“the”。

阅读更多：MySQL 教程

问题描述

假设我们有一个存储音乐家信息的表artist，其中有一个字段name记录音乐家的姓名。我们希望对该表按照音乐家姓名的首字母进行排序，并且忽略冠词“the”。如果直接使用ORDER BY name语句，则按照字典序排序，结果是不符合要求的。例如，如果表中有以下数据：

id	name
1	The Beatles
2	Rolling Stones
3	The Who
4	Pink Floyd
5	Led Zeppelin

按照字典序排序的结果是：

id	name
4	Pink Floyd
2	Rolling Stones
1	The Beatles
3	The Who
5	Led Zeppelin

这个结果并不是我们期望的排序结果，因为我们希望忽略“the”，把“The Beatles”放在“Beatles”前面。

解决方案

为了实现自定义排序，我们需要用到MySQL的字符串处理函数以及ORDER BY子句中的CASE语句。下面分别介绍这两方面的内容。

字符串处理函数

MySQL提供了一些字符串处理函数，用于对字符串进行处理。这里我们需要用到的字符串处理函数是SUBSTRING()和TRIM()。

SUBSTRING()

SUBSTRING()函数可以从一个字符串中提取子串。其语法如下：

SUBSTRING(str, pos, len)

其中，str表示要提取子串的字符串，pos表示要提取的子串的起始位置，len表示要提取的子串的长度。如果len被省略，则子串将从pos一直提取到字符串末尾。

例如，SUBSTRING('Hello, world!', 1, 5)的返回结果是'Hello'。

TRIM()

TRIM()函数可以去掉一个字符串的首尾空格。其语法如下：

TRIM([BOTH | LEADING | TRAILING] [remstr] FROM str)

其中，BOTH表示去掉字符串两端的空格，LEADING表示去掉字符串开头的空格，TRAILING表示去掉字符串结尾的空格。remstr表示要去掉的字符。如果被省略，则去掉字符串中所有的空格。str表示要进行处理的字符串。

例如，TRIM(' hello ')的返回结果是'hello'。

自定义排序

有了字符串处理函数的基础，我们可以进入正题了。我们需要把每个音乐家姓名转换为不带冠词的字符串，然后按照新的字符串进行排序。这个过程可以用CASE语句实现。

CASE语句可以根据条件来返回不同的结果。其语法如下：

CASE expr
  WHEN val1 THEN result1
  WHEN val2 THEN result2
  ...
  [ELSE dresult]
END

其中，expr表示要进行比较的表达式，val表示某个比较值，result表示该比较值对应的返回结果。如果expr的值等于某个val的值，则返回对应的result。如果没有匹配的值，则返回ELSE语句指定的默认结果dresult。

在本例中，我们需要把每个音乐家姓名转换为不带冠词的字符串。这个过程可以用CASE语句和字符串处理函数结合实现。具体实现方法为：

使用TRIM()函数去掉字符串两端的空格。
使用SUBSTRING()函数提取字符串中除“the ”之外的子串。如果字符串不以“the ”开头，则直接返回原字符串。

实现代码如下：

SELECT
  id,
  name,
  CASE
    WHEN name LIKE 'The %' THEN TRIM(BOTH ' ' FROM SUBSTRING(name, 5))
    ELSE name
  END AS sort_name
FROM
  artist
ORDER BY
  sort_name;