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

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

1.1 字符集简介

我们知道，计算机中实际存储的是二进制数据，那它是怎么存储字符串呢？当然是建立字符与二进制数据的映射关系了。要建立这个关系，最起码要搞清楚下面这两件事儿。

• 要把哪些字符映射成二进制数据？也就是界定字符范围。
• 怎么映射？将字符映射成二进制数据的过程叫作编码，将二进制数据映射到字符的过程叫作解码。

人们抽象出一个字符集的概念来描述某个字符范围的编码规则。比如，我们自定义一个名称为xiaohaizi4919的字符集，它包含的字符范围和编码规则如下。

• 包含字符 ‘a’、‘b’、‘A’、‘B’。

• 编码规则为一个字节编码一个字符的形式。字符和字节的映射关系如下

  'a'-> 00000001(十六进制0x01)
  'b'-> 00000010(十六进制0x02)
  'A'-> 00000011(十六进制0x03)
  'B'-> 00000100(十六进制0x04)
  

xiaohaizi4919字符集在现实生活中并没有，它是我自定义的字符集！是我自定义的字符集！是我自定义的字符集！（重要的事情讲三遍）

有了xiaohaizi4919字符集，我们就可以用二进制形式表示一些字符串了。下面是一些字符串用xiaohaizi4919字符集编码后的二进制表示：

• ‘bA’ -> 0000001000000011（十六进制 0x0203）；
• ‘baB’ -> 000000100000000100000100（十六进制 0x020104）；
• ‘cd’ 无法表示，因为字符集 xiaohaizi4919 不包含 ‘c’ 和 ‘d’。

1.2 比较规则简介

在确定了xiaohaizi4919 字符集表示的字符范围以及编码规则后，该怎么比较两个字符的大小呢？最容易想到的就是直接比较这两个字符对应的二进制编码的大小。比如字符a 的编码为0x01，字符 b 的编码为0x02，所以a 小于b。这种简单的比较规则也可以称为二进制比较规则。

二进制比较规则尽管很简单，但有时候并不符合现实需求。比如，在很多场合下，英文字符都是不区分大小写的，也就是说 ‘a’ 和 ‘A’ 是相等的。此时就不能简单粗暴地使用二进制比较规则了，这时可以这样指定比较规则：

• 将两个大小写不同的字符全都转为大写或者小写:
• 再比较这两个字符对应的二进制数据。

这是一种稍微复杂一点儿的比较规则，但是实际生活中的字符不止英文字符这一种，还有中文字符、德文字符、法文字符等。对于某一种字符集来说，可以制定用来比较字符大小的多种规则，也就是说同一种字符集可以有多种比较规则。稍后将介绍现实生活中使用的各种字符集以及它们的一些比较规则。

1.3 一些重要的比较规则

不同字符集表示的字符范围和用到的编码规则可能不一样。

常用字符集：

• ASCII 字符集： 共收录 128 个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于ASCII字符集总共才 128 个字符，所以可以使用一个字节来进行编码：

  'L' -> 01001100（十六进制0x4C，十进制76）
  'M' -> 01001101（十六进制0x4D，十进制77）
  

• ISO 8859-1 字符集： 共收录256 个字符，它在ASCII字符集的基础上又扩充了128个西欧常用字符（包括德法两国的字母）。

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

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

• UTF-8 字符集： 几乎收录了当今世界各个国家/地区使用的字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用长编码的方式，编码一个字符需要 1 ~ 4 字节：

  'L' -> 01001100（1字节，十六进制 0x4C）
  '啊' -> 111001011001010110001010（3字节，十六进制 0xE5958A）
  

其实准确地说，UTF-8 只是 Unicode 字符集的一种编码方案，Unicode 字符集可以采用 UTF-8、UTF-16、UTF-32 这几种编码方案。UTF-8 使用1~4字节编码一个字符 UTF-16 使用2或4字节编码一个字符，UTF-32 使用4字节编码一个字符。更详细的Unicode 及其编码方案的知识，大家可以自行查阅

这种使用不同字节数来表示一个字符的编码方式称为变长编码方式。比如字符串“爱u”其中的 ‘爱’ 需要用2字节进行编码，编码后的十六进制表示为0xBOAE；‘u’ 需要用1字节进行编码，编码后的十六进制表示为0x75，所以拼合起来就是 0xBOAE75。

