mysql order by如何实现自然排序

摘要 ：在本教程中，您将了解MySQL如何使用ORDER BY子句实现自然排序技术。

假设我们有一个名为 items的 表，包含两列： id 和 item_no 。要创建 items 表，我们使用CREATE TABLE语句，sql语句如下：

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

我们使用INSERT语句将一些数据插入到 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; 

这不是我们希望看到的结果。我们预期的结果如下：

这被称为自然排序。不幸的是，MySQL不提供任何内置的自然排序语法或函数。

为了实现以上排序规则，首先我们将 item_no 列分成2列： prefix 和 suffix 。 prefix 列存储 item_no的 数字部分， suffix 列存储 item_no 的字母部分。然后，我们可以根据 prefix 和 suffix 列对数据进行排序，如下所示：

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

sql语句首先按prefix排序，然后按suffix排序。这样我们得到预期的排序结果。

这个解决方案的缺点是我们必须在插入或更新 item_no 之前将它分成两部分。另外，当我们查询数据时，我们必须将两列合并成一列。

如果 item_no 数据是相当标准的格式，您可以使用以下sql查询实现自然排序而不更改表结构。

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

在这个sql语句中，首先我们使用类型转换将 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字段 长度首先对结果集进行排序，然后按 item_no字段 值对结果集进行排序：

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

这样就实现了自然排序。

如果上面的方法不能解决自然排序， 那么，您需要在应用程序层中执行自然排序。一些语言支持自然排序功能，例如，PHP提供了对数组使用自然排序算法的函数natsort() 。

在本教程中，我们向您介绍了在MySQL中自然排序的实现思路和方法。

查看更多关于mysql order by如何实现自然排序的详细内容...