MySQL 自然排序

摘要：在本教程中，您将了解 MySQL 中使用 ORDER BY 子句的一些自然排序技术。

设置实例表

首先，创建一个 items 使用以下 CREATE TABLE 语句命名的新表：

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

三、从 items 表中查询数据按以下排序 item_no ：

SELECT 
    item_no
FROM
    items
ORDER BY item_no;

这可能不是我们所期望的。我们希望看到如下图所示的结果：

这称为自然排序。不幸的是，MySQL 不提供任何内置的自然排序语法或函数。该 ORDER BY 子句以线性方式对字符串进行排序，即一次一个字符，从第一个字符开始。

MySQL 自然排序实例

要解决此问题，首先我们将 item_no 列拆分为 2 列： 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;

在此查询中，首先，我们 item_no 使用类型 cast将数据转换为无符号整数。其次，我们使用 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');

排序后的预期结果如下：

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

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

如您所见，数据是自然排序的。

以防万一，上述所有解决方案都不适合您。需要在应用层进行自然排序。一些语言支持自然排序功能，例如，PHP提供了natsort() 函数，它使用自然排序算法对数组进行排序**。**

在本教程中，您学习了如何使用一些技术在 MySQL 中执行自然排序。

设置实例表

MySQL 自然排序实例

本页目录