MySQL中的字母数字排序

Question

如何在MySQL中使包含字母数字字符的列仅按数字顺序排序？

列（名称）是唯一字段。

我的表包含记录，

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 时，我只能按字母顺序得到结果。如何才能得到其他排序方式？

- Paulraj

Answer 1

我会这样做:

我会这样做：

select id, name from Table1 order by names + 0, names;

无序：

mysql> select * from alpha;
+---+-------+
| i | name  |
+---+-------+
| 1 | ab001 |
| 2 | ab010 |
| 3 | aa002 |
| 4 | cc001 |
| 5 | cb010 |
| 6 | aaa02 |
+---+-------+
6 rows in set (0.00 sec)

使用我的查询：

mysql> select i, name from alpha order by name + 0, name;
+---+-------+
| i | name  |
+---+-------+
| 3 | aa002 |
| 6 | aaa02 |
| 1 | ab001 |
| 2 | ab010 |
| 5 | cb010 |
| 4 | cc001 |
+---+-------+
6 rows in set (0.00 sec)

- Xavier Maillard

---

非常感谢，它可以很好地处理我的序列1A、2A、5C、15D、20A...。对于排序字母数字数据来说是一个不错的解决方案。 - garatu

Answer 2

假设你的字符串总是以3位数字结尾，你可以使用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不支持函数索引，因此此查询速度相对较慢。最好重新设计数据库（例如单独存储数字），以便不需要对函数值进行排序。

- Mark Byers

---

每当您按顺序使用函数时，就是时候考虑重构数据库了。 - HLGEM

谢谢，它运行得很好，但它最适合特定长度值。 - heySushil

Answer 3

我遇到了一个稍微不同的问题，但第二个示例对我有用。我想给它投票，但我刚刚开始使用这里。

我正在处理一个单个varchar列，它可以包含任何类型的信息，但无论其中包含什么，都需要正确排序：

因此，我的列可能包含以下信息：

AB CD EF GH

或者

01 02 03 04

Xavier的答案使得当信息全部为字母时按字母顺序排列，当信息为数字时按数字顺序排列。

谢谢

谢谢！

- dmgig

Answer 4

如果您的前X个字符是字母，后面是数字，并且字母字符不同，那么最好将此数据放入两列中 - 一列用于初始字母，另一列用于数字。现在，每次想要排序时，您都不必进行转换，只需首先按字母列排序，然后按数字列排序。如果您经常进行排序或者有大型数据集，则重新设计结构确实是更好的方法。

您甚至可以将当前字段保留为PK，并使用触发器将其分离到两个新字段中。这样，当输入或更新数据时，数据将以可自动排序的格式放置，所有当前代码仍将正常工作，但您可以重构以使用更高效的排序。

- HLGEM