计算机在读取一个字节序列时，怎么区分某个字节代表的是一个单独的字符还是某个字符的一部分呢？别忘了 ASCII 字符集只收录128个字符，使用 0~127 就可以表示全部字符。所以，如果某个字节是在0~127之内（该字节的最高位为0），就意味着一个字节代表一个单独的字符，否则（该字节的最高位为1）就是两个字节，代表一个单独的字符

MySQL 并不区分字符集和编码方案的概念，所以后面唠叨的时候会把 UTF-8、UTF-16、UTF-32 都当作一种字符集对待。

对于同一个字符，不同字符集可能采用不同的编码方式。比如对于汉字 ‘我’ 来说，ASCII字符集中根本没有收录这个字符，UTF-8 和 GB2312 字符集对汉字我的编码方式如下。

• UTF-8 编码：111001101000100010010001（3 字节，十六进制形式为 0xE68891）。
• GB2312 编码：1100111011010010（2字节，十六进制形式为 0xCED2）。

2. MySQL 中支持的字符集和比较规则

2.1 MySQL 的 utf8 和 utf8mb4

前文讲到，UTF-8 字符集在表示一个字符时需要使用1~4字节，但是我们常用的一些字符使用1~3字节就可以表示了。而在 MySOL 中，字符集表示一个字符所用的最大字节长度在某些方面会影响系统的存储和性能。设计 MySQL 的大叔“偷偷”地定义了下面两个概念。

• utf8mb3：“阉割”过的 UTF-8 字符集，只使用 1~3 字节表示字符。

• utf8mb4：正宗的 UTF-8 字符集，使用 1~4 字节表示字符。

有一点需要注意：在 MySOL 中，utf8 是 utf8mb3 的别名，所以后文在 MySQL 中提到 utf8 时就意味着使用1~3字节来表示一个字符。如果大家有使用4字节编码一个字符的情况，比如存储一些 emoji 表情，请使用utf8mb4。

在 MySOL8.0 中，设计 MySOL 的大叔已经很大程度地优化了 utf8mb4 字符集的性能，而且已经将其设置为默认的字符集。

2.2 字符集查看

MySQL 支持非常多的字符集，可以用下述指令查看当前 MySQL 中支持的字符集：

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

其中，CHARACTER SET 和 CHARSET 是同义词，用任意一个都可以。在后文中用到 CHARACTER SET 的地方都可以用 CHARSET 替换。

mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.04 sec)

MySQL 中表示字符集的名称时使用小写模式

我使用的这个 MySQL 版本一共支持 41 种字符集，其中 Default collation 列表示这种字符集种一种默认的比较规则。大家注意返回结果中的最后一列 Maxlen，它代表这种字符集最多需要几个字节来表示一个字符。为了让大家的印象更深刻，我把几个常用字符集的 Maxlen 列摘抄下来（见表 3-1），大家务必记住。

2.3 比较规则查看

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

ShOW COLLATION [LIKE 匹配的模式];

前面说了，一种字符集可能对应这若干种比较规则。MySQL 支持的字符集已经非常多，所以支持的比较规则就更多了，我们先看一下 utf8 字符集下的匹配规则：

这些比较规则的命名挺有规律的：

• 比较规则的名称以与其关联的字符集的名称开头。比如在上面的查询结果中，比较规则的名称都是以utf8 开头的。
• 后面紧跟着该比较规则所应用的语言。比如，utf8_polish_ci 表示波兰语的比较规则：utf8_spanish_ci 表示班牙语的比较规则；utf8_general_ci 是一种通用的比较规则。
• 名称后缀意味着该比较规则是否区分语言中的重音、大小写等，具体可用的值如表 3-2所示。

比如比较规则 utf8_general_ci 是以ci 结尾的，说明不区分大小写。

每种字符集对应若干种比较规则，且每种字符集都有一种默认的比较规则。在执行 SHOW COLLATION 语句后返回的结果中，Default 列的值为 YES 的比较规则，就是该字符集的默认比较规则，比如 utf8 字符集默认的比较规则就是 utf8_general_ci。

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

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

MySQL 有 4 个级别的字符集，分别是服务器级别、数据库级别、表级别、列级别。

1. 服务器级别

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

