MySQL中的字符集以及比较规则

ZeonHon

已于 2024-07-22 16:28:51 修改

阅读量1k

点赞数 20

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-07-22 16:28:14 首次发布

本文链接：https://blog.csdn.net/weixin_64965172/article/details/140609708

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

2.1 MySQL中的utf8和utf8mb4

1 基本概念

计算机里面只能存储二进制数据，那我们如果想要存储字符串就要建立一个字符与二进制数据的映射关系

整个过程可以分为两步：

①要把哪些字符映射成二进制数据？

②如何映射？

将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码。

1.1 字符集的概念

字符集指的是某个字符范围的编码规则

举个例子：定义一个叫做ZeonHon的字符集，其中包含的字符范围和编码规则如下：

包含字符 'a' 、 'b' 、 'A' 、 'B'
编码规则如下：采用1个字节编码一个字符的形式，字符和字节的映射关系如下：

'a' -> 00000001 (十六进制：0x01)

'b' -> 00000010 (十六进制：0x02)

'A' -> 00000011 (十六进制：0x03)

'B' -> 00000100 (十六进制：0x04)

有了字符集之后，就可以表示一些简单的字符串了，比如：

aa -> 0000000100000001(十六进制：0x0101)

aA -> 0000000100000011(十六进制：0x0103)

cd无法表示，因为定义的ZeonHon字符集里面并没有收纳这两个字符

1.2 比较规则的概念

比较规则指的是针对某个字符集中的字符比较大小的一种规则

提到比较字符大小，容易直接想到比较它们对应的二进制数据的大小即可

比方说字符 'a' 的编码为 0x01 ，字符 'b' 的编码为 0x02 ，所以 'a' 小于 'b' ，这种简单的比较规则也可以被称为二进制比较规则（binary collation）

这种是比较简单的比较规则，但是现实业务状况往往更加复杂，比如在很多场合对于英文字符我们都是不区分大小写的，也就是说 'a' 和 'A' 是相等的，在这种场合下就不能简单粗暴的使用二进制比较规则了，这时候我们可以这样指定比较规则： 1. 将两个大小写不同的字符全都转为大写或者小写。 2. 再比较这两个字符对应的二进制数据。这是一种稍微复杂一点点的比较规则，但是实际生活中的字符不止英文字符一种，比如我们的汉字有几万之多，对于某一种字符集来说，比较两个字符大小的规则可以制定出很多种，也就是说同一种字符集可以有多种比较规则

1.3 一些重要的字符集

不同的字符集有着不同的字符范围，编码规则以及比较规则：

ASCII 字符集共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码，

我们看一些字符的编码方式： 'L' -> 01001100（十六进制：0x4C，十进制：76） 'M' -> 01001101（十六进制：0x4D，十进制：77）

ISO 8859-1 字符集共收录256个字符，是在 ASCII 字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名 latin1 。
GB2312 字符集收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容 ASCII 字符集，所以在编码方式上显得有些奇怪：如果该字符在 ASCII 字符集中，则采用1字节编码。否则采用2字节编码。这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式。

比方说字符串 '爱u' ，其中 '爱' 需要用2个字节进行编码，编码后的十六进制表示为 0xCED2 ， 'u' 需要用1个字节进行编码，编码后的十六进制表示为 0x75 ，所以拼合起来就是 0xCED275 。

tips：我们怎么区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？别忘了`ASCII`字符集只收录128个字符，使用0～127就可以表示全部字符，所以如果某个字节是在0～127之内的，就意味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。

GBK 字符集 GBK 字符集只是在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312 。
utf8 字符集收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，

比方说这样： 'L' -> 01001100（十六进制：0x4C） '啊' -> 111001011001010110001010（十六进制：0xE5958A）

对于同一个字符，采取不同的字符集也许会有不同的编码方式

比如对于汉字 '我' 来说， ASCII 字符集中根本没有收录这个字符， utf8 和 gb2312 字符集对汉字我的编码方式如下：

utf8编码：111001101000100010010001 (3个字节，十六进制表示是：0xE68891)

gb2312编码：1100111011010010 (2个字节，十六进制表示是：0xCED2)

2 MySQL中支持的字符集以及比较规则

2.1 MySQL中的utf8和utf8mb4

上文提到utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能（MySQL设计的时候，有很多地方都是为了节省空间或者内存而设计的）所以就有了两个不同的概念：

utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。
utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

在MySQL中utf8是utf8mb3的缩写和简称，一般看到utf8就是指的utf8mb3

要是想要存储emoji的话，则需要使用utf8mb4

2.2 查看字符集

查看当前 MySQL 中支持的字符集可以用下边这个语句：

SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

（下图来自《从根上理解MySQL：MySQL是怎样运行的》小孩子4919著）

Default collation 列表示这种字符集中一种默认的比较规则。注意返回结果中的最后一列 Maxlen ，它代表该种字符集表示一个字符最多需要几个字节。

2.3 查看比较规则

查看 MySQL 中支持的比较规则的命令如下：

SHOW COLLATION [LIKE 匹配的模式];

下图是utf8字符集的比较规则

字符集名称后紧跟的是主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则

名称的后缀也代表了不同的含义：意味着该比较规则是否区分语言中的重音、大小写等等

具体可以用的值如下： |后缀|英文释义|描述| |:--:|:--:|:--:| | _ai | accent insensitive |不区分重音| | _as | accent sensitive |区分重音| | _ci | case insensitive |不区分大小写| | _cs | case sensitive |区分大小写| | _bin | binary |以二进制方式比较|

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则， SHOW COLLATION 的返回结果中的 Default 列的值为 YES 的就是该字符集的默认比较规则，比方说 utf8 字符集默认的比较规则就是 utf8_general_ci

