MySQL'Order By'-正确对字母数字进行排序 [英] MySQL 'Order By' - sorting alphanumeric correctly

问题描述

我要按以下顺序显示以下数据项(编号1-12):

I want to sort the following data items in the order they are presented below (numbers 1-12):

1
2
3
4
5
6
7
8
9
10
11
12

但是，我的查询-使用order by xxxxx asc时按其他所有字母的前一位排序:

However, my query - using order by xxxxx asc sorts by the first digit above all else:

1
10
11
12
2
3
4
5
6
7
8
9

有什么技巧可以使它更正确地排序?

Any tricks to make it sort more properly?

此外，为了完全公开，这可以是字母和数字的组合(尽管现在不是)，例如:

Further, in the interest of full disclosure, this could be a mix of letters and numbers (although right now it is not), e.g.:

A1
534G
G46A
100B
100A
100JE

等...

谢谢！

更新:要求查询的人

select * from table order by name asc

推荐答案

人们使用不同的技巧来做到这一点.我用Google搜索了一下，发现每个结果都有不同的技巧.看看他们:

People use different tricks to do this. I Googled and find out some results each follow different tricks. Have a look at them:

• MySQL中的字母数字排序
• MySQL中的自然排序
• 对数字值进行排序具有字母数字值
• mySQL自然排序
• MySQL中的自然排序

• Alpha Numeric Sorting in MySQL
• Natural Sorting in MySQL
• Sorting of numeric values mixed with alphanumeric values
• mySQL natural sort
• Natural Sort in MySQL

我刚刚为以后的访问者添加了每个链接的代码.

I have just added the code of each link for future visitors.

MySQL中的字母数字排序

提供输入

1A 1a 10A 9B 21C 1C 1D

预期输出

1A 1C 1D 1a 9B 10A 21C

查询

Bin Way
===================================
SELECT 
tbl_column, 
BIN(tbl_column) AS binray_not_needed_column
FROM db_table
ORDER BY binray_not_needed_column ASC , tbl_column ASC

-----------------------

Cast Way
===================================
SELECT 
tbl_column, 
CAST(tbl_column as SIGNED) AS casted_column
FROM db_table
ORDER BY casted_column ASC , tbl_column ASC

MySQL中的自然排序

提供输入

Table: sorting_test
 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test12                   | 2           |
| test13                   | 3           |
| test2                    | 4           |
| test3                    | 5           |
 -------------------------- -------------

预期产量

 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test2                    | 4           |
| test3                    | 5           |
| test12                   | 2           |
| test13                   | 3           |
 -------------------------- -------------

查询

SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY LENGTH(alphanumeric), alphanumeric  

对混合数值进行排序带有字母数字值

提供输入

2a, 12, 5b, 5a, 10, 11, 1, 4b

预期产量

1, 2a, 4b, 5a, 5b, 10, 11, 12

查询

SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;

希望这会有所帮助

这篇关于MySQL'Order By'-正确对字母数字进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！