mysql数字字母排序_MySQL'Order By'-正确排序字母数字

小编典典

人们使用不同的技巧来做到这一点。我搜索了一下，发现每个结果都遵循不同的技巧。看看他们：

编辑：

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

给定输入

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

给定输入

表：sorting_test

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

| 字母数字VARCHAR(75)| 整数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;

希望这可以帮助

2020-05-17