我们看一下这两个系统变量的值：

可以看到，MySQL 服务器级别默认字符集是 utf8mb4，默认的比较规则是 utf8mb4_0900_ai_ci。

在启动服务器程序时，可以通过启动选项或者在服务器程序运行过程种使用 SET 语句来修改这两个变量的值。比如，我们可以在配置文件种这么写：

[server]
character_set_server=gb2312
collation_server=gb2312_chinese_ci

当服务器在启动时读取这个配置文件后，这两个系统变量的值便修改了。

2. 数据库级别

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

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

其中 DEAFULT 可以省略，并不影响语句的语义。

如果想查看当前数据库使用的字符集和比较规则，可以查看下表中的两个系统变量的值（前提时使用 USE 语句选择当前的默认数据库。如果没有默认数据库，则变量与服务器级别下相应的系统变量具有相同的值。

注意：

数据库的字符集和比较规则就是我们在创建数据库语句时指定的。需要注意的一点是，charset_set_database 和 collation_database 这两个系统变量只是用来告诉用户当前数据库的字符集和比较规则是什么。我们不能通过修改这两个变量的值来改变当前数据库的字符集和比较规则。

在数据库的创建语句中也可以不指定字符集和比较规则，比如：

create database 数据库名;

这将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

3. 表级别

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

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

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

mysql> USE charset_demo_db;
Database changed

mysql> CREATE TABLE (
	-> 		COL VARCHAR(10)
	-> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.03 sec)

CREATE TABLE (
	COL VARCHAR(10)
);

4. 列级别

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

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

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

eg：

mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

在修改列的字符集时需要注意：如果列中存储的数据不能用修改后的字符集进行表示，则会发生错误。

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

由于字符集和比较规则之间相互关联，因此如果只修改字符集，比较规则也会跟着变化；如果只修改比较规则，字符集也会跟着变化。具体规则如下:

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

无论哪个级别的字符集和比较规则，这两条规则都适用。我们以服务器级别的字符集和比较规则为例来看一下详细过程。

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

  

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

  

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

前文介绍的这4个级别的字符集和比较规则的联系如下:

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

知道了这些规则后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定每个列存储的实际数据所占用的存储空间大小。

3.2 客户端和服务器通信过程中使用的字符集

1. 编码和解码使用的字符集不一致

说到底，字符串在计算机上的体现就是一个字节序列。如果使用不同的字符集去解码这个字节序列，最后得到的结果可能让你挠头。

我们知道，字符串 ‘我’ 在 UTF-8 字符集编码下的字节序列是 0xE68891。如果程序A把这个字节序列发送到程序 B，程序 B 使用不同的字符集解码这个字节序列（假设使用的是 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 字符集解释后的字符串就是 “æ^'” 。

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

2.字符集转换的概念

如果接收 0xE68891 这个字节序列的程序按照 UTF-8 字符集进行解码，然后又把它按照 GBK 字符集进行编码，则编码后的字节序列就是 0xCED2。我们把这个过程称为字符集的转换，也就是字符串 ‘我’ 从 UTF-8 字符集转换为 GBK 字符集。

3. MySQL中的字符集转换过程

如果我们仅仅把 MySQL 当作一个软件，那么从用户的角度来看，客户端发送的请求以及服务器返回的响应都是一个字符串。但是从机器的角度来看，客户端发送的请求和服务器返回的响应本质上就是一个字节序列。在这个 “客户端发送请求，服务器返回响应” 的过程中，其实经历了多次的字符集转换。下面详细分析一下。

客户端发送请求

MySQL 客户端发送给服务器的请求以及服务器返回给客户端的响应，其实都遵从了一定的格式（这个 “格式” 指明了请求和响应的每一个字节分别代表什么意思）。我们把 MySQL 客户端与服务器进行通信的过程中事先规定好的数据格式称为 MySQL 通信协议。由于 MySQL 本身是开源软件，因此可以直接分析代码来了解这个协议。即使不想查看源码，也可以简单地使用诸如 Wireshark 等抓包软件来分析这个协议。在了解了 MySQL 通信协议之后，我们甚至可以动手制作自己的客户端软件。

由于市面上的 MySQL 客户端软件种类繁多，我们只以 MySQL 安装目录的 bin目录下自带的 mysql 客户端程序为例进行分析。一般情况下，客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一致。可以使用下述方法获取操作系统当前使用的字符集。

