SHIHAO ZHAO

MySQL字符集(Charset)

2020/07/01 MySQL

在计算机中，只能存储二进制数据，也就是0和1。那字符串改如何存储呢？我们就需要建立二进制数据和字符的映射关系。在建立映射关系的时候，我们要想清楚两件事：第一件事是哪些字符需要被映射成二进制数据，也就是界定清楚字符范围。第二件事是如何映射，将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码。

1. 字符集和比较规则简介

人们抽象出一个字符集的概念来描述某个字符范围的编码规则。比如，我定义一个属于我自己的字符集，叫作宇宙第一字符集，那么它包含的字符范围和编码规则如下：

  • 包含字符：'a'，'b'，'c'

  • 编码规则如下：

    采用1个字节编码一个字符的形式，字符和字节的映射关系如下：

    'a' -> 00000001 (十六进制：0x01)
'b' -> 00000010 (十六进制：0x02)
'c' -> 00000011 (十六进制：0x03)

通过宇宙第一字符集我们就可以用二进制形式表示一些字符串了。

'ac' -> 0000001000000011  (十六进制：0x0203)
'df' -> 无法表示，字符集不包含'd'和'f'

1.1 比较规则简介

在我们确定了宇宙第一字符集字符集表示字符的范围以及编码规则后，怎么比较两个字符的大小呢？最容易想到的就是直接比较这两个字符对应的二进制编码的大小，比方说字符'a'的编码为0x01，字符'b'的编码为0x02，所以'a'小于'b'，这种简单的比较规则也可以被称为二进制比较规则，英文名为binary collation。

但在一些特殊情况下，比如不区分大小写的情况，我们就不能这么简单地比较了，比如'a'和'A'是相等的。在这种情况下，我们可以把大写的'A'转为小写的'a'，再比较它们的二进制数据。

实际生活中的字符不止英文字符一种，比如我们的汉字有几万之多，对于某一种字符集来说，比较两个字符大小的规则可以制定出很多种，也就是说同一种字符集可以有多种比较规则。

1.2 常用字符集

现有的字符集有很多种，常用的有如下几种：

  • 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个字节进行编码，编码后的十六进制表示为0xB0AE，'u'需要用1个字节进行编码，编码后的十六进制表示为0x75，所以拼合起来就是0xB0AE75。

  • GBK字符集

    GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。

  • utf8字符集

    收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，比方说这样：

    'L' ->  01001100（十六进制：0x4C）
'啊' ->  111001011001010110001010（十六进制：0xE5958A）

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

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


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的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用utf8mb4。

2.2 MySQL比较规则的查看

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

SHOW COLLATION [LIKE 匹配的模式];

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


3. 字符集和比较规则的应用

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

MySQL有4个级别的字符集和比较规则，分别是：

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

3.1.1 服务器级别

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

系统变量 描述
character_set_server 服务器级别的字符集
collation_server 服务器级别的比较规则

我们可以使用以下语句查看使用的字符集和比较规则：

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

3.1.2 数据库级别

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

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

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

3.1.3 表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

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

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

3.1.4 列级别

需要注意的是，对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

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

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

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

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

由于字符集和比较规则是互相有联系的，如果我们只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：

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

不论哪个级别的字符集和比较规则，这两条规则都适用。

我们介绍的这4个级别字符集和比较规则的联系如下：

  • 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
  • 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
  • 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则


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

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

字符串在计算机上的体现就是一个字节串，如果你使用不同字符集去解码这个字节串，可能不会得到你想要的结果。

我们知道字符'我'在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字符集解释后的字符串就是'我'

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

4.2 字符集转换的概念

如果接收0xE68891这个字节串的程序按照utf8字符集进行解码，然后又把它按照gbk字符集进行编码，最后编码后的字节串就是0xCED2，我们把这个过程称为字符集的转换，也就是字符串'我'从utf8字符集转换为gbk字符集。

4.3 MySQL中字符集的转换

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


参考文章：

MySQL 是怎样运行的：从根儿上理解 MySQL

文档信息

Search

    Table of Contents