建表的时候随手选了个字符集查数据发现中文排序不对、大小写匹配出问题、索引莫名其妙失效八成是排序规则Collation没搞对。MySQL 8.0 改了默认排序规则从 utf8mb4_general_ci 换成了 utf8mb4_0900_ai_ci还加了一堆语言专用排序规则。这篇就是讲排序规则的每个都配上能跑的 SQL。一、先搞清楚字符集和排序规则是什么关系字符集Charset决定怎么存排序规则Collation决定怎么比。同一个字符集可以有多种排序规则。比如 utf8mb4 字符集在 MySQL 8.0 中有几十种排序规则对应不同的比较和排序逻辑。简单类比字符集是字典的收录范围排序规则是字典的编排方式。同样是汉字可以按拼音排也可以按笔画排。查看 MySQL 8.0 支持的所有 utf8mb4 排序规则SHOW COLLATION WHERE Charset utf8mb4;输出会很长挑几个重点的排序规则重音敏感大小写敏感说明utf8mb4_0900_ai_ci否否MySQL 8.0 默认utf8mb4_0900_as_ci是否区分重音utf8mb4_0900_as_cs是是区分重音大小写utf8mb4_0900_bin--二进制比较utf8mb4_zh_0900_as_ci是否中文拼音排序utf8mb4_zh_0900_as_cs是是中文拼音区分大小写utf8mb4_bin--旧版二进制比较utf8mb4_general_ci否否MySQL 5.7 默认已过时二、排序规则命名规则拆解MySQL 排序规则的命名有规律看懂名字就能猜出它的行为。以 utf8mb4_zh_0900_as_ci 为例utf8mb4 _ zh _ 0900 _ as _ ci | | | | | 字符集 语言 UCA版本 重音 大小写各部分含义部分含义取值示例字符集前缀属于哪个字符集utf8mb4、utf8、latin1语言代码为哪种语言优化zh中文、ja日语、de德语等UCA版本Unicode排序算法版本0900UCA 9.0、无旧算法重音敏感性ai 不区分as 区分ai、as大小写敏感性ci 不区分cs 区分ci、csbin二进制逐字节比较bin没有语言代码的排序规则如 utf8mb4_0900_ai_ci是通用排序规则基于 DUCETDefault Unicode Collation Element Table对大多数语言都能工作但对特定语言比如中文按拼音排序不够精确。三、七大常用排序规则详解先建一张测试表后面所有示例都用它CREATE TABLE user_test ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) COLLATE utf8mb4_0900_ai_ci, city VARCHAR(50) COLLATE utf8mb4_0900_ai_ci ); INSERT INTO user_test (name, city) VALUES (张三, 北京), (李四, 上海), (王五, 广州), (Zhang San, Shenzhen), (zhang san, Hangzhou), (Müller, Berlin), (Muller, Munich), (Café, Paris), (Cafe, Lyon);1. utf8mb4_0900_ai_ci —— MySQL 8.0 默认MySQL 8.0 的默认排序规则建表时不指定就会用这个。aiaccent insensitive不区分重音Café 和 Cafe 视为相等cicase insensitive不区分大小写Zhang 和 zhang 视为相等0900使用 UCA 9.0.0 排序算法-- 不区分大小写 SELECT * FROM user_test WHERE name zhang san; -- 结果Zhang San、zhang san 都能查到 -- 不区分重音 SELECT * FROM user_test WHERE name Muller; -- 结果Müller、Muller 都能查到 SELECT * FROM user_test WHERE name Cafe; -- 结果Café、Cafe 都能查到大部分场景用这个就够了。但有一个坑——中文排序按 Unicode 码点排不是按拼音SELECT name FROM user_test ORDER BY name; -- 张三、李四、王五 的排序结果是按 Unicode 码点 -- 不是按拼音顺序李四、王五、张三2. utf8mb4_zh_0900_as_ci —— 中文拼音排序MySQL 8.0 新增的中文专用排序规则按汉语拼音排序。-- 临时指定排序规则 SELECT name FROM user_test ORDER BY name COLLATE utf8mb4_zh_0900_as_ci; -- 结果按拼音李四(L)、王五(W)、张三(Z)建一张专门存中文数据的表CREATE TABLE chinese_data ( id INT PRIMARY KEY AUTO_INCREMENT, word VARCHAR(100) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_ci; INSERT INTO chinese_data (word) VALUES (重庆), (重量), (长大), (长城), (长安), (音乐), (快乐), (银行), (行走); SELECT word FROM chinese_data ORDER BY word; -- 按拼音排序 -- 长安(changan)、长城(changcheng)、长大(changda) -- 重庆(zhongqing)、重量(zhongliang) -- 银行(yinhang)、行走(xingzou) -- 音乐(yinyue)、快乐(kuaile)多音字是个麻烦。重庆和重量重默认读 zhòng排序按这个走。遇到多音字不准的情况业务层自己处理。3. utf8mb4_zh_0900_as_cs —— 中文拼音 区分大小写和上面那个一样按拼音排序但额外区分大小写。SELECT name FROM user_test ORDER BY name COLLATE utf8mb4_zh_0900_as_cs; -- 查询时区分大小写 SELECT * FROM user_test WHERE name Zhang San COLLATE utf8mb4_zh_0900_as_cs; -- 只返回 Zhang San不会匹配 zhang san适合中英文混合的表英文大小写要求精确匹配。4. utf8mb4_0900_as_cs —— 区分重音 区分大小写通用排序规则但对重音和大小写都敏感。SELECT * FROM user_test WHERE name Café COLLATE utf8mb4_0900_as_cs; -- 只返回 CaféCafe 被排除 SELECT * FROM user_test WHERE name Müller COLLATE utf8mb4_0900_as_cs; -- 只返回 MüllerMuller 被排除 SELECT * FROM user_test WHERE name Zhang San COLLATE utf8mb4_0900_as_cs; -- 只返回 Zhang Sanzhang san 被排除用户系统、账号系统建议用这个。用户名 Admin 和 admin 应该是两个不同的账号。5. utf8mb4_0900_bin —— 二进制精确比较逐字节比较按 Unicode 码点排序。它根本不管语义就是比较二进制值。SELECT name FROM user_test ORDER BY name COLLATE utf8mb4_0900_bin; -- 按码点严格排序Cafe Café Muller Müller Zhang San zhang san -- 大写字母的码点小于小写字母所以 Zhang San 排在 zhang san 前面存储 UUID、哈希值、Base64 编码这类数据时适合用这个。这些数据不需要语言级别的排序纯逐字节比较效率最高。CREATE TABLE token_store ( id BIGINT PRIMARY KEY AUTO_INCREMENT, token VARCHAR(64) COLLATE utf8mb4_0900_bin, user_id BIGINT, INDEX idx_token (token) );6. utf8mb4_bin —— 旧版二进制比较和 utf8mb4_0900_bin 功能类似但基于旧的排序算法。MySQL 8.0 保留了它主要是为了兼容从 5.7 及更早版本迁移过来的数据。-- 两者行为基本一致 SELECT A a COLLATE utf8mb4_bin; -- 0不相等 SELECT A a COLLATE utf8mb4_0900_bin; -- 0不相等新建的表不要再用了直接用 utf8mb4_0900_bin。7. utf8mb4_general_ci —— MySQL 5.7 默认MySQL 5.7 及之前版本的默认排序规则。不区分大小写但排序准确性不如 0900 系列。-- general_ci 和 0900_ai_ci 的排序差异 SELECT ß ss COLLATE utf8mb4_general_ci; -- 0不相等 SELECT ß ss COLLATE utf8mb4_0900_ai_ci; -- 1相等UCA 9.0 更准确从 MySQL 5.7 升级到 8.0 时要注意新表的默认排序规则变了。如果需要保持兼容-- 建表时显式指定 CREATE TABLE legacy_table ( id INT PRIMARY KEY, name VARCHAR(100) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;但新项目不建议再用这个。四、排序规则的四个生效级别MySQL 中排序规则可以在四个级别设置范围从小到大列级 表级 数据库级 服务器级优先级是列级最高服务器级最低。-- 1. 服务器级my.cnf 配置文件 [mysqld] character_set_server utf8mb4 collation_server utf8mb4_0900_ai_ci -- 2. 数据库级 CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_ci; -- 3. 表级 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- 4. 列级最精细的控制 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100) COLLATE utf8mb4_zh_0900_as_ci, code VARCHAR(50) COLLATE utf8mb4_0900_bin ) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- name 列用中文拼音排序code 列用二进制比较其他列用默认查看当前各级别的排序规则设置-- 服务器级 SHOW VARIABLES LIKE collation_server; -- 数据库级 SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME mydb; -- 表级 SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA mydb; -- 列级 SELECT COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA mydb AND TABLE_NAME products;五、实战场景排序规则选择流程场景1用户注册——需要精确匹配用户名不能因为大小写就重复注册Admin 和 admin 应该是不同的账号。CREATE TABLE account ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) COLLATE utf8mb4_0900_as_cs, password_hash VARCHAR(255), email VARCHAR(100) COLLATE utf8mb4_0900_as_cs, UNIQUE KEY uk_username (username), UNIQUE KEY uk_email (email) ); INSERT INTO account (username, email) VALUES (Admin, admintest.com); -- 再插入会失败 INSERT INTO account (username, email) VALUES (admin, admin2test.com); -- 成功因为 as_cs 区分大小写Admin ≠ admin如果用默认的 ai_ci第二条 INSERT 会因为唯一索引冲突而失败。场景2商品搜索——模糊匹配、大小写不敏感电商商品搜索通常不区分大小写用户搜 iphone 也能找到 iPhone。CREATE TABLE product ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) COLLATE utf8mb4_0900_ai_ci, brand VARCHAR(100) COLLATE utf8mb4_0900_ai_ci, INDEX idx_name (name) ); INSERT INTO product (name, brand) VALUES (iPhone 15 Pro Max, Apple), (iphone14 手机壳, 第三方), (IPHONE 充电器, Apple); SELECT * FROM product WHERE name LIKE %iphone%; -- 三条都能匹配到因为 ai_ci 不区分大小写场景3通讯录——中文按拼音排序手机通讯录的联系人列表中文要按拼音排序同时支持英文混排。CREATE TABLE contacts ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) COLLATE utf8mb4_zh_0900_as_ci, phone VARCHAR(20) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_ci; INSERT INTO contacts (name, phone) VALUES (赵云, 13800000001), (刘备, 13800000002), (诸葛亮, 13800000003), (关羽, 13800000004), (曹操, 13800000005), (David, 13800000006), (Alice, 13800000007); SELECT name FROM contacts ORDER BY name; -- Alice、David、曹操(C)、关羽(G)、刘备(L)、诸葛亮(Z)、赵云(Z) -- 英文按字母排中文按拼音排混排结果自然场景4多语言内容——需要区分重音存储法语、德语等带重音符号的内容搜索时要区分重音。CREATE TABLE i18n_content ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200) COLLATE utf8mb4_0900_as_cs, lang VARCHAR(10), content TEXT COLLATE utf8mb4_0900_as_cs ); INSERT INTO i18n_content (title, lang) VALUES (Résumé, fr), (Resume, en), (Naïve, fr), (Naive, en); SELECT * FROM i18n_content WHERE title Résumé; -- 只返回法语那条不会匹配 Resume六、查询时临时指定排序规则不想改表结构只是某次查询想换个排序规则用 COLLATE 关键字。-- 按拼音排序不管表默认是什么排序规则 SELECT name FROM user_test ORDER BY name COLLATE utf8mb4_zh_0900_as_ci; -- 查询时区分大小写 SELECT * FROM user_test WHERE name COLLATE utf8mb4_0900_as_cs Zhang San; -- 查询时不区分大小写 SELECT * FROM user_test WHERE name COLLATE utf8mb4_0900_ai_ci zhang san; -- JOIN 时指定排序规则避免排序规则冲突 SELECT a.name, b.order_no FROM user_test a JOIN orders b ON a.name COLLATE utf8mb4_0900_ai_ci b.customer_name;COLLATE 可以用在 WHERE、ORDER BY、GROUP BY、HAVING、JOIN ON 等几乎所有涉及字符串比较的地方。七、排序规则冲突——最容易踩的坑当两个不同排序规则的列做比较时MySQL 会报错CREATE TABLE t1 (name VARCHAR(50) COLLATE utf8mb4_0900_ai_ci); CREATE TABLE t2 (name VARCHAR(50) COLLATE utf8mb4_zh_0900_as_ci); -- 直接 JOIN 会报错 SELECT * FROM t1 JOIN t2 ON t1.name t2.name; -- ERROR 1267 (HY000): Illegal mix of collations -- (utf8mb4_0900_ai_ci,IMPLICIT) and -- (utf8mb4_zh_0900_as_ci,IMPLICIT) for operation 用 COLLATE 统一排序规则SELECT * FROM t1 JOIN t2 ON t1.name COLLATE utf8mb4_0900_ai_ci t2.name;根子上还是建表时统一排序规则别一个表 ai_ci另一个 as_ci。排序规则不一致导致索引失效这个坑藏得很深CREATE TABLE orders ( id INT PRIMARY KEY, customer_name VARCHAR(50) COLLATE utf8mb4_0900_ai_ci, INDEX idx_name (customer_name) ); -- 如果连接的客户端排序规则不同 SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; -- 这条查询用不上索引 SELECT * FROM orders WHERE customer_name 张三; -- 因为 utf8mb4_general_ci 和 utf8mb4_0900_ai_ci 不兼容 -- MySQL 做了隐式转换索引失效全表扫描用 EXPLAIN 验证SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci; EXPLAIN SELECT * FROM orders WHERE customer_name 张三; -- type: refkey: idx_name走了索引 SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; EXPLAIN SELECT * FROM orders WHERE customer_name 张三; -- type: ALLkey: NULL全表扫描索引失效八、排序规则对比实验把几种排序规则放一起对比看看-- 大小写敏感性对比 SELECT A a COLLATE utf8mb4_0900_ai_ci AS ai_ci, A a COLLATE utf8mb4_0900_as_cs AS as_cs, A a COLLATE utf8mb4_0900_bin AS bin; -- ai_ci: 1 as_cs: 0 bin: 0 -- 重音敏感性对比 SELECT Café Cafe COLLATE utf8mb4_0900_ai_ci AS ai_ci, Café Cafe COLLATE utf8mb4_0900_as_ci AS as_ci, Café Cafe COLLATE utf8mb4_0900_bin AS bin; -- ai_ci: 1 as_ci: 0 bin: 0 -- 排序结果对比 WITH chars AS ( SELECT a AS c UNION ALL SELECT A UNION ALL SELECT b UNION ALL SELECT B UNION ALL SELECT 张 UNION ALL SELECT 李 ) SELECT c, ROW_NUMBER() OVER (ORDER BY c COLLATE utf8mb4_0900_ai_ci) AS ai_ci_pos, ROW_NUMBER() OVER (ORDER BY c COLLATE utf8mb4_0900_bin) AS bin_pos, ROW_NUMBER() OVER (ORDER BY c COLLATE utf8mb4_zh_0900_as_ci) AS zh_pos FROM chars; -- ai_ci: aA(1), bB(2), 张(3), 李(4) 不区分大小写中文字母分开排 -- bin: A(1), B(2), a(3), b(4), 张(5), 李(6) 大写码点小严格按码点 -- zh: aA(1), bB(2), 李(3), 张(4) 中文按拼音排九、ALTER TABLE 修改排序规则已有表要改排序规则-- 改表的默认排序规则不影响已有列 ALTER TABLE user_test CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_ci; -- 改某个列的排序规则 ALTER TABLE user_test MODIFY name VARCHAR(50) COLLATE utf8mb4_zh_0900_as_ci; -- 整表转换包括所有字符串列 ALTER TABLE user_test CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_ci;注意几点CONVERT TO 会重建整张表数据量大时锁表时间长线上操作有风险唯一索引中相同的值可能变成不同或者反过来冲突先用 pt-online-schema-change 在测试环境验证一把-- 安全的做法先检查有没有冲突 SELECT name, COUNT(*) AS cnt FROM user_test GROUP BY name COLLATE utf8mb4_0900_as_cs HAVING cnt 1; -- 如果有结果说明改成 as_cs 后会出现重复数据十、性能影响不同排序规则对性能有影响。数据量越大越明显。比较开销排序从快到慢utf8mb4_0900_bin ≈ utf8mb4_bin utf8mb4_0900_ai_ci utf8mb4_0900_as_cs utf8mb4_zh_0900_as_ci二进制最快直接逐字节对比不走 Unicode 算法。带语言特定规则的需要查语言排序表慢一些。实际测试-- 创建测试数据 CREATE TABLE perf_test ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) ) CHARACTER SET utf8mb4; -- 插入10万条随机数据用存储过程或脚本 -- ... -- 对比不同排序规则的 ORDER BY 性能 SET profiling 1; ALTER TABLE perf_test COLLATE utf8mb4_0900_bin; SELECT name FROM perf_test ORDER BY name LIMIT 1000; ALTER TABLE perf_test COLLATE utf8mb4_0900_ai_ci; SELECT name FROM perf_test ORDER BY name LIMIT 1000; ALTER TABLE perf_test COLLATE utf8mb4_zh_0900_as_ci; SELECT name FROM perf_test ORDER BY name LIMIT 1000; SHOW PROFILE; -- bin 通常最快zh 系列略慢差距在 5%-15% 左右百万级以上数据排序规则对 ORDER BY、GROUP BY、DISTINCT 有明显影响。但正确性优先。选对排序规则比快几毫秒重要得多。十一、常用操作速查表操作SQL查看所有 utf8mb4 排序规则SHOW COLLATION WHERE Charset utf8mb4;查看服务器排序规则SHOW VARIABLES LIKE collation_server;查看数据库排序规则SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME db_name;查看表排序规则SHOW TABLE STATUS LIKE table_name;查看列排序规则SHOW FULL COLUMNS FROM table_name;建库时指定CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_ci;建表时指定CREATE TABLE t (...) CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_ci;改列排序规则ALTER TABLE t MODIFY col VARCHAR(100) COLLATE utf8mb4_0900_bin;查询时临时指定SELECT * FROM t WHERE col COLLATE utf8mb4_0900_as_cs Val;连接级设置SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;总结几个结论新项目直接用 utf8mb4 utf8mb4_0900_ai_ciMySQL 8.0 默认就是它别折腾中文按拼音排序用 utf8mb4_zh_0900_as_ciMySQL 8.0 才有5.7 没有精确匹配用户名、账号用 as_cs 结尾的避免大小写搞混排序规则不一致索引就失效建表时整个项目统一别再用 utf8mb4_general_ci 了那是 5.7 的默认8.0 只是兼容才保留排序规则能不改就不改ALTER TABLE CONVERT TO 会锁表线上操作有风险