• 当使用类UNIX操作系统时

  LC_ALL、LC_CTYPE、LANG 这3个环境变量的值决定了操作系统当前使用的是哪种字符集。其中，LC_ALL 的优先级比 LC_CTYPE 高，LC_CTYPE 的优先级比 LANG 高。也就是说，如果设置了 LC_ALL，则无论是否设置了 LC_CTYPE 或者 LANG，最终都以 LC_ALL 为准；如果既没有设置 LC_ALL，就以 LC_ CTYPE 为准；如果既没有设置 LC_ALL 也没有设置 LC_CTYPE，就以LANG 为准。

  下面看一下这3个变量的值在我的 macOS 操作系统上分别是什么:

  shell> echo $LC_ALL
  zhCN.UTF-8
  shell> echo $LC_CTYPE
  shell> echo $LANG
  
  腾讯云服务器
  [root@VM-4-8-centos ~]# echo $LC_ALL
  [root@VM-4-8-centos ~]# echo $LC_CTYPE
  [root@VM-4-8-centos ~]# echo $LANG
  en_US.utf8
  

  很显然，只设置了LC_ALL 的值：zhCN.UTF-8 （其中的 CN 表示语言以及国家地区的代码，大家可以忽略）。这就意味着我的 macOS 操作系统当前使用的字符集是 UTF-8。如果这3 个环境变量都没有设置，那么操作系统当前使用的字符集就是其默认的字符集。

  获取类 UNIX 操作系统当前使用的字符集时，调用的是系统函数 nl_langinfo(CODESEI)，该函数会分析上述3个系统变量的值。对源码感兴趣的读者可以进一步研究。

• 当使用Windows 操作系统时

  在Windows中，字符集称为代码页（code page），一个代码页与一个唯一的数字相关联。

  比如：936 代表 GBK 字符集，65001 代表 UTF-8 字符集。我们可以在 Windows 命令行窗口的标题栏上单击鼠标右键，在弹出的菜单中单击 “属性” 子菜单，从弹出的对话框中选择 “选项” 选项卡。

  

  可以看到，当前代码页的值是 936，也就表示当前的命令行窗口使用的是 GBK 字符集。

  更简单的方法则是直接运行 chcp 命令，查看当前代码页是什么。

  

  在 Windows 中获取当前代码页时，调用的系统函数为 GetConsoleCP 。对源码感兴趣的读者可以进一步研究。

  在 Windows 操作系统中，如果在启动 MySQL 客户端程序时携带了 default-character-set 启动选项，那么 MySQL 客户端将以该启动选项指定的字符集对请求的字符串进行编码（这一点并不适用于类UNIX 操作系统）。
  比如，我们在 Windows 的命令行窗口中使用如下命令启动客户端（省略了用户名、密码等其他启动选项）：

  mysql -uxxxx -pxxxxx --default-character-set=utf8
  

  那么客户端将会以 UTF-8 字符集对请求的字符串进行编码。

服务器接收请求

从本质上来说，服务器接收到的请求就是一个字节序列。服务器将这个字节序列看作是使用系统变量character_set_client 代表的字符集进行编码的字节序列（每个客户端与服务器建立连接后，服务器都会为该客户端维护一个单独的 character_set_client 变量，这个变量是 SESSION 级别的）。

大家在这里应该意识到一件事儿：客户端在编码请求字符串时实际使用的字符集，与服务器在收到一个字节序列后认为该字节序列所采用的编码字符集，是两个独立的字符集。一般情况下，我们应该尽量保证这两个字符集是一致的。就像我跟你说的是中文，你也要把听到的话当成中文来理解，如果你要把它当成英文来理解，那就把人整迷糊了。

当然，我们并不限制你非要把中文当成英文来理解的权利，就像在 MySQL 中可以通过 SET 命令来修改character_set_client 的值一样。假如客户端实际使用 UTF-8 字符集来编码请求的字符串，我们还是可以通过下面的命令将 character_set_client 设置为 latin1 字符集：

SET character_set_client=latin1;

这样一来，就发生了 “鸡同鸭讲” 的事情。比如，客户端实际发送的是一个汉字字符 ‘我’
（UTF-8 的编码为 0xE68891），但服务器却将其理解为3 个字符：‘æ’、‘^’ 和 ‘'’。

