MySQL按数字字符串排序 [英] MySQL order by string with numbers

问题描述

我有诸如M1 M3 M4 M14 M30 M40之类的字符串(实际上是一个字母后的2-3位数) 当我执行"ORDER BY name"时，将返回:

I have strings such as M1 M3 M4 M14 M30 M40 etc (really any int 2-3 digits after a letter) When I do " ORDER BY name " this returns:

M1, M14, M3, M30, M4, M40

当我想要的时候:

M1, M3, M4, M14, M30, M40 它把整个东西当作一个字符串来对待，但是我想把它当作字符串+ int

M1, M3, M4, M14, M30, M40 Its treating the whole thing as a string but I want to treat it as string + int

有什么想法吗?

推荐答案

您可以在ORDER BY中使用SUBSTR和CAST AS UNSIGNED/SIGNED:

You could use SUBSTR and CAST AS UNSIGNED/SIGNED within ORDER BY:

SELECT * FROM table_name ORDER BY
    SUBSTR(col_name FROM 1 FOR 1),
    CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)

这篇关于MySQL按数字字符串排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！