MySQL Natural Sorting

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

设置样本表

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

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

其次，将一些行插入到items表中：

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');Code language: SQL (Structured Query Language) (sql)

第三，从items表中查询按item_no排序的数据：

SELECT 
    item_no
FROM
    items
ORDER BY item_no;Code language: SQL (Structured Query Language) (sql)

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

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

MySQL自然排序示例

为了解决这个问题，首先我们将item_no列分成两列： prefix和suffix 。 prefix列存储item_no的数字部分， suffix列存储字母部分。然后，我们可以根据这些列对数据进行排序，如以下查询所示：

SELECT 
    CONCAT(prefix, suffix)
FROM
    items
ORDER BY 
    prefix , suffix;Code language: SQL (Structured Query Language) (sql)

该查询首先按数字顺序对数据进行排序，然后按字母顺序对数据进行排序。我们得到了预期的结果。

该解决方案的缺点是我们必须在插入或更新item_no之前将其分成两部分。另外，当我们选择数据时，我们必须将两列合并为一列。

如果item_no数据的格式相当标准，则可以使用以下查询进行自然排序，而无需更改表结构。

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;Code language: SQL (Structured Query Language) (sql)

在此查询中，首先，我们使用类型cast将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');Code language: SQL (Structured Query Language) (sql)

排序后的预期结果如下：

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

SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;Code language: SQL (Structured Query Language) (sql)

正如您所看到的，数据是自然排序的。

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

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