mysql order by 引号_MySQL Order By子句与自然语言排序

在本教程中，您将使用ORDER BY子句了解MySQL中的各种自然排序技术。

下面让我们使用一个示例数据来开始学习自然排序技术。

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

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;

+---------+

| item_no |

+---------+

| 1 |

| 10Z |

| 1C |

| 2 |

| 20D |

| 2A |

| 3C |

+---------+

7 rows in set

这不是我们的预期的结果，我们想要看到的结果如下：

+---------+

| item_no |

+---------+

| 1 |

| 1C |

| 2 |

| 2A |

| 3C |

| 10Z |

| 20D |

+---------+

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

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

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;

执行上面查询语句，得到以下结果 -

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;

执行上面查询，得到以下结果 -

mysql> SELECT

item_no

FROM

items

ORDER BY LENGTH(item_no) , item_no;

+---------+

| item_no |

+---------+

| A-1 |

| A-2 |

| A-3 |

| A-4 |

| A-5 |

| A-10 |

| A-11 |

| A-20 |

| A-30 |

+---------+

9 rows in set

如下所看到数据就是自然排序的。

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

在本教程中，我们已经演示了如何在MySQL中执行自然排序的各种技术。

¥ 我要打赏

纠错/补充

收藏

加QQ群啦，易百教程官方技术学习群

注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。