MySQL ORDER BY

在本文中，我们介绍了在 MySQL 中的如何使用 ORDER BY 子句排序 SELECT 语句的结果集。

通常情况下，SELECT 语句返回的结果集是按照数据库默认的规则排序的。如果我们想按照自定义自定义规则排序结果集，可以使用 ORDER BY 子句。

我们可以通过 ORDER BY 子句指定排序的字段以及升序排序还是降序排序。

MySQL ORDER BY 语法

在 ORDER BY 子句中，我们可以指定一个或多个排序的字段。 ORDER BY 子句的语法如下：

SELECT
   column1, column2, ...
FROM
   table_name
[WHERE clause]
ORDER BY
   column1 [ASC|DESC],
   column2 [ASC|DESC],
   ...;

说明：

  • ORDER BY 子句可以指定一个或多个字段。
  • [ASC|DESC] 代表排序是升序还是降序，这是可选的。
  • ASC 代表升序，DESC 代表降序。
  • 未指定 [ASC|DESC] 时，默认值是 ASC。即，默认是按指定的字段升序排序。
  • 当指定多个列时，首先按照前面的字段排序，其次按照后面的字段排序。

MySQL ORDER BY 排序规则说明

  • ORDER BY column ASC;

    此 ORDER BY 子句对结果集按 column 字段的值升序排序。

  • ORDER BY column DESC;

    此 ORDER BY 子句对结果集按 column 字段的值降序排序。

  • ORDER BY column;

    此 ORDER BY 子句对结果集按 column 字段的值升序排序。这个语句等效于： ORDER BY column ASC;。

  • ORDER BY column1, column2;

    此 ORDER BY 子句对结果集先按 column1 字段的值升序排序，然后再按 column2 字段的值升序排序。

    也就是说主排序按 column1 字段升序排序，在主排序的基础上，对 column1 字段相同的行，再按 column2 字段升序排序。

  • ORDER BY column1 DESC, column2;

    此 ORDER BY 子句对结果集先按 column1 字段的值降序排序，然后再按按 column2 字段的值升序排序。

    也就是说主排序按 column1 字段降序排序，在主排序的基础上，对 column1 字段相同的行，再按 column2 字段升序排序。

MySQL ORDER BY 实例

在以下实例中，我们使用 Sakila 示例数据库中的 actor 表进行演示。

按字段升序排序

以下 SQL 语句使用 ORDER BY 子句按演员姓氏升序进行排序。

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name;

+----------+-------------+--------------+
| actor_id | first_name  | last_name    |
+----------+-------------+--------------+
|       92 | KIRSTEN     | AKROYD       |
|       58 | CHRISTIAN   | AKROYD       |
|      182 | DEBBIE      | AKROYD       |
|      194 | MERYL       | ALLEN        |
|      118 | CUBA        | ALLEN        |
|      145 | KIM         | ALLEN        |
...

按字段降序排序

以下 SQL 语句使用 ORDER BY 子句按演员姓氏降序进行排序。

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name DESC;

+----------+-------------+--------------+
| actor_id | first_name  | last_name    |
+----------+-------------+--------------+
|      111 | CAMERON     | ZELLWEGER    |
|       85 | MINNIE      | ZELLWEGER    |
|      186 | JULIA       | ZELLWEGER    |
|       63 | CAMERON     | WRAY         |
|      156 | FAY         | WOOD         |
|       13 | UMA         | WOOD         |
|      144 | ANGELA      | WITHERSPOON  |
|       68 | RIP         | WINSLET      |
....

按多字段排序

以下 SQL 语句使用 ORDER BY 子句先按演员姓氏升序排序，再按演员名字升序排序。

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name, first_name;

+----------+-------------+--------------+
| actor_id | first_name  | last_name    |
+----------+-------------+--------------+
|       58 | CHRISTIAN   | AKROYD       |
|      182 | DEBBIE      | AKROYD       |
|       92 | KIRSTEN     | AKROYD       |
|      118 | CUBA        | ALLEN        |
|      145 | KIM         | ALLEN        |
|      194 | MERYL       | ALLEN        |
....