另外还需要注意的是，如果 character_set_client 对应的字符集不能解释请求的字节序列，那么服务器就会发出警告。比如，客户端实际使用 UTF-8 字符集来编码请求的字符串，我们现在把 character_set_client 设置成 asii 字符集，而请求字符串中包含了一个汉字 ‘我’ （对应的字节序列就是0xE68891），那么将会发生这样的事情:

从上面的输出结果中可以看到，0xE68891 并不是正确的 ascii 字符。

服务器处理请求

我们知道，服务器会将请求的字节序列当作采用 character_set_client 对应的字符集进行编码的字节序列，不过在真正处理请求时又会将其转换为使用 SESSION 级别的系统变量 character_set_connection 对应的字符集进行编码的字节序列。

我们也可以通过 SET 命令单独修改 character_set_connection 系统变量。比如，客户端发送给服务器的请求中包含字节序列 0xE68891，然后服务器针对该客户端的系统变量 character_set_client 为 utf8，此时服务器就知道该字节序列其实是代表汉字 ‘我’。如果服务器针对该客户端的系统变量 character_set_connection 为 gbk，那么还要在计算机内部将该字符转换为采用 gbk 字符集编码的形式，也就是 0xCED2。

有的同学可能认为这一步骤多此一举了，但是请考虑下面这个查询语句：

mysql> SELECT 'a' = 'A';

这个查询语句的返回结果是 TRUE 还是 FALSE？其实仅根据这个语句是不能确定结果的。这是因为我们并不知道这两个字符串到底采用了什么字符集进行编码，也不知道这里使用的比较规则是什么。

此时，character_set_connection 系统变量就发挥了作用，它表示这些字符串应该使用哪种字符集进行编码。当然，还有一个与之配套的系统变量 collation_connection，这个系统变量表示这些字符串应该使用哪种比较规则。现在通过 SET 命令将 character_set_connection 和 collation_connection 系统变量的值分别设置为 gbk 和 gbk_chinese_ci，然后再比较 ‘a’ 和 ‘A’：

可以看到在这种情况下这两个字符串是相等的。

现在通过 SET 命令修改 character_set_connection 和 collation_connection 的值，将它们分别设置为 gbk 和 gbk_bin，然后比较 ‘a’ 和 ‘A’：

mysql> SET character_set_connection = gbk;
Query OK , 0 rows affected (0.00 sec)

mysql> SET collation_connection = gbk_bin;
Query QK , 0 rows affected (0.00 sec)

mysql> SELECT 'a'= 'A';
+-----------+
| 'a'='A'
+-----------+
| 0
+-----------+
1 row in set (0.00 sec)

可以看到，在这种情况下这两个字符串就不相等了。

我们接下来考虑请求中的字符串和某个列进行比较的情况。比如我们有一个表 t:

CREATE TABLE tt (
	C VARCHAR(100)
) ENGINE=INNODB CHARSET=utf8;

很显然，列 c 采用的字符集和表级别字符集 utf8 一致。这里采用默认的比较规则 utf8_general_ci。表 tt 中有一条记录：

mysql> SELECT * FROM tt;
+------+
| c
+------+
| 我
+------+
l row in set (0.00 sec)

假设现在 character_set_connection 和 collation_connection 的值分别设置为 gbk 和 gbk_chinese 然后我们有下面这样一条查询语句：

SELECT * FROM tt WHERE C = '我';

在执行这个语句前，面临一个很重要的问题：字符串 ‘我’ 是使用 gbk 字符集进行编码的比较规则是 gbk_chinese ci；而列 c 是采用 utf8 字符集进行编码的，比较规则为 utf8_general_ci。这该怎么比较呢?设计 MySOL 的大叔规定，在这种情况下，列的字符集和排序规则的优先级更高。因此，这里需要将请求中的字符串我先从 gbk 字符集转换为 utf8 字符集，然后再使用列 c 的比较规则 utf8_general_ci 进行比较。

服务器生成响应

还是以前面创建的表 tt 为例。列 c 是使用 utf8 字符集进行编码的，所以字符串 ‘我’ 在列 c 中的存放格式就是 0xE68891。当执行下面这个语句时：

SELECT * FROM tt;

