MySQL中两个VARCHAR类型字段多字段排序结果不符合预期的解决方法

现有语句异常的核心原因 #

你当前使用的select name,class from student order by name asc, class asc;语法本身没有错误，结果不符合预期基本都来自VARCHAR类型字段的排序逻辑和日常直觉的偏差，常见场景包括：

• 字符串字段是按字符编码逐位对比排序，并非按人类的语义认知排序
  • 针对name字段：如果表字段配置了区分大小写的排序规则，大写开头的字符（比如你提到的记录里Mike的大写M）编码值远小于小写开头的字符，会直接排在所有小写姓名的最前列，如果你预期是不区分大小写、按字母/拼音自然归集姓名，就会觉得Mike的位置完全不对
  • 针对class字段：如果存的是Four/Five这类英文班级名，按字符串逐位排序的话Four会固定排在Five前面，完全是英文字母顺序；如果存的是带数字的字符串（比如Class 4/Class 10），字符串排序会把Class 10排在Class 4前面，和人直觉的“4班比10班靠前”的认知完全相反
• 多字段排序的优先级严格按ORDER BY后面的字段顺序从左到右执行：先按name升序归集所有同姓名的记录，再对同姓名的记录按class升序排序，如果你实际想要的逻辑是先按班级归集、同班级内再排姓名，当前语句自然不符合要求

针对性修正写法 #

• 如果需要实现不区分大小写的自然排序，直接对排序字段做统一转小写处理，消除大小写带来的排序偏移：
  SELECT name, class FROM student ORDER BY LOWER(name) ASC, LOWER(class) ASC;
• 如果班级字段存的是带数字的字符串、需要按实际数值大小排序，提取字段里的数字部分作为排序依据：
  SELECT name, class FROM student ORDER BY name ASC, CAST(REGEXP_REPLACE(class, '[^0-9]', '') AS UNSIGNED) ASC;
• 如果实际需要的排序优先级相反（先按班级排序，同班级内再按姓名排序），直接调换ORDER BY后的字段顺序即可：
  SELECT name, class FROM student ORDER BY class ASC, name ASC;

补充说明：MySQL中ORDER BY默认使用升序（ASC）规则，需要对某一个字段单独做降序排序时，直接在对应字段后追加DESC关键词即可，不需要对整个排序子句统一指定方向，比如ORDER BY name ASC, class DESC就代表姓名升序、同姓名下班级降序。

内容的提问来源于stack exchange，提问作者Ali Asadullah Shah