按自定义顺序排序

有时候单纯的按照字段的值排序并不能满足要求，我们需要按照自定义的顺序的排序。比如，我们需要按照电影分级 'G', 'PG', 'PG-13', 'R', 'NC-17' 的顺序对影片进行排序。

对于这样的需求，它可以理解为按照列表中元素的索引位置进行排序。我们分别使用 CASE 子句或 FIELD() 函数实现它。

在以下实例中，我们使用 Sakila 示例数据库中的 film 表作为演示。

假设您要根据影片的等级按照的 'G', 'PG', 'PG-13', 'R', 'NC-17' 顺序对影片进行排序。

使用 CASE 实现自定义排序

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;

+---------+-----------------------------+--------+
| film_id | title                       | rating |
+---------+-----------------------------+--------+
|       2 | ACE GOLDFINGER              | G      |
|       4 | AFFAIR PREJUDICE            | G      |
...
|       1 | ACADEMY DINOSAUR            | PG     |
|       6 | AGENT TRUMAN                | PG     |
...
|       7 | AIRPLANE SIERRA             | PG-13  |
|       9 | ALABAMA DEVIL               | PG-13  |
...
|       8 | AIRPORT POLLOCK             | R      |
|      17 | ALONE TRIP                  | R      |
...
|       3 | ADAPTATION HOLES            | NC-17  |
|      10 | ALADDIN CALENDAR            | NC-17  |
...
1000 rows in set (0.00 sec)

在这个例子中，我们使用 CASE 将电影的等级转换为一个索引数字。然后使用 ORDER BY 按照这个数字进行排序。

可能您觉得 CASE 子句写起来很复杂，特别是列表值很多的时候。那么，请使用如下的 FIELD() 函数。

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

对于上面实例中的 CASE 语句，我们可以如下的使用 FIELD() 代替。

SELECT
    *
FROM
    film
ORDER BY FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17');

输出结果与上面实例完全相同。

在本例中，我们使用 FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17') 作为 ORDER BY 排序的表达式。其中 FIELD(value, value1, value2, ...) 函数返回 value 在 value1, value2, ... 列表中的位置。

ORDER BY 和 NULL

在 MySQL 中的升序排序中， NULL 值出现在非 NULL 值之前。

我们下面的实例使用以下临时数据作为演示：

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT 0 AS v
UNION ALL
SELECT 1 AS v;

+------+
| v    |
+------+
| A    |
| B    |
| NULL |
| 0    |
| 1    |
+------+

当我们使用 ORDER BY 子句升序 ASC 排序时， NULL 值排在非 NULL 值的前面。如下：

SELECT *
FROM (
    SELECT 'A' AS v
    UNION ALL
    SELECT 'B' AS v
    UNION ALL
    SELECT NULL AS v
    UNION ALL
    SELECT 0 AS v
    UNION ALL
    SELECT 1 AS v
) t
ORDER BY v;

+------+
| v    |
+------+
| NULL |
| 0    |
| 1    |
| A    |
| B    |
+------+

当我们使用 ORDER BY 子句降序 DESC 排序时， NULL 值排在非 NULL 值的后面。如下：

SELECT *
FROM (
    SELECT 'A' AS v
    UNION ALL
    SELECT 'B' AS v
    UNION ALL
    SELECT NULL AS v
    UNION ALL
    SELECT 0 AS v
    UNION ALL
    SELECT 1 AS v
) t
ORDER BY v DESC;

+------+
| v    |
+------+
| B    |
| A    |
| 1    |
| 0    |
| NULL |
+------+

结论

本文介绍了在 MySQL 中如何使用 ORDER BY 子句进行升序和降序排序，以及实现如何自定义排序。 ORDER BY 子句的用法要点如下：

  • 使用 ORDER BY 子句按一列或多列对结果集进行排序。
  • 使用 ASC 选项对结果集进行升序排序，使用DESC 选项对结果集进行降序排序。
  • 使用 FIELD() 函数或者 CASE 子句可按照自定义的序列排序。
  • 升序排序时， NULL 在非 NULL 值之前；降序排序时，NULL 在非 NULL 值之后。