MYSQL数据排序（九）自然排序

发布时间：2022-09-17 15:08:51 所属栏目：MySql教程来源：

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

　　CREATE TABLE IF NOT EXISTS items (
　　 id INT AUTO_INCREMENT PRIMARY KEY,
　

　　假设我们有一个名为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转换为无符号整数。然后mysql排序，我们使用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一分钟定位 Next	MySQL 8.0新特点 CTE
MySQL NULLIF函数用处	MySQL基准测试工具-sy