Skip to main content

MySQL使用ORDER BY子句进行自然排序

设置示例表

首先,使用以下create table语句创建一个名为items的新表:

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

第三,从按项目编号排序的项目表中查询数据:

SELECT 
item_no
FROM
items
ORDER BY item_no;

MySQL Natural Sorting Example

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

MySQL Natural Sorting Example correct order

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

MySQL自然排序示例

为了解决这个问题,首先我们将item_no列拆分为两列:前缀和后缀。前缀列存储项目_no的数字部分,后缀列存储字母部分。然后,我们可以根据这些列对数据进行排序,如下查询所示:

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

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

这种解决方案的缺点是,在插入或更新项目之前,我们必须将其分为两部分。此外,在选择数据时,我们必须将两列合并为一列。

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

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

排序后的预期结果如下:

MySQL Natural Sorting Another Good Example

为了达到这个结果,我们可以使用长度函数。请注意,LENGTH函数返回字符串的长度。其思想是先按长度,然后按列值对项_no data进行排序,如下所示:

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

正如你所见,数据是自然排序的。

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

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