是不是直接将 0xE68891 读出后发送到客户端呢？这可不一定，这取决于 SESSION 级别的系统变量 character_set_results 的值。服务器会先将字符串 ‘我’ 从 utf8 字符集编码的 0xE68891 转换为 character_set results 系统变量对应的字符集编码后的字节序列，之后再发送给客户端。

如果有特殊需要，也可以使用 SET 命令来修改 character_set_results 的值。比如我们执行下述语句：

SET character set results = gbk;

那么，如果再次执行 SELECT * FROM tt 语句，在服务器返回给客户端的响应中，字符串 ‘我’ 对应的就是字节序列 0XCED2。

每个 MySQL 客户端都维护着一个客户端默认字符集，客户端在启动时会自动检测所在操作系统当前使用的字符集，并按照一定的规则映射成 MySQL 支持的字符集，然后将该字符集作为客户端默认的字符集。通常的情况是，操作系统当前使用什么字符集，就映射为什么字符集。但是总存在一些特殊情况。假如操作系统当前使用的是 ascii 字符集，则会被映射为 MySOL 支持的 latin1 字符集。如果 MySOL 不支持操作系统当前使用的字符集，则会将客户端默认的字符集设置为 MySOL 的默认字符集。

在 MySOL5.7 以及之前的版本中，MySQL 的默认字符集为 latin1。自 MySOL8.0 版本开始，MySQL的默认字符集政为 utf8mb4。

另外，如果在启动 MySOL 客户端时设置了 default-character-set 启动选项，那么服务器会忽视操作系统当前使用的字符集，直接将 default-character-set 启动选项中指定的值作为客户端的默认字符集。

在连接服务器时，客户端将默认的字符集信息与用户名、密码等信息一起发送给服务器，服务器在收到后会将 character_set_client、character_set_connection 和 character_set_results 这3个系统变量的值初始化为客户端的默认字符集。

在客户端成功连接到服务器后，可以使用 SET 语句分别修改 character_set_client、character_set_connection 和 character_set_results 系统变量的值，也可以使用下面的语句一次性修改这几个系统变量的值：

SET NAMES charset_name;

上面这条语句与下面这3条语句的效果一样：

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

不过需要特别注意的是，SET NAMES 语句并不会改变客户端在编码请求字符串时使用的字符集，也不会修改客户端的默认字符集。

客户端接收到响应

客户端收到的响应其实也是一个字节序列。对于类 UNIX 操作系统来说，收到的字节序列基本上相当于直接写到黑框框中（请注意这里的用词是 “基本上相当于”，其实内部还会做一些工作，这里就不关注具体细节了），再由黑框框将这个字节序列解释为人类能看懂的字符（如果没有特殊设置的话，一般用操作系统当前使用的字符集来解释这个字节序列）。对于Windows 操作系统来说，客户端会使用客户端的默认字符集来解释这个字节序列。

对于类 UNIX 操作系统来说，在向黑框框中写入数据时，调用的是系统函数 fputs、putc 或者 fwrite 对于 Windows 操作系统来说，调用的是系统函数 WriteConsoleW。

我们通过一个例子来理解这个过程。比如操作系统当前使用的字符集为 UTF-8，我们在启动 MySQL 客户端时使用了--default-character-set=gbk 启动选项，那么客户端的默认字符集会被设置为 gbk，服务器的 character_set_results 系统变量的值也会被设置为 gbk。现在假设服务器的响应中包含字符 ‘我’，发送到客户端的字节序列就是我的 gbk 编码 0xCED2，针对不同的操作系统，会发生如下行为。

• 对于类UNIX 操作系统来说，会把接收到的字节序列（也就是0xCED2） 直接写到黑框框中，并默认使用操作系统当前使用的字符集（UTF-8）来解释这个字符。很显然无法解释，所以我们在屏幕上看到的就是乱码。
• 对于类Windows 操作系统来说，会使用客户端的默认字符集（gbk）来解释这个字符很显然会成功地解释成字符 “我”。

上面唠叨了这么多东西，主要是想让大家明白5件事情：

