MySQL 字符集、排序规则与查询关系详解

MySQL 查询是否区分大小写及重音敏感，取决于创建时指定的字符集（character set）和排序规则（collation）。
（1）字符集（Character Set）：规定可存储的字符，如 utf8、utf8mb4、latin1 等。
（2）排序规则（Collation）：确定字符比较与排序规则，如是否区分大小写、重音等。
注意：
（1）每个排序规则必须对应特定的字符集，如 utf8mb4_bin 仅用于 utf8mb4 字符集。
（2）“重音敏感”（Accent Sensitive）：指排序规则将带重音符号的字母视为与基本字母相同或不同的特性，如 'cafe' 与 'café'。
在 MySQL 5.7 中，默认字符集和排序规则是 latin1 和 latin1_swedish_ci。实际应用里，建议使用 utf8mb4 字符集以支持完整 Unicode 字符（含 emoji），并采用默认对应的 utf8mb4_general_ci 排序规则，该规则下查询不区分大小写。
一、常见排序规则

排序规则对应字符集大小写敏感重音敏感适用版本说明utf8_general_ciutf8❌❌所有版本基本 Unicode 比较utf8mb4_general_ciutf8mb4❌❌5.5.3+默认的 utf8mb4 排序规则latin1_swedish_cilatin1❌❌所有版本传统西欧默认排序规则utf8mb4_unicode_ciutf8mb4❌✅5.5.3+符合 Unicode 标准的更准确排序utf8mb4_0900_ai_ciutf8mb4❌❌8.0+基于 Unicode 9.0 标准utf8_binutf8✅✅所有版本二进制比较（严格区分大小写）utf8mb4_binutf8mb4✅✅5.5.3+utf8mb4 的二进制比较utf8mb4_unicode_csutf8mb4✅✅5.5.3+已弃用，改用 utf8mb4_binutf8mb4_0900_as_csutf8mb4✅✅8.0+严格区分大小写和重音表格说明：
（1）可通过排序规则后缀判断其是否支持大小写和重音敏感，ci、cs、ai、as 分别表示不区分大小写、区分大小写、不区分重音和区分重音。
（2）带 bin 后缀的排序规则基于二进制（Binary）比较，严格区分和重音。
二、设置方法

1. 数据库实例级

1. # my.conf 或 my.ini
2. [mysqld]
3. character-set-server=utf8mb4
4. collation-server=utf8mb4_general_ci

复制代码

2. 数据库级

1. CREATE DATABASE mydb
2.   CHARACTER SET utf8mb4
3.   COLLATE utf8mb4_general_ci;

复制代码

3. 表级

1. CREATE TABLE case_sensitive_table (
2.     id INT,
3.     username VARCHAR(50)
4. ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

复制代码

4. 列级

1. CREATE TABLE case_sensitive_table (
2.     id INT,
3.     username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
4. ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

复制代码

三、设置建议

1. 注意事项

（1）性能影响：使用区分大小写的比较可能会影响查询性能，混合使用不同字符集也可能影响查询优化
（2）存储空间：不同字符集占用的存储空间不同 (如 utf8mb4 比 latin1 占用更多空间)
（3）索引限制：某些情况下不同字符集的列不能直接比较或连接
（4）转换风险：修改已有数据的字符集前需谨慎，可能导致数据丢失或损坏
2. 最佳实践

（1）尽量保持各表及表内各列字符集和排序规则一致，除非有特殊需求
（2）修改已有数据的字符集前先备份数据
（3）对于需要区分大小写的列 (如用户名、验证码)，明确指定使用_bin排序规则
（4）应文档化说明使用不同字符集/排序规则的原因
（5）MySQL 8.0 默认使用 utf8mb4 + utf8mb4_0900_ai_ci，它符合更新的 Unicode 标准且性能更优
四、查看方法

1. ## 查看数据库字符集和排序规则
3. SHOW VARIABLES LIKE 'character_set_server';
4. SHOW VARIABLES LIKE 'collation_server';
6. ## 查看某表字符集和序规则
7. SHOW CREATE TABLE `table_name`; # CHARSET 后面为字符集
8. SHOW TABLE STATUS LIKE 'table_name'; # Collation 列表示排序规则

复制代码

五、查询时如何强制区分大小写

1. 强制方法

如遇到不支持区分大小写的排序规则，可使用以下方法强制启用：
（1）使用 COLLATE 指定区分大小写的排序规则

1. SELECT * FROM table_name WHERE column_name COLLATE utf8mb4_bin = 'Value'; -- utf8mb4_bin 需适配字符集

复制代码

（2）使用 BINARY 运算符

1. SELECT * FROM table_name WHERE BINARY column_name = 'Value';

复制代码

（3）使用 LIKE BINARY 语法

1. SELECT * FROM table_name WHERE column_name LIKE BINARY 'Value';

复制代码

（4）使用区分大小写的函数

1. SELECT * FROM table_name WHERE CAST(column_name AS BINARY) = CAST('Value' AS BINARY);

复制代码

（5）修改列或表的字符集排序规则

1. -- 修改列的排序规则
2. ALTER TABLE table_name MODIFY column_name VARCHAR(255) COLLATE utf8mb4_bin;
4. -- 修改表的默认排序规则
5. ALTER TABLE table_name COLLATE utf8mb4_bin;

复制代码

2. 各方法对比

方法是否利用索引持久性字符集兼容性推荐场景缺点使用 COLLATE 指定区分大小写的排序规则✅ 是仅当前查询需匹配列字符集临时区分大小写查询需显式指定，字符集需一致使用 BINARY 运算符⚠️ 部分场景仅当前查询任意字符集快速兼容性写法某些版本无法利用索引使用 LIKE BINARY 语法⚠️ 部分场景仅当前查询任意字符集不推荐（语义混淆）性能差，模糊匹配语义不清晰使用区分大小写的函数⚠️ 部分场景仅当前查询任意字符集极特殊场景性能最差，语法冗长修改列或表的字符集排序规则✅ 是✅ 永久生效需匹配列字符集需要长期区分大小写的字段修改表结构可能锁表3. 使用建议

如仅当前查询需区分大小写，建议使用 COLLATE 指定区分大小写的排序规则；如需永久生效，建议修改列或表的字符集排序规则。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！