【MySQL】字符集和比较规则

你的名字 2024-03-30 12:34 70阅读 0赞

#### 文章目录 ####

*  1. MySQL中的utf8和utf8mb4
 *  2. MySQL中的比较规则
 *  3. 各级别的字符集和比较规则
 *   *  3.1 服务器级别
     *  3.2 数据库级别
     *  3.3 表级别
     *  3.4 列级别
 *  4. 修改字符集或比较规则
 *  5. MySQL中的字符集转换过程

## 1. MySQL中的utf8和utf8mb4 ##

在MySQL中，我经常会看见**utf8和utf8mb4**这两种编码格式，但是这两种格式我并不了解，一般创建数据库的时候，如果让我选，我默认会选择utf8，但是这个和UTF-8长得最像。

但是实际上，这两种是有区别的。

*  **utf8**：utf8实际上是utf8mb3的别名，**它是阉割版的UTF-8字符集**，只使用1-3字节表示字符。
 *  **utf8mb4**：这个才是正宗的UTF-8字符集，使用1-4字节表示字符。

所以，如果想要在程序中存储emoji表情的话，是需要使用utf8mb4字符集的，因为emoji表情是需要使用4字节编码一个字符。

> **在MySQL8.0中，已经极大程度优化了utf8mb4字符集的性能，并将其设置为默认字符集**

--------------------

## 2. MySQL中的比较规则 ##

比较规则是指对于某种字符集来说，制定用来比较字符大小的多种规则，**一种字符集多种规则，一个默认规则**。

在MySQL中，可以通过以下命令查看比较规则

show collation [like 匹配的模式];

![image-20221227162640919][]

这些命名是有规律的。

*  **比较规则的名称与其关联的字符集的名称开头**，比如上述比较规则有些以utf8mb3开头
 *  **后面紧接着该比较规则所应用的语言**。比如utf8mb3\_roman\_ci表示罗马语的比较规则
 *  **名称的后缀意味这该比较规则是否区分语言中的重音、大小写等**。

<table> 
 <thead> 
  <tr> 
   <th>后缀</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>_ai</td> 
   <td>不区分重音</td> 
  </tr> 
  <tr> 
   <td>_as</td> 
   <td>区分重音</td> 
  </tr> 
  <tr> 
   <td>_ci</td> 
   <td>不区分大小写</td> 
  </tr> 
  <tr> 
   <td>_cs</td> 
   <td>区分大小写</td> 
  </tr> 
  <tr> 
   <td>_bin</td> 
   <td>以二进制方式进行比较</td> 
  </tr> 
 </tbody> 
</table>

--------------------

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

**在MySQL中有四个级别的字符集和比较规则。**

*  服务器级别
 *  数据库级别
 *  表级别
 *  列级别

--------------------

### 3.1 服务器级别 ###

MySQL提供了两种系统变量表示服务器级别的字符集和比较规则

<table> 
 <thead> 
  <tr> 
   <th>系统变量</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>character_set_server</td> 
   <td>服务器级别的字符集</td> 
  </tr> 
  <tr> 
   <td>collation_server</td> 
   <td>服务器级别的比较规则</td> 
  </tr> 
 </tbody> 
</table>

![image-20221227164036168][]

![image-20221227164103114][]

**可以通过SET语句来改变这两个系统变量。**

**也可以通过修改配置文件来修改**

![image-20221227164211029][]

--------------------

### 3.2 数据库级别 ###

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

![image-20221227164349625][]

如果想要查看当前数据库的字符集和比较规则，可以查看下面两个系统变量的值

<table> 
 <thead> 
  <tr> 
   <th>系统变量</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>character_set_database</td> 
   <td>当前数据库的字符集</td> 
  </tr> 
  <tr> 
   <td>collation_database</td> 
   <td>当前数据库的比较规则</td> 
  </tr> 
 </tbody> 
</table>

> 需要注意的是，这两个系统变量并不同与描述服务器级别的字符集和比较规则的那两个比较规则，这两个系统变量只是用来告诉用户当前数据库的字符集和比较规则是什么，**并不能通过修改两个变量的值来修改档期按数据库的字符集和比较规则。**

**如果在创建数据库的时候不指定字符集和比较规则，那么将会使用服务器级别的字符集和比较规则来作为数据库的字符集和比较规则。**

--------------------

### 3.3 表级别 ###

**可以在创建和修改表的时候指定表的字符集和比较规则**

![image-20221227165048985][]

**如果创建表的语句没有指定字符集和比较规则，则会使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。**

--------------------

### 3.4 列级别 ###

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

![image-20221227165247255][]

**对于某一列来说，如果创建和修改表的语句没有指定字符集和比较规则，则会使用该列所在的表的字符集和比较规则作为其字符集和比较规则。**

