MySQL 如何使用ORDER BY CASE WHEN REGEXP排序查询？

在实际的开发工作中，我们经常需要对数据库中的数据进行排序操作，而有时根据普通的大小比较已经不能够满足我们的需求，这时我们就需要使用更加高级的排序方式。本文将介绍如何使用ORDER BY CASE WHEN REGEXP排序查询来满足一些复杂的排序需求。

阅读更多：MySQL 教程

什么是ORDER BY CASE WHEN REGEXP排序查询？

首先，我们需要了解一下ORDER BY CASE WHEN REGEXP排序查询的基本概念：

ORDER BY语句：用于对查询结果进行排序，根据指定的列按照升序或降序排列，默认情况下按照升序排列；
CASE WHEN语句：用于逐个判断条件，并返回相应的结果，类似于if-else语句；
REGEXP语句：正则表达式，用于匹配字符串。

综合使用ORDER BY CASE WHEN REGEXP语句可以根据复杂的排序规则对查询结果进行排序，而不仅仅是基于普通的大小比较。

ORDER BY CASE WHEN REGEXP排序查询实例

接下来，我们将通过实例代码演示如何使用ORDER BY CASE WHEN REGEXP排序查询。假设我们有一个商品表，包含商品名称、商品价格、商品库存等字段。现在需求是根据不同条件对商品进行排序。

首先我们需要创建一个商品表，并插入一些测试数据：

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT UNSIGNED NOT NULL
);

INSERT INTO products (name, price, stock) VALUES
  ('苹果', 8.88, 100),
  ('香蕉', 6.66, 200),
  ('橘子', 3.33, 300),
  ('草莓', 2.22, 400),
  ('葡萄', 5.55, 500);

按照商品价格排序

首先我们需要按照商品价格升序排序，可以使用以下代码：

SELECT * FROM products ORDER BY price ASC;

结果如下：

+----+------+-------+-------+
| id | name | price | stock |
+----+------+-------+-------+
|  4 | 草莓 |  2.22 |   400 |
|  3 | 橘子 |  3.33 |   300 |
|  2 | 香蕉 |  6.66 |   200 |
|  5 | 葡萄 |  5.55 |   500 |
|  1 | 苹果 |  8.88 |   100 |
+----+------+-------+-------+

按照商品名称排序

接下来我们需要按照商品名称升序排序，可以使用以下代码：

SELECT * FROM products ORDER BY name ASC;

结果如下：

+----+------+-------+-------+
| id | name | price | stock |
+----+------+-------+-------+
|  4 | 草莓 |  2.22 |   400 |
|  1 | 苹果 |  8.88 |   100 |
|  3 | 橘子 |  3.33 |   300 |
|  5 | 葡萄 |  5.55 |   500 |
|  2 | 香蕉 |  6.66 |   200 |
+----+------+-------+-------+

按照商品名称和价格排序

如果我们需要按照商品名称进行排序，但是对于同名的商品需要按照价格升序排序。可以使用以下代码：

SELECT * FROM products
ORDER BY name ASC, CASE WHEN name = '苹果' THEN price END ASC, price ASC;

在上述代码中，我们使用CASE WHEN语句逐个判断商品名称是否为”苹果”，如果是，则按照价格升序排序，否则按照默认的商品名称升序排序，最后再按照价格升序排序。结果如下：

+----+------+-------+-------+
| id | name | price | stock |
+----+------+-------+-------+
|  4 | 草莓 |  2.22 |   400 |
|  3 | 橘子 |  3.33 |   300 |
|  5 | 葡萄 |  5.55 |   500 |
|  1 | 苹果 |  8.88 |   100 |
|  2 | 香蕉 |  6.66 |   200 |
+----+------+-------+-------+

注意，在使用CASE WHEN语句时，一定要注意每个when-then的分支要有一个默认的else分支。否则，当条件不满足时会返回NULL，这会影响后续的排序。

按照商品名称中的数字排序

有时商品名称中会包含数字，我们需要按照数字大小进行排序。例如，商品名称为”水杯20oz”，”水杯10oz”，”水杯30oz”，需要按照”水杯10oz”、”水杯20oz”、”水杯30oz”的顺序进行排序。可以使用以下代码：

SELECT * FROM products
ORDER BY CAST(REGEXP_SUBSTR(name, '\\d+') AS UNSIGNED) ASC, name ASC;

在上述代码中，我们使用REGEXP_SUBSTR函数提取商品名称中的数字，并使用CAST函数将其转换为无符号整数排序。结果如下：

+----+----------+-------+-------+
| id | name     | price | stock |
+----+----------+-------+-------+
|  4 | 水杯10oz |  2.22 |   400 |
|  1 | 水杯20oz |  8.88 |   100 |
|  5 | 水杯30oz |  5.55 |   500 |
|  3 | 食品      |  3.33 |   300 |
|  2 | 男鞋      |  6.66 |   200 |
+----+----------+-------+-------+

注意，在使用REGEXP_SUBSTR函数时，要注意正则表达式的格式以及返回的结果类型。如果不转换类型，则会按照字符串的字典序排序，这通常不是我们想要的结果。