字符集与比较规则及在Mysql中的应用

可怕的竹鼠商

已于 2022-09-30 17:52:54 修改

阅读量382

点赞数

分类专栏：数据库基础知识文章标签： mysql 服务器

于 2022-09-30 17:44:23 首次发布

本文链接：https://blog.csdn.net/a982847942/article/details/127127172

版权

数据库基础知识专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.字符集与编码方案

**为什么需要字符集和编码方案，为什么不将字符串直接存储到存储介质上呢？**因为计算机只能存储二进制数据，因此任何非二进制的数据都必须要转化为二进制数据才能进行存储。因此必须要将字符串也转换为对应的二进制串进行存储。
什么是字符集？仅仅是一套从字符到数字的映射字典，它只规定了应该用什么数字来标识字符，仅此而已，至于计算机在存储的时候应该用什么字节来标识，字符集是根本不管这事的。Unicode, GB2312, ASCII都属于字符集
什么是编码方案？将指定的字符集映射为二进制序列有很多中方法，这其中的任何一种可行的方案都可以称为编码方案。但是编码方案有优劣之分，好的编码方案不仅可以节省存储空间，而且涉及到网络传输时还能节省带宽。
说了这么多，我们举一个简单的例子来理解一下，假定一个给定的字符集baby中包含字符：‘a’,‘b’,‘A’,‘B’。并规定’a’ —> 0 ‘b’ —> 1 ‘A’ —> 2 ‘B’ —> 3。那么一种可行的编码方案可能如下:
‘a’ —> 0000 0000
‘b’ —> 0000 0001
‘A’ —> 0000 0010
‘B’ —> 0000 0011
上面的编码方案中只用一个字节来表示字符集中的每一个字符，这也称为定长编码。当然，你也可以对某些字符用一个字节来编码，而对另外的字符用2个或3个等字节来编码，这种字符集中不同字符所占用的字节不同的编码方案也叫作变长编码。

几种常用的字符集如下：

ASCII

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码。具体对照关系见https://www.habaijian.com/。

ISO-8859-？

ISO-8859-？是对ASCII码的扩展，共表示256个字符，根据不同的扩展形式有不同的名称。
其中ISO-8859-1共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。
ISO-8859-7共收录256个字符，是在ASCII字符集的基础上又扩充了128个希腊常用字符，也可以使用1个字节来进行编码。

GB2312

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：
如果该字符在ASCII字符集中，则采用1字节编码。
否则采用2字节编码。

GBK

GBK是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的，即不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1。GBK包含全部中文字符，是国家编码，通用性比UTF8差，不过UTF8占用的数据库比GBK大。

Unicode

准确来讲，在Unicode之前的字符集，即代表了字符集也代表了编码规则。而Unicode则仅仅代表了字符集，对应的编码规则有utf8，utf16，utf32。不严谨的说Unicode是一种字符编码，让每个字符和一个数字对应起来，仅此而已，至于这个数字如何存储它就不管了。utf8就是定义了如何具体存储这个编码数字的一种方法。
utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。

2.比较规则

借用上面的例子：字符集baby中包含字符：‘a’,‘b’,‘A’,‘B’。并规定’a’ —> 0 ‘b’ —> 1 ‘A’ —> 2 ‘B’ —> 3。
‘a’ —> 0000 0000
‘b’ —> 0000 0001
‘A’ —> 0000 0010
‘B’ —> 0000 0011
此时我们有了字符集和编码规则，但仍存在一个问题，'a’和’A’进行比较时是相等吗？很显然这个问题的答案不是固定的，要根据你的使用场景来决定，这就涉及到比较规则了。如果你的场景中不区分大小写，那么很明显对应的比较规则中’a’和’A’应该是相等的。
对于上面这个字符集我们可以简单给出两种比较规则:
1.直接使用二进制进行比较，此时只用比较每个字符对应的二进制序列的大小即可。
2.忽略大小写进行比较，这时我们就需要将两个大小写不同的字符全都转为大写或者小写，再比较这两个字符对应的二进制数据。
根据你的使用场景，还会有更多对应的比较规则。

3.在Mysql中的运用

先来看一下Mysql支持的字符集有哪些。
file
其中的Default collation列表示这种字符集中一种默认的比较规则。返回结果中的最后一列Maxlen代表该种字符集表示一个字符最多需要几个字节。
再来看一下Mysql支持的比较规则。
file
…
file
可以看到支持的比较规则特别多，我们简单来看下utf8支持的比较规则
file
其中Default代表该字符集默认采用的比较规则，utf8为utf8_general_ci
这些比较规则的命名是有规律的：
1.比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的。
2.后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。
3.名称后缀意味着该比较规则是否区分语言中的重音、大小写之类的，具体可以用的值如下：

后缀	英文释义	描述
_ai	accent insensitive	不区分重音
_as	accent sensitive	区分重音
_ci	case insensitive	不区分大小写
_cs	case sensitive	区分大小写
_bin	binary	以二进制方式比较

字符集	Default Collation	Maxlen
ascii	ascii_general_ci	1
latin1	latin1_swedish_ci	1
gb2312	gb2312_chinese_ci	2
gbk	gbk_chinese_ci	2
utf8	utf8_general_ci	3
utf8mb4	utf8mb4_0900_ai_ci	4

上表展示了几种常见的字符集对应的比较规则和最大长度。这里特别注意下utf8和utf8mb4，为什么他们都是采用utf8编码但是Maxlen不同，而且看起来好像utf8mb4更像我们之前介绍的utf8呢？
我们上边说utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在MySQL中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以MySQL偷偷的定义了两个概念：
utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。
有一点需要大家十分的注意，在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用utf8mb4。