> 注意：**如果在修改列的字符集的时候，如果存储的数据不能使用修改后的字符集进行表示，则会报错**

--------------------

## 4. 修改字符集或比较规则 ##

字符集和比较规则之间是相互联系的，只修改其一需要遵循以下规则：

*  只修改字符集，则比较规则将变为修改后的字符集的默认比较规则。
 *  只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

--------------------

## 5. MySQL中的字符集转换过程 ##

在客户端**发送请求**的时候，一般来说，**客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一致**。

**但是如果MySQL客户端设置了default-character-set启动选项的时候，那么服务器就会忽视操作系统当前使用的字符集。**

而在服务器**接收请求**的时候，服务器接收到的请求实际上就是一个字节序列。服务器会将这个字节序列看作是使用系统变量character\_set\_client表示的字符集编码的字节序列。

注意的是，**这个系统变量character\_set\_client是session级别的**

**客户端发送请求的字符集和服务器接收请求的字符集是两个独立的字符集**，一般来说，客户端发送请求所用字符集应和服务器接收请求的字符集应保持一致，但是也会出现不一致的时候

如果服务器的character\_set\_client对应的字符集不能解释请求的字节序列，**那么服务器就会发出警告**。

在服务器**处理请求**的时候，会将服务器接收请求的character\_set\_client对应的字符集进行编码的字节序列转换成session级别的系统变量character\_set\_connection对应的字符集进行编码的字节序列。

对于character\_set\_connection我们可以通过SET命令单独修改此系统变量，除了character\_set\_connection，还有一个与其对应的系统变量**collation\_connection，这个系统变量表示了这些字符串应使用哪种比较规则。**

同样这个系统变量collation\_connection可以通过SET命令进行修改。

> **如果客户端发送请求、服务器接收请求和服务器处理请求这一过程中，字符集和比较规则不一样，会出现什么情况呢？**

就这个表来说

![image-20221227172650955][]

向客户端发送一条查询语句

![image-20221227172712963][]

由于当前操作系统使用的是gbk字符集，所以字符串‘我’是使用GBK字符集进行编码的，比较规则为gbk\_chinese\_ci，而列C使用的是utf8字符集编码的，比较规则为utf8\_general\_ci。

**那么在这种情况下，列的字符集和排序规则的优先级更高，也就是说这里需要将‘我’从GBK字符集转换成utf8字符集，然后使用列c的比较规则进行比较**。

> **当服务器生成响应的时候，是不是直接将列c也就是utf8字符集进行编码的结果发送回客户端呢？**

答案是不一定，**这取决与SESSION级别的系统变量character\_set\_results的值**，结果会从utf8字符集编码的转换成

character\_set\_results对应的字符编码后的字节序列

<table> 
 <thead> 
  <tr> 
   <th>系统变量</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>character_set_client</td> 
   <td>服务器认为请求是按照系统变量指定的字符集进行编码的</td> 
  </tr> 
  <tr> 
   <td>character_set_connection</td> 
   <td>服务器在处理请求时，会将请求字符序列从character_set_client转换成character_set_connection</td> 
  </tr> 
  <tr> 
   <td>character_set_results</td> 
   <td>服务器采用该系统变量指定的字符集对返回客户端的字符串进行编码</td> 
  </tr> 
 </tbody> 
</table>

**由于三个变量均为SESSION级别，因此每个客户端与服务器建立连接的时候都会维护这三个变量。**

![image-20221227173539204][]

最后就是**客户端接收响应了**，响应也就是一个字节序列，收到的字节序列会直接写进黑框框中，再由黑框框将字节序列接受成人类能懂的字符（如果没有特殊设置，一般使用操作系统当前使用的字符集进行解释）

--------------------

> 参考：《MySQL是怎样运行的：从根儿上理解 MySQL》

--------------------

[image-20221227162640919]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c3822ddf59db4a2abd9c642d9201b0d8.png
[image-20221227164036168]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/2ec8f1cb80264d738027c1cbd3d12d12.png
[image-20221227164103114]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/749985abe25a44b08037dc7b290561f2.png
[image-20221227164211029]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/4d3c54bec88e4d10b6fc927dcdc303dc.png
[image-20221227164349625]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/42a51bc04c32417c8ea75548552c77cc.png
[image-20221227165048985]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/7348ecad3447485c8c2ea03936179339.png
[image-20221227165247255]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d2e2ddd034e3485e9cedc17ebc5346b4.png
[image-20221227172650955]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/a5ffd7e7bfd245b7976f5aeb903f0016.png
[image-20221227172712963]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d0faaa18c15d43f296e8bb83ed9e7c77.png
[image-20221227173539204]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/cd082b8f66a8470b948fafde514812da.png