Mysql 中文名称(包括字母)按首字母排序

如果中文名称字段只有汉字那么排序只加个 

CONVERT(name USING gbk ) COLLATE gbk_chinese_ci asc

就可以。

现在要排序的字段既有中文也有字母还有特殊字符串。要求，汉字获取首字母，拼音直接截取首字母，特殊字符全部排在后面，默认归到其他类别：

SQL如下：

select
            cf.project_id as projectId,
            p.name as projectName,
						if(p.name NOT REGEXP '^[[:alpha:]]',1,0)  as projectIsNumbersOrSpecialCharacters,
						if(p.name REGEXP '^[a-zA-Z]',UPPER(LEFT(p.name,1)),ELT( INTERVAL( CONV( HEX( left( CONVERT( p.name
USING gbk ) , 1 ) ) , 16, 10 ) , 0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA, 0xB7A2, 0xB8C1, 0xB9FE, 0xBBF7, 0xBFA6, 0xC0AC, 0xC2E8, 0xC4C3, 0xC5B6, 0xC5BE, 0xC6DA, 0xC8BB, 0xC8F6, 0xCBFA, 0xCDDA, 0xCEF4, 0xD1B9, 0xD4D1 ) , 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'W', 'X', 'Y', 'Z' )) as PY
						
						
        from cloud_file cf
        left join project p on p.id = cf.project_id and p.mark = 1
        where cf.mark = 1
           and cf.project_id <> 0
            and cf.file_type = 2
        group by cf.project_id
        order by  if(p.name NOT REGEXP '^[[:alpha:]]' ,1,0) asc,
			
			if(p.name REGEXP '^[a-zA-Z]',UPPER(LEFT(p.name,1)),ELT( INTERVAL( CONV( HEX( left( CONVERT( p.name
USING gbk ) , 1 ) ) , 16, 10 ) , 0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA, 0xB7A2, 0xB8C1, 0xB9FE, 0xBBF7, 0xBFA6, 0xC0AC, 0xC2E8, 0xC4C3, 0xC5B6, 0xC5BE, 0xC6DA, 0xC8BB, 0xC8F6, 0xCBFA, 0xCDDA, 0xCEF4, 0xD1B9, 0xD4D1 ) , 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'W', 'X', 'Y', 'Z' )) asc
			
 	-- CONVERT(p.name USING gbk ) COLLATE gbk_chinese_ci asc -- 如果按照此排序，既有字母又有汉字的不生效了
			
			

排序结果如下：