MySQL 字符集、排序规则与查询关系最佳实践！
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_ci | utf8 | ❌ | ❌ | 所有版本 | 基本 Unicode 比较 |
| utf8mb4_general_ci | utf8mb4 | ❌ | ❌ | 5.5.3+ | 默认的 utf8mb4 排序规则 |
| latin1_swedish_ci | latin1 | ❌ | ❌ | 所有版本 | 传统西欧默认排序规则 |
| utf8mb4_unicode_ci | utf8mb4 | ❌ | ✅ | 5.5.3+ | 符合 Unicode 标准的更准确排序 |
| utf8mb4_0900_ai_ci | utf8mb4 | ❌ | ❌ | 8.0+ | 基于 Unicode 9.0 标准 |
| utf8_bin | utf8 | ✅ | ✅ | 所有版本 | 二进制比较（严格区分大小写） |
| utf8mb4_bin | utf8mb4 | ✅ | ✅ | 5.5.3+ | utf8mb4 的二进制比较 |
| utf8mb4_unicode_cs | utf8mb4 | ✅ | ✅ | 5.5.3+ | 已弃用，改用 utf8mb4_bin |
| utf8mb4_0900_as_cs | utf8mb4 | ✅ | ✅ | 8.0+ | 严格区分大小写和重音 |
表格说明：
（1）可通过排序规则后缀判断其是否支持大小写和重音敏感，ci、cs、ai、as 分别表示不区分大小写、区分大小写、不区分重音和区分重音。
（2）带 bin 后缀的排序规则基于二进制（Binary）比较，严格区分和重音。
二、设置方法
1. 数据库实例级
|
1
2
3
4
|
# my.conf 或 my.ini[mysqld]character-set-server=utf8mb4collation-server=utf8mb4_general_ci |
2. 数据库级
|
1
2
3
|
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
3. 表级
|
1
2
3
4
|
CREATE TABLE case_sensitive_table (id INT,username VARCHAR(50)) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
4. 列级
|
1
2
3
4
|
CREATE TABLE case_sensitive_table (id INT,username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin) 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
2
3
4
5
6
|
## 查看数据库字符集和排序规则SHOW VARIABLES LIKE 'character_set_server';SHOW VARIABLES LIKE 'collation_server';## 查看某表字符集和序规则SHOW CREATE TABLE `table_name`; # CHARSET 后面为字符集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
3
4
|
-- 修改列的排序规则ALTER TABLE table_name MODIFY column_name VARCHAR(255) COLLATE utf8mb4_bin;-- 修改表的默认排序规则ALTER TABLE table_name COLLATE utf8mb4_bin; |
2. 各方法对比
| 方法 | 是否利用索引 | 持久性 | 字符集兼容性 | 推荐场景 | 缺点 |
|---|---|---|---|---|---|
| 使用 COLLATE 指定区分大小写的排序规则 | ✅ 是 | 仅当前查询 | 需匹配列字符集 | 临时区分大小写查询 | 需显式指定，字符集需一致 |
| 使用 BINARY 运算符 | ⚠️ 部分场景 | 仅当前查询 | 任意字符集 | 快速兼容性写法 | 某些版本无法利用索引 |
| 使用 LIKE BINARY 语法 | ⚠️ 部分场景 | 仅当前查询 | 任意字符集 | 不推荐（语义混淆） | 性能差，模糊匹配语义不清晰 |
| 使用区分大小写的函数 | ⚠️ 部分场景 | 仅当前查询 | 任意字符集 | 极特殊场景 | 性能最差，语法冗长 |
| 修改列或表的字符集排序规则 | ✅ 是 | ✅ 永久生效 | 需匹配列字符集 | 需要长期区分大小写的字段 | 修改表结构可能锁表 |
3. 使用建议
如仅当前查询需区分大小写，建议使用 COLLATE 指定区分大小写的排序规则；如需永久生效，建议修改列或表的字符集排序规则。
到此这篇关于MySQL 字符集、排序规则与查询关系详解的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需129元，即可下载本站文章涉及的文件和软件。