当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位数字结尾，则可以使用 RIGHT :
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.