mysql order by 自定义排序|极客教程

mysql order by 自定义排序

1. 引言

在MySQL中，ORDER BY子句用于对结果集进行排序。默认情况下，ORDER BY按照列的升序进行排序。但在某些情况下，我们可能需要对结果集进行自定义排序，以满足特定的业务需求。

本文将详细讨论MySQL中的自定义排序技术，包括使用常量、函数、CASE语句和自定义排序规则等方式进行排序。

2. 使用常量进行自定义排序

在有些情况下，我们需要按照特定的顺序对结果集进行排序，而不是按照默认的升序或降序排序。这时可以通过在ORDER BY子句中使用常量来实现自定义排序。

例如，我们有一个名为students的表，其中包含以下数据：

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 1  | John  | 20  |
| 2  | Alice | 25  |
| 3  | Mike  | 18  |
| 4  | Lily  | 22  |
+----+-------+-----+

现在，我们要按照特定的顺序显示这些学生的信息，顺序为Alice、Lily、John、Mike。可以使用常量函数FIELD()对结果集进行自定义排序：

SELECT id, name, age
FROM students
ORDER BY FIELD(name, 'Alice', 'Lily', 'John', 'Mike');

执行上述查询后，结果如下：

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 2  | Alice | 25  |
| 4  | Lily  | 22  |
| 1  | John  | 20  |
| 3  | Mike  | 18  |
+----+-------+-----+

3. 使用函数进行自定义排序

除了常量函数FIELD()外，我们还可以使用其他类型的函数对结果集进行自定义排序。常用的函数包括CONVERT()、CAST()、SUBSTRING()、TRIM()等。

例如，我们有一个名为products的表，其中包含以下数据：

+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
| 1  | iPhone 12 Pro   | 1000  |
| 2  | Galaxy S21      | 900   |
| 3  | Huawei P40 Pro  | 800   |
| 4  | OnePlus 9 Pro   | 700   |
+----+-----------------+-------+

现在，我们要按照价格的整数部分对产品进行排序。可以使用函数SUBSTRING_INDEX()对结果集进行自定义排序：

SELECT id, name, price
FROM products
ORDER BY CAST(SUBSTRING_INDEX(price, '.', 1) AS UNSIGNED) DESC;

执行上述查询后，结果如下：

+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
| 1  | iPhone 12 Pro   | 1000  |
| 2  | Galaxy S21      | 900   |
| 3  | Huawei P40 Pro  | 800   |
| 4  | OnePlus 9 Pro   | 700   |
+----+-----------------+-------+

4. 使用CASE语句进行自定义排序

另一种常用的方法是使用CASE语句在ORDER BY子句中进行自定义排序。

例如，我们有一个名为employees的表，其中包含以下数据：

+-----+----------+---------+
| id  | name     | country |
+-----+----------+---------+
| 1   | John     | USA     |
| 2   | Alice    | UK      |
| 3   | Mike     | Canada  |
| 4   | Lily     | UK      |
+-----+----------+---------+

现在，我们要按照国家的自然顺序进行排序，即按字母顺序排列。可以使用CASE语句对结果集进行自定义排序：

SELECT id, name, country
FROM employees
ORDER BY CASE country
    WHEN 'USA' THEN 1
    WHEN 'UK' THEN 2
    WHEN 'Canada' THEN 3
    ELSE 4
END;

执行上述查询后，结果如下：

+----+-------+---------+
| id | name  | country |
+----+-------+---------+
| 1  | John  | USA     |
| 2  | Alice | UK      |
| 4  | Lily  | UK      |
| 3  | Mike  | Canada  |
+----+-------+---------+

5. 使用自定义排序规则

除了上述方法外，MySQL还提供了更灵活的方式，即使用自定义排序规则来实现自定义排序。

自定义排序规则是一种定义列值之间相对顺序的技术。可以使用ALTER TABLE语句和charset参数来创建自定义排序规则。

例如，我们要创建一个自定义排序规则，按照”Alice”、”Lily”、”John”、”Mike”的顺序对students表中的name列进行排序。

首先，我们需要创建一个自定义字符集和排序规则：

CREATE CHARACTER SET custom_charset;
ALTER CHARACTER SET custom_charset COLLATE = utf8_general_ci;

然后，我们将name列的字符集设置为自定义字符集，并使用自定义排序规则进行排序：

ALTER TABLE students MODIFY COLUMN name VARCHAR(50)
CHARACTER SET custom_charset COLLATE utf8_custom_ci;

SELECT id, name, age
FROM students
ORDER BY name;

执行上述查询后，结果如下：

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 2  | Alice | 25  |
| 4  | Lily  | 22  |
| 1  | John  | 20  |
| 3  | Mike  | 18  |
+----+-------+-----+