Mysql中的字母数字顺序

本文介绍了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位数字结尾，则可以使用 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.