2.4 四个不同级别的字符集以及比较规则

服务器级别

character_set_server 表示服务器级别的字符集， collation_server 表示服务器级别的比较规则。

数据库级别

创建和修改数据库时可以指定字符集和比较规则：

CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称];

character_set_database 表示当前数据库的字符集， collation_database 表示当前默认数据库的比较规则，这两个系统变量是只读的，不能修改。如果没有指定当前默认数据库，则变量与相应的服务器级系统变量具有相同的值。

表级别

创建和修改表的时候指定表的字符集和比较规则：

CREATE TABLE 表名 (列的信息) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]];

ALTER TABLE 表名 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

列级别

创建和修改列定义的时候可以指定该列的字符集和比较规则：

CREATE TABLE 表名( 列名字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列... );

ALTER TABLE 表名 MODIFY 列名字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

3 客户端和服务器端通信中的字符集

首先先放总结：下图是客户端和服务器通信时字符在其中的变化过程

我们知道字符 '我' 在 utf8 字符集编码下的字节串长这样： 0xE68891 ，如果一个程序把这个字节串发送到另一个程序里，另一个程序用不同的字符集去解码这个字节串，假设使用的是 gbk 字符集来解释这串字节，

解码过程就是这样的：

1. 首先看第一个字节 0xE6 ，它的值大于 0x7F （十进制：127），说明是两字节编码，继续读一字节后是 0xE688 ，然后从 gbk 编码表中查找字节为 0xE688 对应的字符，发现是字符 '鎴'

2. 继续读一个字节 0x91 ，它的值也大于 0x7F ，再往后读一个字节发现没有了，所以这是半个字符。

3. 所以 0xE68891 被 gbk 字符集解释成一个字符 '鎴' 和半个字符。

假设用 iso-8859-1 ，也就是 latin1 字符集去解释这串字节，解码过程如下：

1. 先读第一个字节 0xE6 ，它对应的 latin1 字符为 æ 。

2. 再读第二个字节 0x88 ，它对应的 latin1 字符为 ˆ 。

3. 再读第二个字节 0x91 ，它对应的 latin1 字符为 ‘ 。

4. 所以整串字节 0xE68891 被 latin1 字符集解释后的字符串就是 'æˆ‘'

如果对于同一个字符串编码和解码使用的字符集不一样，会产生意想不到的结果，作为人类的我们看上去就像是产生了乱码一样。

3.1 MySQL中的字符集转换

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量

为了方便理解整个过程，下面是一个简单的例子：

1. 客户端发送请求所使用的字符集一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：类 Unix 系统使用的是 utf8 Windows 使用的是 gbk 例如我在使用的 macOS 操作系统时，客户端使用的就是 utf8 字符集。所以字符 '我' 在发送给服务器的请求中的字节形式就是： 0xE68891

tips： 如果你使用的是可视化工具，比如navicat之类的
这些工具可能会使用自定义的字符集来编 码发送到服务器的字符串，而不采用操作系统默认的字符集

2. 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client ，然后把这串字节转换为 character_set_connection 字符集编码的字符。由于我的计算机上 character_set_client 的值是 utf8 ，首先会按照 utf8 字符集对字节串 0xE68891 进行解码，得到的字符串就是 '我' ，然后按照 character_set_connection 代表的字符集，也就是 gbk 进行编码，得到的结果就是字节串 0xCED2 。

3. 因为表 t 的列 col 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为 0xCED2 的记录，最后找到了一条记录。小贴士：如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行一次字符集转换。

4. 上一步骤找到的记录中的 col 列其实是一个字节串 0xCED2 ， col 列是采用 gbk 进行编码的，所以首先会将这个字节串使用 gbk 进行解码，得到字符串 '我' ，然后再把这个字符串使用 character_set_results 代表的字符集，也就是 utf8 进行编码，得到了新的字节串： 0xE68891 ，然后发送给客户端。

5. 由于客户端是用的字符集是 utf8 ，所以可以顺利的将 0xE68891 解释成字符我，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果。

从这个分析中我们可以得出这么几点需要注意的地方：

服务器认为客户端发送过来的请求是用 character_set_client 编码的。

假设你的客户端采用的字符集和 character_set_client 不一样的话，这就会出现意想不到的情况。比如我的客户端使用的是 utf8 字符集，如果把系统变量 character_set_client 的值设置为 ascii 的话，服务器可能无法理解我们发送的请求，更别谈处理这个请求了。

服务器将把得到的结果集使用 character_set_results 编码后发送给客户端。

假设你的客户端采用的字符集和 character_set_results 不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏幕上出现乱码。比如我的客户端使用的是 utf8 字符集，如果把系统变量 character_set_results 的值设置为 ascii 的话，可能会产生乱码。

character_set_connection 只是服务器在将请求的字节串从 character_set_client 转换为 character_set_connection 时使用，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用 character_set_connection 代表的字符集进行编码。比如你把 character_set_client 设置为 utf8 ，把 character_set_connection 设置成 ascii ，那么此时你如果从客户端发送一个汉字到服务器，那么服务器无法使用 ascii 字符集来编码这个汉字，就会向用户发出一个警告。

为了方便我们设置， MySQL 提供了一条非常简便的语句：

SET NAMES 字符集名;

这一条语句产生的效果和我们执行这3条的效果是一样的：

SET character_set_client = 字符集名;

SET character_set_connection = 字符集名;

SET character_set_results = 字符集名;

比方说我的客户端使用的是 utf8 字符集，所以需要把这几个系统变量的值都设置为 utf8 ：

mysql> SET NAMES utf8;

Query OK, 0 rows affected (0.00 sec)