• 客户端发送的请求字节序列是采用哪种字符集进行编码的；—— 操作系统默认字符集或者--default-character-set
• 服务器接收到请求字节序列后会认为它是采用哪种字符集进行编码的；—— character_set_client
• 服务器在运行过程中会把请求的字节序列转换为以哪种字符集编码的字节序列；—— character_set_connection
• 服务器在向客户端返回字节序列时，是采用哪种字符集进行编码的；—— character_set_results
• 客户端在收到响应字节序列后，是怎么把它们写到黑框框框中的。—— 操作系统默认或者--default-character-set

3.3 比较规则的应用

结束了字符集的“漫游”，我们把视角再次聚焦到比较规则。比较规则通常用来比较字符串的大小以及对某些字符串进行排序，所以有时候也称为排序规则。

比如表t的列 col 使用的字符集是 gbk，使用的比较规则是 gbk_chinese_ci，我们向里面插入几条记录：

mysql> INSERT INTO t(col) VALUES('a'), ('b'), ('A'), ('B');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0 Warnings: 0

我们在查询的时候按照 col 列排序一下：

mysql> SELECT * FROM t ORDER BY Col;
+------4
| col
+------+
| a
| A
| b
| B	   
| 我   
+------+
5 rows in set (0.00 sec)

可以看到在默认的比较规则 gbk_chinese_ci 中是不区分大小写的。我们现在把列 col 的比较规则修改为 gbk_bin：

mysql> ALTER TABLE t MODIFY col VARCHAR(10) COLLATE gbk bin;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

由于 gbk_bin 是直接比较字符的二进制编码，所以是区分大小写的。我们看一下排序后的查询结果：

mysql> SELECT * FROM t ORDER BY col;
+------+
| col
+------+
| A
| B
| a
| b
| 我

大家在对字符串进行比较，或者对某个字符串列执行排序操作时，如果没有得到想象中的结果，需要思考一下是不是比较规则的问题。

列 col 中各个字符在使用 gbk 字符集编码后对应的数字如下:

• ‘A’->65（十进制）；
• ‘B’->66（十进制）；
• ‘a’->97（十进制）；
• ‘b’->98（十进制）；
• ‘我’->52946（十进制）。

4. 总结

字符集指的是某个字符范围的编码规则。

比较规则是对某个字符集中的字符比较大小的一种规则。

在 MySQL 中，一个字符集可以有若干种比较规则，其中有一个默认的比较规则。一个比较规则必须对应一个字符集。

在 MySQL 中查看支持的字符集和比较规则的语句如下：

• SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
• SHOW COLLATION [LIKE 匹配的模式];

MySQL 有 4 个级别的字符集和比较规则，具体如下。

• 服务器级别

  character_set_server 表示服务器级别的字符集，collation_server 表示服务器级别的比较规则。

• 数据库级别

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

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

  character_set_database 表示当前数据库的字符集，collation_database 表示当前数据库的比较规则。这两个系统变量只用来读取，修改它们并不会改变当前数据库的字符集和比较规则。如果没有指定当前数据库，则这两个系统变量与服务器级别相应的系统变量具有相同的值。

• 表级别

  创建和修改表的时候指定表的字符集和比较规则:

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

• 列级别
  创建和修改列的时候指定该列的字符集和比较规则:

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

从发送请求到接收响应的过程中发生的字符集转换如下所示。

• 客户端发送的请求字节序列是采用哪种字符集进行编码的。

  这一步骤主要取决于操作系统当前使用的字符集；对于 Windows 操作系统来说，还与客户端启动时设置的 default-character-set 启动选项有关。

• 服务器接收到请求字节序列后会认为它是采用哪种字符集进行编码的。

  这一步骤取决于系统变量 character_set_client 的值。

• 服务器在运行过程中会把请求的字节序列转换为以哪种字符集编码的字节序列。

  这一步骤取决于系统变量 character_set_connection 的值。

• 服务器在向客户端返回字节序列时，是采用哪种字符集进行编码的。

  这一步骤取决于系统变量 character_set_results 的值。

• 客户端在收到响应字节序列后，是怎么把它们写到黑框框中的。

  这一步骤主要取决于操作系统当前使用的字符集：对于 Widows 操作系统来说，还与客户端启动时设置的 default-character-set 启动选项有关。在这个过程中，各个系统变量的含义如表 3-6 所示。

文章来源地址https://www.toymoban.com/news/detail-738327.html

到了这里，关于3.字符集和比较规则简介的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！