MySQL 'Order By' - 正确排序字母数字

Question

我想按照下面显示的顺序对以下数据项进行排序(数字 1-12):

123456789101112

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

110111223456789

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.:

A1534GG46A100B100A100JE

etc....

Thanks!

update: people asking for query

select * from table order by name asc

Answer 1

人们使用不同的技巧来做到这一点。我用谷歌搜索并发现了一些结果，每个结果都遵循不同的技巧。看看它们:

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

编辑:

我刚刚为 future 的访问者添加了每个链接的代码。

Alpha Numeric Sorting in MySQL

给定输入

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

Expected output

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

Query

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

Natural Sorting in MySQL

给定输入

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

Expected Output

 -------------------------- -------------
| 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  

Sorting of numeric values mixed with alphanumeric values

给定输入

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;