4.1 自然排序

在本教程中，您将使用ORDER BY子句了解MySQL中的各种自然排序技术。

下面让我们使用一个示例数据来开始学习自然排序技术。

假设我们有一个items的表，其中包含两列：id和item_no。使用以下创建items表，如下：

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

我们使用语句将一些数据插入到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     |
| 2A      |
| 3C      |
+---------+
7 rows in set

这不是我们的预期的结果，我们想要看到的结果如下：

+---------+
| item_no |
+---------+
| 1       |
| 1C      |
| 2       |
| 2A      |
| 3C      |
| 10Z     |
| 20D     |
+---------+

为了克服这个问题，首先我们将item_no列分成两列：prefix 和 suffix。 prefix列存储item_no的数字部分，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;

执行上面查询语句，得到以下结果 -

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

下面来看看经常要处理的另一个常见的数据。

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');

排序后的预期结果如下：

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

执行上面查询，得到以下结果 -

mysql> 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

如下所看到数据就是自然排序的。

在本教程中，我们已经演示了如何在MySQL中执行自然排序的各种技术。

Previous4.1 ORDER BY 子句 Next第五节连接表

Last updated 5 years ago

Was this helpful?

mysql> 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