mysql 数字中文混排序

某个字段有纯数字、纯字母、纯中文以及数字加中文。需求希望所有数字开头先按照数字排序、然后再英文、最后按照中文排序。

select name from(
select name,(name+0) as sortNum, ((name+0)>0) as isNum from customs
) t order by isNum desc, sortNum , name

 

补充常见的排序效果如下：

按自然排序（ASCII）、GBK、UTF-8。 

一、ASCII

select name from customs order by name;

二、 GBK

select name from customs order by convert(name using gbk) collate gbk_chinese_ci

 三、UTF-8

select name from customs order by convert(name using utf8) collate utf8_unicode_ci;