第三章 乱码的前世今生-字符集和比较规则

字符集简介

需要解决两个问题：

• 界定清楚字符范围，需要哪些字符

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

比较规则简介

同一个字符集可以有多种比较规则

比如说二进制规则就是两个字符对应的二进制编码进行比较

一些重要的字符集

ASCII字符集：

收录128个字符，包括空格，标点字符，数字，大小写字母和一些不可见字符

GB2312字符集：

兼容ASCII字符集，以及收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。

• 如果字符在ASCII字符集中，采用1字节编码

• 否则采用2字节编码

如果字节在0-127之内就是单独的字符，否则就是两个字节代表一个单独的字符

GBK字符集：

兼容GB2312，作了相关的补充

utf8字符集： 兼容ASCII字符集，并且有地球上所有的字符

编码一个字符需要使用1-4字节

MySQL中支持的字符集和排序规则

因为用最大字节长度在某些方面会影响系统的存储和性能，所以设计MySQL的大佬偷偷定义了两个概念：

utf8mb3:只使用1-3表示字符，阉割了哪些需要4个字节表示的字符，在MySQL中utf8表示utf8mb3的别名

utf8mb4：使用1-4个字符

字符集的查看

MySQL支持许多种字符集，查看MySQL中支持的字符集可以用下面这个语句：

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

其中CHARACTER SET和CHARSET是同义词

其中Default collation列表示这种字符集中一种默认的比较规则

Maxlen列就是说一个字符最多需要几个字节

比较规则的查看

语法格式：

show collation like 匹配的模式

在我的终端是搜不到的

所以我们就直接来理解以下这个就行了

mysql> SHOW COLLATION LIKE 'utf8\_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 |
| utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 |
| utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 |
| utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 |
| utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 |
| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.00 sec)

• 比较规则是以与其关联的字符集的名称开头的，上面的查询结果的比较规则名称都是以utf8名称开头的

• 后面紧跟的是比较规则主要作用于哪种语言，如utf8_polish_ci是波兰语的规则比较，utf8_general_ci是一种通用的比较规则

• 名称后缀意味着比较规则是否区分语言中的重音，大小写等，具体可以用的值如下：

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

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

字符集和比较规则的应用

各级别的字符集和比较规则

Mysql有四个级别的字符集和比较规则

• 服务器级别

• 数据库级别

• 表级别

• 列级别

服务器级别

我们来看这两个系统变量分别叫什么：

character_set_server服务器级别的字符集

collation_server服务器级别的比较规则

我们可以在配置文件中修改这两个变量

数据库级别

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法：

//创建
create database 数据库名
character set 字符集名称
collate 比较规则名称；
//修改
alter database 数据库名
character set 字符集名称
collate 比较规则名称

实验记录

如果想查看当前数据库使用的字符集和比较规则（首先要使用use语法选择当前默认的数据库）

character_set_database 当前数据库的字符集

collation_database当前数据库的比较规则

collation是整理的意思

我们创建的时候如果没有指定字符集和比较规则，就直接使用系统默认的

create database 数据库名;

当然我们还是可以通过alter来修改的

表级别

在创建和修改表 的时候指定表的字符集

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

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

如果我们创建和修改表的语句中没有指明字符集和比较规则，将使用所在数据库的字符集和比较规则作为该表的字符集和比较规则

下面是改变这个表的示例：

列级别

同一个表中不同的列可以有不同的字符集和比较规则

格式：

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

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

这个是alter的一个实例

下面是将列改成和表一样的实例

注意：如果转换前后列的存储的数据转换不成功就会报错

比方说utf8 →ascii 可能会报错，因为asciil不能表示汉字字符

仅修改字符集或仅修改比较规则

因为字符集和比较规则是互相联系的，如果我们修改了一方，那么另一方也会跟着变化。

具体规则如下：

• 只修改字符集，比较规则会变成字符集默认的比较规则

• 只修改比较规则，字符集变成比较规则默认的字符集

无论哪种级别的，这两条规则都适用

我们修改当前服务器的系统变量

