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，这些记录将首先显示。如果需要，您可以单独处理。