MySQL 比较规则（Collation）详解

一、概述比较规则Collation是一组定义如何比较和排序字符集中字符的规则。它决定了字符串的大小写是否敏感A a重音符号是否敏感é e多语言字符的排序顺序例如瑞典语中Ö排在Z之后字符串的相等性判断和ORDER BY 结果比较规则始终依附于某个字符集Character Set两者相辅相成。MySQL 中每个字符集至少有一个默认比较规则并支持多个可选的比较规则。二、比较规则的核心概念2.1 字符集 vs 比较规则维度字符集Character Set比较规则Collation定义字符到二进制字节的编码映射字符之间比较、排序的规则示例utf8mb4, latin1, gbkutf8mb4_unicode_ci, latin1_swedish_ci决定如何存储数据如何比较、排序、分组数据2.2 比较规则的主要属性大小写敏感Case SensitivityA和a是否视为相同字符。重音敏感Accent Sensitivityé和e是否视为相同字符。二进制Binary直接比较字符的二进制编码值最为严格。多语言排序Language-specific某些语言中字母的顺序与英语不同如德语ß排序等同于ss。2.3 比较规则命名规范MySQL 比较规则命名格式为字符集名称_语言/通用_敏感度或字符集名称_bin常见后缀及含义后缀含义示例_ciCase Insensitive大小写不敏感utf8mb4_general_ci_csCase Sensitive大小写敏感utf8mb4_cs部分字符集支持_aiAccent Insensitive重音不敏感utf8mb4_unicode_aiMySQL 8.0_asAccent Sensitive重音敏感utf8mb4_unicode_as_binBinary二进制比较utf8mb4_bin_0900_ai_ci基于 UCA 9.0.0不区分大小写/重音MySQL 8.0 默认utf8mb4_0900_ai_ci命名示例解读utf8mb4_general_ci字符集为utf8mb4通用规则大小写不敏感A a重音敏感é ≠ e。utf8mb4_unicode_ci基于 Unicode 排序标准大小写不敏感重音不敏感é e排序更准确但稍慢。utf8mb4_0900_ai_ci基于 UCA 9.0.0同时忽略大小写和重音MySQL 8.0 默认。utf8mb4_bin直接比较二进制值A的编码 0x41 小于a的 0x61严格区分大小写和重音。三、常用比较规则详解3.1 utf8mb4 字符集的常用比较规则比较规则大小写重音特点推荐场景utf8mb4_general_ci不敏感敏感速度快部分特殊字符排序不准确如 ß 排序为 s 而非 ss对排序准确性要求不高的英文/中文环境utf8mb4_unicode_ci不敏感不敏感基于 Unicode 标准排序准确性能略低于 general多语言国际化应用utf8mb4_0900_ai_ci不敏感不敏感MySQL 8.0 默认基于 UCA 9.0.0准确性和性能平衡MySQL 8.0 推荐默认选择utf8mb4_bin敏感敏感直接比较二进制值需要区分大小写、存储哈希值、唯一标识符utf8mb4_unicode_520_ci不敏感不敏感基于 UCA 5.2.0MySQL 5.6 引入中间版本兼容性3.2 latin1 字符集示例比较规则说明latin1_swedish_ciLatin1 默认瑞典语排序Ö 排在 Z 之后latin1_general_ci通用规则Ö O排序与英语类似latin1_bin二进制比较3.3 比较规则对排序的影响实例-- 创建测试表 CREATE TABLE collation_test ( name VARCHAR(10) ) CHARACTER SET utf8mb4; -- 插入数据 INSERT INTO collation_test VALUES (A), (a), (B), (b), (Ö), (O); -- 使用 utf8mb4_general_ci 排序重音敏感Ö ≠ O ALTER TABLE collation_test MODIFY name VARCHAR(10) COLLATE utf8mb4_general_ci; SELECT * FROM collation_test ORDER BY name; -- 结果A, a, B, b, O, Ö大写字母排在前小写在后Ö 视为独立字符 -- 使用 utf8mb4_unicode_ci 排序重音不敏感Ö O ALTER TABLE collation_test MODIFY name VARCHAR(10) COLLATE utf8mb4_unicode_ci; SELECT * FROM collation_test ORDER BY name; -- 结果A, a, O, Ö, B, bO 和 Ö 被视为等同按原始顺序排列 -- 使用 utf8mb4_bin 排序严格二进制 ALTER TABLE collation_test MODIFY name VARCHAR(10) COLLATE utf8mb4_bin; SELECT * FROM collation_test ORDER BY name; -- 结果A, B, O, a, b, Ö按 ASCII/Unicode 码点排序四、比较规则的作用范围比较规则可以在以下层级指定优先级从低到高层级设置方式默认继承服务器级collation_server 变量编译时默认或配置文件数据库级CREATE DATABASE ... COLLATE继承服务器级表级CREATE TABLE ... COLLATE继承数据库级列级列定义中的 COLLATE继承表级查询级表达式中的 COLLATE 子句临时覆盖查看当前各层级比较规则SHOW VARIABLES LIKE collation%; -- 服务器和连接级 SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME your_db; SHOW CREATE TABLE your_table; -- 表级和列级五、比较规则对操作的影响5.1 字符串比较WHERE, JOIN, GROUP BY比较规则直接决定、LIKE、IN、GROUP BY等操作的行为。-- 大小写不敏感时查询 abc 能匹配 ABC SELECT * FROM t WHERE name abc; -- 如果 collation 是 _ci返回大小写所有匹配 -- 大小写敏感时必须精确匹配 SELECT * FROM t WHERE name abc; -- 只返回 abc不返回 ABC5.2 排序ORDER BY不同比较规则会导致相同的ORDER BY产生不同的结果顺序特别是涉及特殊字母或大小写时。5.3 索引使用MySQL 索引基于列的比较规则构建。当查询条件中使用了与列不同的比较规则时索引可能失效。-- 列 collation utf8mb4_unicode_ci查询时使用 utf8mb4_bin SELECT * FROM t WHERE name abc COLLATE utf8mb4_bin; -- 无法使用索引需要全表扫描最佳实践保持查询条件和列使用相同的比较规则或在连接级别统一。5.4 性能差异_general_ci比较快但某些语言排序不精确。_unicode_ci及_0900_ai_ci更准确但内部需要更复杂的映射表性能稍低通常差距可接受。_bin最快直接字节比较但严格区分大小写和重音。六、比较规则的查看与设置6.1 查看可用比较规则-- 查看所有比较规则 SHOW COLLATION; -- 查看特定字符集的比较规则 SHOW COLLATION WHERE Charset utf8mb4; -- 查看默认比较规则 SHOW CHARACTER SET LIKE utf8mb4;6.2 设置比较规则服务器级永久编辑/etc/my.cnf[mysqld] collation-server utf8mb4_unicode_ci character-set-server utf8mb4连接级临时SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; -- 或单独设置 SET collation_connection utf8mb4_unicode_ci;数据库级CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 修改已有数据库 ALTER DATABASE mydb COLLATE utf8mb4_unicode_ci;表级CREATE TABLE t (id INT) COLLATE utf8mb4_unicode_ci; ALTER TABLE t COLLATE utf8mb4_bin; -- 只改默认不影响已有列列级CREATE TABLE t (name VARCHAR(10) COLLATE utf8mb4_unicode_ci); ALTER TABLE t MODIFY name VARCHAR(10) COLLATE utf8mb4_bin;查询级覆盖SELECT * FROM t WHERE name abc COLLATE utf8mb4_bin; SELECT * FROM t ORDER BY name COLLATE utf8mb4_bin;七、常见问题与陷阱7.1 大小写不敏感带来的意外匹配-- 用户表用户名唯一且大小写不敏感 CREATE TABLE users (username VARCHAR(20) COLLATE utf8mb4_general_ci UNIQUE); INSERT INTO users VALUES (Admin); INSERT INTO users VALUES (admin); -- 报错Duplicate entry admin与 Admin 冲突解决使用utf8mb4_bin或utf8mb4_cs比较规则或应用层校验。7.2 排序结果与预期不符-- 德语中 ß 应该排在 ss 附近但 utf8mb4_general_ci 中 ß 被视为 s SELECT * FROM german_words ORDER BY word COLLATE utf8mb4_general_ci; -- 结果不准确 SELECT * FROM german_words ORDER BY word COLLATE utf8mb4_unicode_ci; -- 准确7.3 隐式转换导致索引失效-- 列 collation 为 utf8mb4_unicode_ci连接 collation 为 utf8mb4_general_ci SELECT * FROM t WHERE name abc; -- MySQL 需要转换列值无法使用索引解决统一连接和列的 collation或在查询中显式使用相同的 collation。7.4 不同字符集之间的比较-- 列是 utf8mb4常量是 latin1 字符集 SELECT * FROM t WHERE name _latin1abc; -- 会触发字符集转换可能导致索引失效或错误建议避免跨字符集比较必要时显式转换CONVERT(name USING latin1)。八、如何选择合适的比较规则8.1 决策树是否需要严格区分大小写/重音是 → 使用_bin或_cs如utf8mb4_bin否 → 下一步是否主要处理英文/数字/中文对排序准确性要求不高 →utf8mb4_general_ci性能好需要准确的多语言排序德语、法语、瑞典语等→ 下一步MySQL 版本8.0 →utf8mb4_0900_ai_ci最佳平衡5.7 及以下 →utf8mb4_unicode_ci8.2 典型场景推荐场景推荐比较规则英文博客、日志系统utf8mb4_general_ci国际化社交应用utf8mb4_0900_ai_ci8.0或 utf8mb4_unicode_ci用户登录名需区分大小写utf8mb4_bin唯一标识符UUID、Tokenutf8mb4_bin全文检索与字段存储规则一致通常 utf8mb4_unicode_ci中文为主的系统utf8mb4_general_ci 或 utf8mb4_unicode_ci 均可中文排序按拼音或笔画需额外处理九、高级主题比较规则与字符集转换9.1 表达式中的 COLLATE 子句-- 强制使用特定比较规则 SELECT a A COLLATE utf8mb4_bin; -- 返回 0 SELECT a A COLLATE utf8mb4_general_ci; -- 返回 1 -- 混合比较时需要显式指定 SELECT name FROM t WHERE name abc COLLATE utf8mb4_bin;9.2 字符集转换函数-- 转换字符集并保留原有比较规则 SELECT CONVERT(name USING utf8mb4) FROM t; -- 同时改变比较规则 SELECT CAST(name AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin FROM t;十、总结要点说明比较规则决定比较、排序、分组的行为与字符集紧密相关命名后缀 _ci, _cs, _ai, _as, _bin 表示敏感属性了解含义便于选择MySQL 8.0 推荐 utf8mb4_0900_ai_ci平衡准确性和性能严格区分大小写/重音时用 _bin例如用户名、密码哈希保持层级一致服务器、数据库、表、列、连接尽量统一注意索引使用比较规则不一致会导致索引失效快速检查清单[ ] 确认数据库默认比较规则符合业务需求[ ] 重要表的字符串列使用合适的比较规则[ ] 应用连接使用SET NAMES ... COLLATE ...统一[ ] 避免在查询中隐式或显式使用不同的比较规则[ ] 测试 ORDER BY 结果是否符合预期