MySQL自然排序

时间:2019-11-20 08:52:13  来源:igfitidea点击:

简介:在本教程中,您将使用ORDER BY子句了解MySQL中的一些自然排序技术。

设置样本表

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

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

其次,将一些行插入到项目表中:

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

第三,从由item_no排序的项目表中查询数据:

SELECT 
    item_no
FROM
    items
ORDER BY item_no;

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

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

MySQL自然排序示例

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

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

排序后的预期结果如下:

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

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

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

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

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