Mysql中的字母数字顺序 [英] Alphanumeric Order By in Mysql

问题描述

当mysql中包含字母数字字符的列时，如何仅按数字顺序排序?

How can i make only numeric order by when the column containing alphanumeric characters in mysql ?

列(名称)是唯一字段.

column (name) is unique field.

我的表包含记录，

id  name
1   ab001
2   ab010
3   aa002
4   ac004
5   ba015
6   ba006
7   aa005
8   ac003

结果必须是这样

id  name
1   ab001
3   aa002
8   ac003
4   ac004
7   aa005
6   ba006
2   ab010
5   ba015

当我尝试查询Select * from test order by name时，我仅按字母字符获取结果顺序.我怎么得到这个?

When I am trying this query Select * from test order by name, I am getting the results order by alpha characters only. How do I get this ?

推荐答案

假定您的字符串始终以3位数字结尾，则可以使用

Assuming your strings always end with 3 digits you could use RIGHT:

SELECT id, name
FROM Table1
ORDER BY RIGHT(name, 3);

结果:

1, 'ab001'
3, 'aa002'
8, 'ac003'
4, 'ac004'
7, 'aa005'
6, 'ba006'
2, 'ab010'
5, 'ba015'

MySQL不支持功能索引，因此此查询将相对较慢.最好重新设计数据库(例如，单独存储数字)，这样就不必对函数的值进行排序.

MySQL doesn't have support for functional indexes, so this query will be relatively slow. It would be better to redesign your database (e.g. store the number separately) so that ordering on the value of a function isn't necessary.

这篇关于Mysql中的字母数字顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！