文章目录

1. utf8 与 utf8mb4

  • ​​utf8​​​字符集表示一个字符需要使用​​1~4​​​个字节，但是常用的一些字符使用​​1~3​​个字节就可以表示。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者定义了两个概念：
  1. ​​utf8mb3​​​ ：阉割过的​​utf8​​​字符集，只使用​​1~3​​个字节表示字符。
  2. ​​utf8mb4​​​ ：正宗的​​utf8​​​字符集，使用​​1~4​​个字节表示字符。
  • 在MySQL中​​utf8​​​是​​utf8mb3​​​的别名，所以之后在MySQL中提到​​utf8​​​就意味着使用​​1~3​​个字节来表示一个字符。
  • 如果有使用​​4​​​字节编码一个字符的情况，比如存储一些​​emoji​​​表情，那就使用​​utf8mb4​​。
  • 此外，通过如下指令可以查看MySQL支持的字符集：
show charset;
或
show character set;
  • 可以看到 字符集 对应的规则如下，utf8 占用 3 个字节，utf8mb4 占用 4 个字节

    • 【mysql】字符集与比较规则_java

2. 比较规则

  • 上表中，MySQL版本共支持41种字符集，其中的​​Default collation​​​ 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如​​utf8_polish_ci​​​表示以波兰语的规则此较，​​utf8_ spanish_ ci​​​是以西班牙语的规则比较，​​utf8_general_ci​​是一种通用的比较规则。
  • 后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

后缀

英文释义

描述

_ai

accent insensitive

不区分重音

_as

accent sensitive

区分重音

_ci

case insensitive

不区分大小写

_cs

case sensitive

区分大小写

_bin

binary

以二进制方式比较

  • 最后一列​​Maxlen​​，它代表该种字符集表示一个字符最多需要几个字节。
  • 这里把常见的字符集和对应的​​Maxlen​​显式如下：

字符集名称

Maxlen

ascii

1

latin1

1

gb2312

2

gbk

2

utf8

3

utf8mb4

4

  • 常用操作1：
  1. 查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';
  1. 查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';
  • 常用操作2：
  1. 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server'
  1. 查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database' ;
  1. 查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1 ;
  1. 修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_ general_ ci';
  • 说明1：

​​utf8_unicode_ci​​​和​​utf8_ general_ci​​​对中、英文来说没有实质的差别。
​​​utf8_general_ci​​​ 校对速度快，但准确度稍差。
​​​utf8_unicode_ci​​ 准确度高，但校对速度稍慢。

一般情况，用​​utf8_general_ci​​​就够了，但如果应用有德语、法语或者俄语，请一定使用​​utf8_unicode_ci​​。

  • 说明2：
    修改了数据库的默认字符集和比较规则后，原来已经创建的表格的字符集和比较规则并不会改变，如果需要修改，那只能单独修改。
  • 常用操作3：
  1. 查看表的字符集
show create table
  1. 查看表的比较规则
show table status from 数据库名 like '表名';
  1. 修改表的字符集和比较规则
ALTER TABLE 表名 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

3. 请求到响应过程中字符集的变化

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

系统变量

描述

character_set_client

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

character_set_connection

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

character_set_results

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

  • 这几个系统变量在计算机上的默认值如下(不同操作系统的默认值可能不同)：

    • 【mysql】字符集与比较规则_数据库_02

  • 为了体现出字符集在请求处理过程中的变化，这里特意修改一个系统变量的值:
mysql> set character_set_connection = gbk;
Query 0K,
0 rows affected (0.00 sec)
  • 现在假设客户端发送的请求是下边这个字符串:
SELECT * FROM t WHERE s = '我';
  • 为了方便理解这个过程，只分析字符 ‘​​我​​’ 在这个过程中字符集的转换。
  • 现在看一下在请求从发送到结果返回过程中字符集的变化：
  1. 客户端发送请求所使用的字符集
    一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：
  • 类Unix系统使用的是utf8
  • Windows使用的是gbk
  • 当客户端使用的是​​utf8​​​字符集，字符 ‘​​我​​​’ 在发送给服务器的请求中的字节形式就是:​​0xE68891​​

提示：
如果使用的是可视化工具，比如navicat之 类的，这些工具可能会使用自定义的字符集来编码发送到服
务器的字符串，而不采用操作系统默认的字符集(所以在学习的时候还是尽量用命令行窗口)。

  1. 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是
    ​​character_set_client​​ ，然后把这串字节转换为​​character_set_connection​​ 字符集编码的字符。由于我的计算机上​​character_set_client​​ 的值是​​utf8​​，首先会按照​​utf8​​字符集对字节串​​0xE68891​​进行解码，得到的字符串就是’​​我​​’，然后按照​​character_set_connection​​ 代表的字符集，也就是​​gbk​​进行编码，得到的结果就是字节串​​0xCED2​​。
  2. 因为表​​t​​的列​​col​​采用的是​​gbk​​字符集，与​​character_set_connection​​ 一致，所以直接到列中找字节值
    为​​0xCED2​​的记录，最后找到了一条记录。

提示：
如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行一次字符集转换。

  1. 上一步骤找到的记录中的​​col​​​列其实是一个字节串​​exCED2​​​，​​col​​​ 列是采用​​gbk​​​进行编码的，所以首先会将这个字节串使用​​gbk​​​进行解码，得到字符串‘​​我​​​’ ，然后再把这个字符串使用​​character_set_results​​​ 代表的字符集，也就是​​utf8​​​进行编码，得到了新的字节串:​​0xE68891​​ ，然后发送给客户端。
  2. 由于客户端是用的字符集是​​utf8​​​，所以可以顺利的将​​0xE68891​​解释成字符我，从而显示到我们的显示器上，所以我们读懂了返回的结果。
  • 演示图如下：

    • 【mysql】字符集与比较规则_java_03

  • 从这个分析中可以得出这么几点需要注意的地方：
  • 服务器认为客户端发送过来的请求是用​​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​​ 、​​character_set_connection​​、​​character_set_results​​ 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句：
SET NAMES = 字符集名;
  • 这一条语句产生的效果和下面执行这3条语句的效果是一样的：
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;
  • 比方说我的客户端使用的是​​utf8​​​字符集，所以需要把这几个系统变量的值都设置为​​utf8​​：
mysql> SET NAMES utf8;
  • 另外，如果想在启动客户端的时候就把​​character_set_client​​​、​​character_set_connection​​​ 、​​character_set_results​​​ 这三个系统变量的值设置成一样的，可以在启动客户端的时候指定一个叫
    ​​​default-character-set​​的启动选项，在配置文件中设置：
[client]
default-character-set=utf8
  • 上述语句使用的效果和执行一遍​​SET NAMES utf8​​​ 是一样的，都会将那三个系统变量的值设置成​​utf8​​。