**看到这里，你应该明白了字符串乱码产生的根本原因了！本质就是编解码采用的规则不同，比如你用gbk去编码一个"张"，然后用utf8去解码"张"，当然得不到正确的结果了。**而在Mysql中客户端和服务端进行通信过程中也涉及到字符串的编解码，我们先来看一下这个简单的通信过程的模型:
客户端(发送字符串)-------->服务器(1.接受客户端字符串在内部进行处理，2.返回处理结果)------->客户端(接收处理结果)
在Mysql的实现中这个过程并不是只采用一种编码格式，可能涉及到多种编码格式的转换，因此很可能一不小心就产生乱码，这三个过程采用的编码格式是由三个系统变量来控制的。

系统变量	描述
character_set_client	服务器解码请求时使用的字符集
character_set_connection	服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results	服务器向客户端返回数据时使用的字符集

在这里插入图片描述
这里需要注意客户端发送请求所使用的字符集，一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：
类Unix系统使用的是utf8
Windows使用的是gbk
而我使用的是window操作系统，因此客户端发送的默认也是gbk。
如果你使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集。
了解这些之后，整个客户端和服务器请求的过程应该如下图。
file
从这个分析中我们可以得出这么几点需要注意的地方：
1.服务器认为客户端发送过来的请求是用character_set_client编码的。
2.假设你的客户端采用的字符集和 character_set_client 不一样的话，这就会出现意想不到的情况。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_client的值设置为ascii的话，服务器可能无法理解我们发送的请求，更别谈处理这个请求了。
3.服务器将把得到的结果集使用character_set_results编码后发送给客户端。
4.假设你的客户端采用的字符集和 character_set_results 不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏幕上出现乱码。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_results的值设置为ascii的话，可能会产生乱码。
5.character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection时使用，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。比如你把character_set_client设置为utf8，把character_set_connection设置成ascii，那么此时你如果从客户端发送一个汉字到服务器，那么服务器无法使用ascii字符集来编码这个汉字，就会向用户发出一个警告。
我们通常都把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句：
SET NAMES 字符集名;
这一条语句产生的效果和我们执行这3条的效果是一样的：
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;
另外，如果你想在启动客户端的时候就把character_set_client、character_set_connection、character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set的启动选项，比如在配置文件里可以这么写：
[client]
default-character-set=utf8
它起到的效果和执行一遍SET NAMES utf8是一样一样的，都会将那三个系统变量的值设置成utf8。

接下来我们看一个具体的例子，感受一下这三个系统变量带来的影响
file
这里我是window系统客户端应该采用GBK发送字符串，而character_set_client=utf8,但是查询的结果是正确的，没有乱码的产生！这是为什么？
file
同样的编码方式，这次就产生了乱码，这是为什么？
回忆一下之前的内容，GBK和utf8是兼容ASCII编码方式的，而英文单词和数字都可以用ASCII编码，因此BR01可以正常在GBK和utf8之间转换，而GBK对中文采用2个字节编码，utf8采用3个字节编码，因此在中文之间转换失败，从而产生了乱码现象。
file
如果你对Mysql中的这个问题感兴趣的话，可以继续阅读一些关于字符集比较的文章，比如Mysql4个级别的字符集和比较规则：服务器级别、数据库级别、表级别、列级别，这里只给出简单的总结。
这4个级别字符集和比较规则的联系如下：
如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则
由于字符集和比较规则是互相有联系的，如果我们只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：
只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表t中插入一条记录：

mysql> INSERT INTO t(col) VALUES('我我');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+--------+
| s      |
+--------+
| 我我   |
+--------+
1 row in set (0.00 sec)

首先列col使用的字符集是gbk，一个字符’我’在gbk中的编码为0xCED2，占用两个字节，两个字符的实际数据就占用4个字节。如果把该列的字符集修改为utf8的话，这两个字符就实际占用6个字节。
而相对应的客户端和服务端交互过程也可以更详细一点，如下
1.mysql Server收到请求时将请求数据从 character_set_client 转换为 character_set_connection
2.进行内部操作前将请求数据从 character_set_connection 转换为内部操作字符集,步骤如下
　　A. 使用每个数据字段的 CHARACTER SET 设定值；
　　B. 若上述值不存在，则使用对应数据表的字符集设定值
　　C. 若上述值不存在，则使用对应数据库的字符集设定值；
　　D. 若上述值不存在，则使用 character_set_server 设定值。
3.最后将操作结果从内部操作字符集转换为 character_set_results

4.Java中的字符集

如果问大家一个问题，Java中char类型占用几个字节？相信很多人都会说2个字节。暂且不论这个结果是否正确，那么你为什么得到这个答案的？
首先我们要知道Java内码中使用utf16编码规则，对应一个字符占用2个字节或4个字节。但是Java考虑到向前兼容，对于需要使用4个字节字符采用一对char来进行表示，因此在Java内码中一个char占用2个字节。
但Java外码中采用utf8编码，因此对应一个字符占用1~4个字节，即class文件或序列化传输时一个字符都是占用1到4个字节。
补充
**内码:**某种语言运行时，其char和string在内存中的编码方式。
**外码:**除了内码，皆是外码。

5.哈夫曼编码(Huffman Coding)

这里不讲解哈弗曼编码具体实现，具体实现请移步算法目录。
哈夫曼编码是一种编码方式，可变字长编码(VLC)的一种。Huffman于1952年提出一种编码方法，该方法完全依据字符出现概率来构造异字头的平均长度最短的码字，是一种基于熵的编码。它的一个显著的优点就是能够极大的压缩数据，而且是无损的。经过压缩后的数据，可以减少网络传输时占用的流量，以及存储时占用的空间。
但是哈夫曼编码存在一些固有的缺陷，严重阻碍了它的广泛应用，有兴趣的可以了解一下，同时也可以了解一下关于zip压缩采用的LZ77算法。