各级别字符集和比较规则小结

如果没有显示的指定字符集和比较规则，都会是上层默认的字符集和比较规则

根据我们定义的字符集和比较规则，插入一段数据

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

编码和解码使用的字符集不一致的后果

字符串 = 字节串

比如我在utf8中字节串是：0xE68891,假设使用gbk字符集去解码：

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

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

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

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

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

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

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

7. 所以整串字节0xE68891被latin1字符集解释后的字符串就是'我'

注意：每个字符集所拥有的最大字节数是不一样的

字符集转换的概念

如果接收0xE68891这个字节串的程序按照utf8字符集进行解码，然后又按照gbk进行编码，他们当然还是一个同样的字符，我

最后编码后的字节串是0xCED2,我们把这个过程称之为字符集的转换，也就是字符串我从utf8字符集转换为gbk字符集

MySQL中字符集的转换

这个转换过程分别是：

从客户端发往服务器的请求本质是一个字符串

服务器向客户端返回的也是一个字符串

但是字符串其实是使用某种字符集编码的二进制数据

从这个过程中会用到3个系统变量

从发送请求到返回结果这个过程中伴随着多次字符集的转换

character_set_clien:服务器解码请求时使用的字符集

character_set_connection:服务器处理请求时会把请求字符串从character_set_client转为character_set_connection

character_set_results:服务器向客户端返回数据时使用的字符集

我们来查看一下默认值：

这个是我的默认值，可能数据库版本不一样，还有就是我是windows，太穷了没办法

我们来理解以下一个字符串：

select * from t where s = "我"

我们看一下请求从发送到返回过程中字符集的变化：

1.客户端发送请求所使用的字符集

一般情况下客户端使用的字符集和当前操作系统一致

这个字符会被character_set_client所编码，变成一个字节A，假设字节为A，我们假设这个字符串是我

2.服务器收到字符串发送的请求就一串二进制的字节，他会认为这串字符所采用的字符集是character_set_client,然后把这串字节转换为character_set_connection字符集编码的字符。

首先，我的计算机上的character_set_client是gbk，会按照gbk字符集对字节串A进行解码，得到字符串我，然后按照character_set_connnection代表的字符集utf8进行编码，得到的结果就是字节串B

3.因为表t的列col采用的是gbk字符集，与character_set_connection是一致的，所以直接到列中找字节值为B的记录，最后找到一条记录，我们的记录是直接以字节存储的

如果列使用的字符集和charactersetconnection不一致，需要进行解码，然后对解码的这个字符进行编码，也就是字符集转换

这张图可以解释上述的一切

如果说客户端采用的字符集和character_set_client或者是返回的character_set_result不一样的情况的话，第一个就是我的系统变量识别不了客户端发送过来的东西

第二就是客户端识别不了服务器发送过来的东西

比如说客户端是utf8，但是服务器是ascii，这个时候客户端会按照utf8的格式去解码服务器中的数据，虽然说utf8包含了ascii中的内容，但是可惜的是他们解码的方式是不一样的，对同样一个字符，因为utf8用1-4个字节表示一个字符，但是ascii使用1个字节表示一个字符

接着讲一下character_set_connection只是将character_set_client转换为character_set_connection时使用，一定要注意的是该字符集

包含的字符范围一定覆盖请求中的字符

因为转化的过程十分头晕，所以我们通常把我们可以控制的三个变量character_set_clientcharacter_set_connectioncharacter_set_result转换成和客户端使用的字符集一致的情况，这样减少了很多无畏的字符集转换

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

SET NAMES 字符集名;Copy to clipboardErrorCopied

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

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

如果说我们想将客户端设置为utf8字符集

我们应该

如果想将三个变量都设置成一个，可以在配置文件中

将前面的#去掉就行

比较规则的应用

我们插入一些数据，就此来做一下实验

此时我们的比较规则是gbk_chinese_ci中，这时候是不区分大小写的，接着我们将col的比较规则改成gbk_bin

所以我们以后的排序结果和我们想的不一样可以思考需要如何修改比较规则