MySQL ORDER BY string as number(较大的数字高于较小的数字)

Question

考虑以下结果，其中 code 的类型为 VARCHAR:

SELECT code
FROM lock
ORDER BY CAST(code AS signed) > 0 DESC, `code

|code|
|4   |  
|420 |
|5   |
|T6  |
|X30 |

如何更改查询，使其按以下顺序返回结果:

|code|
|4   |  
|5   |
|420 |
|T6  |
|X30 |

Answer 1

SELECT code
FROM lock
ORDER BY CAST(code AS signed) > 0 DESC, CAST(code AS signed) ASC, code ASC

第一个顺序会将数字排序到前面。第二个顺序只会对数字进行升序排序，而第一个顺序会将它们保留在开头。第三个将仅对字符串进行升序排序，数字将保持其顺序，因为它们已经排序。