MySQL 自然排序

---

简介：在本教程中，您将使用ORDER BY子句了解MySQL中的各种自然排序技术。 让我们用示例数据开始教程。 假设我们有一个名为items包含两列的表：id和item_no。要创建items表，我们使用CREATE TABLE语句，如下所示：

CREATE TABLE IF NOT EXISTS items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);

我们使用INSERT语句将一些数据插入items表中：

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');

当我们选择item_no数据并对其进行排序时，我们得到以下结果：

SELECT 
    item_no
FROM
    items
ORDER BY item_no;

+---------+
| item_no |
+---------+
| 1       |
| 10Z     |
| 1C      |
| 2       |
| 20D     |
| 3C      |
+---------+
6 rows in set (0.00 sec)

这不是我们的预期。我们希望看到如下结果：

+---------+
| item_no |
+---------+
| 1       |
| 1C      |
| 2       |
| 3C      |
| 10Z     |
| 20D     |
+---------+
6 rows in set, 4 warnings (0.00 sec)

这称为自然分选。不幸的是，MySQL没有提供任何内置的自然排序语法或功能。在ORDER BY子句对线性方式，即字符串，每次一个字符，从第一个字符开始。 为了解决这个问题，首先我们将item_no列拆分为2列：prefix和suffix。prefix列存储数字部分，而suffix列存储字母部分。然后，我们可以根据这些列对数据进行排序，如下所示：

SELECT 
    CONCAT(prefix, suffix)
FROM
    items
ORDER BY prefix , suffix;

查询首先按数字对数据进行排序，然后按字母顺序对数据进行排序。我们得到了预期的结果。 这种解决方案的缺点是我们必须item_no在插入或更新之前将其分成两部分。另外，在选择数据时，我们必须将两列合二为一。 如果item_no数据采用相当标准的格式，则可以使用以下查询执行自然排序，而无需更改表结构。

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

在此查询中，首先我们item_no使用类型转换将数据转换为无符号整数。其次，我们使用ORDER BY子句首先按字母顺序对行进行排序，然后按字母顺序对行进行排序。 让我们来看看我们经常要处理的另一组常见数据。

TRUNCATE TABLE items;
 
INSERT INTO items(item_no)
VALUES('A-1'),
      ('A-2'),
      ('A-3'),
      ('A-4'),
      ('A-5'),
      ('A-10'),
      ('A-11'),
      ('A-20'),
      ('A-30');

排序后的预期结果如下：

+---------+
| item_no |
+---------+
| A-1     |
| A-10    |
| A-11    |
| A-2     |
| A-20    |
| A-3     |
| A-30    |
| A-4     |
| A-5     |
+---------+
9 rows in set, 9 warnings (0.00 sec)

为了达到这个结果，我们可以使用LENGTH函数。请注意，LENGTH函数返回字符串的长度。我们的想法是先按item_no长度排序数据，然后按列值排序，如下面的查询：

SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;

排序的结果为：

+---------+
| item_no |
+---------+
| A-1     |
| A-2     |
| A-3     |
| A-4     |
| A-5     |
| A-10    |
| A-11    |
| A-20    |
| A-30    |
+---------+
9 rows in set (0.01 sec)

如您所见，数据自然排序。 如果以上所有解决方案都不适合您。您需要在应用程序层中执行自然排序。有些语言支持自然排序功能，例如，PHP提供了natsort() 函数使用自然排序算法对数组进行排序。 在本教程中，我们向您展示了在MySQL中执行自然排序的各种技术。

---