Mysql 中的字母数字排序

发布于 2024-09-04 03:29:19 字数 406 浏览 6 评论 0原文

当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 时，我仅按字母字符排序结果。我如何得到这个？

原文

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

column (name) is unique field.

my table contains the records,

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

The results must be like this,

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

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 ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一念一轮回 2024-09-11 03:29:19

我会这样做：

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)

I would do this way:

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

without ordering:

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)

With my query:

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)

回复收藏 0 原文

随风而去 2024-09-11 03:29:19

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

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

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

Result:

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

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.

回复收藏 0 原文