带字母数字前缀的MySQL排序

Question

我有一个包含以下数据的列的数据库：

aaa-1
aaa-2
aaa-3
...
aaa-10
aaa-11
...
aaa-100
aaa-101
...
aaa-1000

当我按升序查询和排序数据时，我得到：

aaa-1
aaa-10
aaa-11
...
aaa-100
aaa-101
...
aaa-1000
...
aaa-2
...
aaa-3

这实际上是正确的分类方法吗？订单是否因为aaa-前缀而被搞砸了？我该如何按照人类的方式来分类(比如看起来像第一段片段的东西)？

如果问题确实存在于前缀中，是否有一种方法可以删除它并仅用数字组件进行排序？

有人建议我应该改变我的数据，添加前导零，如aaa-0001和aaa-0002等等。然而，我不喜欢使用这种方法，因为每次列表上升到10的时候，我就不得不重新格式化这个专栏。

谢谢大家！)

Answer 1

您可以提取数字部分，将其转换为数字数据类型，然后执行ORDER BY。

SELECT mytable.*,
    CAST(SUBSTRING_INDEX(mycolumn, '-', - 1) AS UNSIGNED) mycolumnintdata
FROM
    mytable
ORDER BY mycolumnintdata;

如果有与数字不匹配的表达式，则CAST函数将返回0，并首先显示这些记录。如果需要，您可以单独处理。

Answer 2

我也有过类似的问题，而为我做这件事的诀窍就是这个。

*“按长度排序( column_name )，column_name

只要值的非数字部分长度相同，这将在10之前排序1，在100之前排序10，等等。

正如Andreas Bergström在this question上给出的。

希望这能帮到别人。

Answer 3

我会给你一个样品分类。不是基于数据样本，但这可以帮助您解决问题。

假设你有这样的数据：

id
----
1
2
6
10
13

当您这样做时，ORDER BY id ASC将返回：

id
----
1
10
13
2
6

我建议使用LPAD。这个查询：SELECT LPAD('12',5,'0')返回00012

因此，当您有像我上面提供的表数据时，您可以这样对它们进行排序：

SELECT * FROM TABLE
ORDER BY LPAD(ID,7,'0') ASC

根据你的数据。SELECT SUBSTR('aaa-100',5,LENGTH('aaa-100') - 3)返回100

所以，SELECT LPAD( SUBSTR('aaa-100',5,LENGTH('aaa-100') - 3), 7, '0')返回00000100

因此，可以组合字符串函数，如SUBSTR和LPAD。现在有线索了吗？