做后台开发的同学应该都碰到过这个场景页面上有个下拉列表或者表格需要按中文姓名、城市名排序显示结果一查出来张三排到李四前面还是后面完全看运气搞得产品经理天天追着你问这个排序怎么是乱的。MySQL默认用的是latin1或者utf8字符集排序的时候按照字符编码值来比大小。中文编码本身不是按拼音顺序排列的所以直接 ORDER BY name 排出来的结果基本没法看。今天把MySQL中文排序的几种方案全讲一遍每种都配上可以直接跑的SQL看完直接用。一、先看看问题长什么样建一张测试表塞几条数据进去CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), city VARCHAR(50), score INT ) CHARSETutf8mb4; INSERT INTO student (name, city, score) VALUES (张三, 北京, 85), (李四, 上海, 92), (王五, 广州, 78), (赵六, 深圳, 88), (陈七, 杭州, 95), (刘八, 成都, 70), (黄九, 武汉, 83), (周十, 南京, 90);先用默认方式排序看看SELECT name FROM student ORDER BY name;结果大概率是这样刘八 周十 张三 李四 王五 赵六 黄九 陈七你说这顺序有啥规律没有。按编码值排的和人脑期望的拼音顺序完全对不上。二、方案一CONVERT函数转GBK排序最常用这是用得最多的一种方式原理很简单GBK编码本身就是按拼音顺序编排的把UTF8的中文字段转成GBK再排序就能得到拼音顺序的结果。SELECT name, city FROM student ORDER BY CONVERT(name USING gbk);输出陈七 黄九 李四 刘八 王五 张三 赵六 周十Chen、Huang、Li、Liu、Wang、Zhang、Zhao、Zhou拼音顺序没问题。带升降序的写法-- 拼音升序 SELECT name, city FROM student ORDER BY CONVERT(name USING gbk) ASC; -- 拼音降序 SELECT name, city FROM student ORDER BY CONVERT(name USING gbk) DESC;多字段混合排序-- 先按城市拼音排序同一个城市再按分数降序 SELECT name, city, score FROM student ORDER BY CONVERT(city USING gbk), score DESC;注意CONVERT方式对多音字无能为力。比如重庆的重会按zhong排序而不是chong银行的行会按xing而不是hang。MySQL不知道你这个词到底是哪个读音它只认字形编码。如果业务对多音字有严格要求得在表里加一个拼音字段。三、方案二建表时指定GBK字符集如果一张表的中文字段经常需要按拼音排序建表的时候直接把字符集设成GBK以后排序就不用每次都CONVERT了。CREATE TABLE student_gbk ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) CHARSETgbk, city VARCHAR(50) CHARSETgbk, score INT ) CHARSETutf8mb4;然后正常排序就行SELECT name FROM student_gbk ORDER BY name;直接就是拼音顺序。不过这种做法有个明显的限制整张表混用字符集后续维护容易出问题而且GBK字符集支持的字符范围比utf8mb4小很多生僻字可能存不进去。更推荐的做法是只给需要排序的字段指定GBKCREATE TABLE student2 ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) CHARSETgbk, city VARCHAR(50) CHARSETgbk, score INT, remark TEXT -- 这个字段存备注用utf8mb4支持emoji和生僻字 ) CHARSETutf8mb4;四、方案三加拼音辅助字段多音字场景推荐前面说了CONVERT转GBK解决不了多音字问题。如果你的业务场景里多音字很常见比如地名、人名最靠谱的办法是在表里加一列存拼音插入数据的时候就把拼音生成好。CREATE TABLE contact ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), name_pinyin VARCHAR(100), phone VARCHAR(20) ) CHARSETutf8mb4;插入数据时把拼音带上INSERT INTO contact (name, name_pinyin, phone) VALUES (张三, zhangsan, 13800000001), (重庆, chongqing, 13800000002), (李白, libai, 13800000003), (银行, yinhang, 13800000004);排序直接按拼音字段来SELECT name, phone FROM contact ORDER BY name_pinyin;输出重庆 13800000002 李白 13800000003 张三 13800000001 银行 13800000004Chongqing、Libai、Zhangsan、Yinhang多音字的读音完全由你控制。Java代码自动生成拼音的方案实际项目中手动填拼音太蠢了一般用pinyin4j这类库在代码层自动转换!-- Maven依赖 -- dependency groupIdcom.belerweb/groupId artifactIdpinyin4j/artifactId version2.5.1/version /dependencyimport net.sourceforge.pinyin4j.PinyinHelper; public class PinyinUtil { public static String toPinyin(String chinese) { if (chinese null || chinese.isEmpty()) { return ; } StringBuilder sb new StringBuilder(); for (char c : chinese.toCharArray()) { if (c 128) { String[] pinyinArray PinyinHelper.toHanyuPinyinStringArray(c); if (pinyinArray ! null pinyinArray.length 0) { sb.append(pinyinArray[0]); } } else { sb.append(c); } } return sb.toString().toLowerCase(); } }但pinyin4j默认取的是第一个读音多音字的最常见读音不一定准确。如果业务对拼音精度要求高要么自己维护一个多音字词典要么用更专业的分词库比如HanLP。五、方案四修改列的COLLATE排序规则MySQL的排序行为由COLLATE校对规则控制。utf8mb4默认的校对规则是 utf8mb4_general_ci这个规则对中文排序不太友好。可以改成 utf8mb4_unicode_ci 或者 utf8mb4_zh_0900_as_csMySQL 8.0。会话级别临时修改-- 整个会话的排序规则改为支持中文的 SET NAMES utf8mb4 COLLATE utf8mb4_zh_0900_as_cs; SELECT name FROM student ORDER BY name;查询时指定COLLATESELECT name FROM student ORDER BY name COLLATE utf8mb4_zh_0900_as_cs;修改表字段的COLLATEALTER TABLE student MODIFY COLUMN name VARCHAR(50) COLLATE utf8mb4_zh_0900_as_cs;改完之后直接 ORDER BY name 就能得到按中文拼音排序的结果。utf8mb4_zh_0900_as_cs 是MySQL 8.0新引入的中文排序规则基于Unicode Collation AlgorithmUCA对中文的排序支持比老的 utf8mb4_unicode_ci 好不少。如果你的MySQL版本是8.0以上优先考虑这个方案。几个中文相关COLLATE的对比COLLATE名称MySQL版本说明utf8mb4_general_ci5.5默认规则中文排序不可用utf8mb4_unicode_ci5.5比general好一些但中文拼音排序不够准确utf8mb4_zh_0900_as_cs8.0专为中文设计拼音排序准确区分大小写utf8mb4_zh_0900_ai_ci8.0同上不区分大小写gbk_chinese_ci5.5GBK字符集的中文排序规则按拼音排序六、方案五ORDER BY FIELD自定义排序有些场景不是按拼音排而是按业务自定义的顺序排。比如城市要按北上广深排在前面其他城市按拼音排。这时候用FIELD函数。SELECT name, city, score FROM student ORDER BY FIELD(city, 北京, 上海, 广州, 深圳), CONVERT(city USING gbk);输出张三 北京 85 李四 上海 92 王五 广州 78 赵六 深圳 88 陈七 杭州 95 刘八 成都 70 黄九 武汉 83 周十 南京 90北京、上海、广州、深圳排在最前面按FIELD指定的顺序后面杭州、成都、武汉、南京按拼音排列。FIELD函数的原理FIELD(value, v1, v2, v3, ...) 返回value在列表中的位置找不到返回0。所以FIELD的排序结果是不在列表中的排最前值为0然后按列表顺序排。如果你想不在列表中的排后面加个DESC或者在FIELD外面套一层-- 不在列表中的城市排后面 SELECT name, city FROM student ORDER BY FIELD(city, 北京, 上海, 广州, 深圳) 0, FIELD(city, 北京, 上海, 广州, 深圳);FIELD(...) 0 这一项让不在列表中的城市返回true1排到后面在列表中的返回false0排前面。七、实战场景完整示例场景1通讯录按姓名拼音分组显示SELECT SUBSTRING(CONVERT(name USING gbk), 1, 1) AS first_char, GROUP_CONCAT(name ORDER BY CONVERT(name USING gbk) SEPARATOR 、) AS names FROM student GROUP BY SUBSTRING(CONVERT(name USING gbk), 1, 1) ORDER BY first_char;模拟通讯录按首字母分组的效果。场景2省市联动选择框省份按拼音排序-- 省份表 CREATE TABLE province ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) CHARSETutf8mb4; INSERT INTO province (name) VALUES (北京市), (上海市), (广东省), (浙江省), (江苏省), (四川省), (湖北省), (湖南省), (河南省), (河北省); -- 按拼音排序输出 SELECT id, name FROM province ORDER BY CONVERT(name USING gbk);输出北京市、广东省、湖北省、河南省、河北省、江苏省、上海市、四川省、浙江省、湖南省拼音顺序。场景3中文和英文混合排序CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) ) CHARSETutf8mb4; INSERT INTO product (name) VALUES (iPhone 15), (华为Mate60), (小米14), (OPPO Find X7), (vivo X100), (三星S24), (荣耀Magic6); -- 英文排在前面中文排在后面各自按规则排序 SELECT name FROM product ORDER BY CASE WHEN name REGEXP ^[a-zA-Z] THEN 0 ELSE 1 END, CASE WHEN name REGEXP ^[a-zA-Z] THEN name ELSE CONVERT(name USING gbk) END;输出iPhone 15 OPPO Find X7 vivo X100 华为Mate60 荣耀Magic6 三星S24 小米14场景4大数据量下的性能考虑CONVERT函数在排序时会导致MySQL无法使用索引每次排序都是filesort。数据量小的时候无所谓百万级以上就能感觉到慢了。这时候有两个优化思路思路一加存储拼音的索引列ALTER TABLE student ADD COLUMN name_pinyin VARCHAR(100); UPDATE student SET name_pinyin CONVERT(name USING gbk); ALTER TABLE student ADD INDEX idx_name_pinyin (name_pinyin);查询直接按索引列排SELECT name FROM student ORDER BY name_pinyin;思路二触发器自动维护拼音列DELIMITER // CREATE TRIGGER trg_student_insert BEFORE INSERT ON student FOR EACH ROW BEGIN SET NEW.name_pinyin CONVERT(NEW.name USING gbk); END // CREATE TRIGGER trg_student_update BEFORE UPDATE ON student FOR EACH ROW BEGIN IF NEW.name OLD.name THEN SET NEW.name_pinyin CONVERT(NEW.name USING gbk); END IF; END // DELIMITER ;这样插入和更新的时候自动维护拼音列查询时直接按索引列排序性能和普通排序没有区别。八、方案选择指南到底用哪种方案根据实际情况选场景推荐方案理由数据量小万级以下临时排序CONVERT转GBK简单直接一行SQL搞定MySQL 8.0表结构可改改COLLATE为utf8mb4_zh_0900_as_cs一劳永逸排序最准确多音字多的业务人名、地名加拼音辅助字段完全可控性能也好大数据量排序频繁拼音列 索引走索引不触发filesort自定义业务排序顺序ORDER BY FIELD灵活控制任意顺序九、几个容易踩的坑坑1utf8和utf8mb4不是一回事。MySQL里的utf8最多只支持3字节字符utf8mb4才支持完整的Unicode包括emoji。建表时统一用utf8mb4排序也一样。坑2CONVERT在某些MySQL版本中可能报错。确保你的MySQL编译时包含了GBK字符集支持。可以用 SHOW CHARACTER SET LIKE gbk; 检查。坑3排序结果在不同MySQL版本间可能不一致。尤其是 utf8mb4_unicode_ciMySQL 5.7和8.0对中文的处理有差异。上线前在目标环境测试一遍。坑4排序和存储字符集不匹配。如果表的字符集是utf8mb4但字段的COLLATE是 utf8mb4_general_ciCONVERT转GBK排序的结果可能跟预期有出入。最稳妥的办法是统一字符集和COLLATE设置。坑5数据里有繁体字。GBK编码里包含繁体字但繁体字的编码位置和简体字不在一起排序结果会把简体和繁体分开。如果业务需要简繁混合排序建议走拼音列方案。