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. 数据库实例级

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

2. 数据库级

create database mydb 
  character set utf8mb4 
  collate utf8mb4_general_ci;

3. 表级

create table case_sensitive_table (
    id int,
    username varchar(50)
) character set utf8mb4 collate utf8mb4_general_ci;

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 标准且性能更优

四、查看方法

## 查看数据库字符集和排序规则
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 指定区分大小写的排序规则

select * from table_name where column_name collate utf8mb4_bin = 'value'; -- utf8mb4_bin 需适配字符集

（2）使用 binary 运算符

select * from table_name where binary column_name = 'value';

（3）使用 like binary 语法

select * from table_name where column_name like binary 'value';

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

select * from table_name where cast(column_name as binary) = cast('value' as binary);

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

-- 修改列的排序规则
alter table table_name modify column_name varchar(255) collate utf8mb4_bin;
-- 修改表的默认排序规则
alter table table_name collate utf8mb4_bin;

2. 各方法对比

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