MySQL 的字符集与排序规则

2023-01-19

通俗讲，字符集就是字符码的集合。在 MySQL 中，字符集的选择影响字符的存储；字符集选择不好，不仅影响存储展示，还会有问题，例如乱码。

在业务中常用的字符集是 UTF-8 字符集，mysql 有两种这样的字符集：utf8、utf8mb4，它们的区别如下：

utf8: 支持最长 3 Byte 的字符编码，但一部分 UTF-8的 4 Byte 编码不支持，例如 emoji。
utf8mb4：支持最长 4 Byte 的字符编码。这是业务中使用最多的字符集，从 MySQL 8.0 开始成为默认字符集。

字符的排序规则（collate）：字符在比较、排序时以及大小写敏感的规则。涉及字符比较的操作均与其相关，例如：排序、分组、索引、比较（=、>、<等）。MySQL 中有些字符排序规则是忽略大小写的，例如 utf8mb4_general_ci 排序规则，这种带有 ci 后缀的是大小写不敏感的标志（ci 即 case insensitive 的缩写）。

查看字符排序规则

使用 show collation 命令查看 MySQL 的字符排序规则，可以看到排序规则对应的字符集和字符集默认的排序规则。

一些字段大小写敏感很重要

通常需要看业务的需求。举个栗子，例如用户表中常用的 username 字段（通常作为邮箱前缀或唯一标识），在 username 为唯一索引时，大小写不同也会认为字段已经存在，在执行第二次插入操作时会抛出索引重复异常。

 1-- version mysql 5.7
 2
 3create table user (
 4	id int(11) not null auto_increment,
 5	username varchar(127) not null default '',
 6	primary key (id),
 7	unique key uniq_idx_username (username)
 8) engine=innodb charset=utf8mb4 collate=utf8mb4_general_ci;
 9
10insert user (id, username) values (1, "zhangsan");
11insert user (id, username) values (2, "ZhangSan");
12
13-- OUTPUT
14
15mysql> insert user (id, username) values (1, "zhangsan");
16-- 插入第二条数据报错，唯一索引重复
17mysql> insert user (id, username) values (2, "ZhangSan");
18
19ERROR 1062 (23000): Duplicate entry 'ZhangSan' for key 'uniq_idx_username'

字符集不同的字段可能引发索引失效

当两个字段字符集不同，在进行比较时会进行字符集转换，转换的过程会调用 MySQL 内置函数（字段类型转换同理）。

字符集与字符排序规则实践

1、保持一个数据库的表都使用同样的字符集是明智的，可以较大程度避免连表查询条件字段隐性的字符集转换导致索引失效的问题。

2、在业务中通常推荐使用 utf8mb4 字符集，能够满足绝大多数字符的存储（例如 emoji 存储）。字符排序规则如果没有特殊需求，一般使用大小写不敏感的 utf8mb4_general_ci；需要大小写敏感时，推荐使用 utf8mb4_bin。

3、当使用 utf8mb4 字符集，默认的字段排序规则是 utf8mb4_general_ci。在创建表时，如果字段没有大小写敏感需求，只需要指定表的默认字符集就行了；特殊字段对大小写敏感有需求时再单独指定。示例如下：

1-- 所有字段都是 utf8mb4 字符集，除 username 外均使用 utf8mb4_general_ci 排序规则
2create table user(
3	id int(11) not null auto_increment,
4	username varchar(127) collate utf8mb4_bin not null,
5	nickname varchar(127) default null,
6	primary key (id),
7	unique key uniq_idx_username (username)
8) engine=innodb default charset=utf8mb4;

（EOF）

<< Previous Post

Next Post >>

#MySQL