从零开始学Mysql - 字符集和编码（下）
引言
这个系列的文章是根据《MySQL是怎样运行的：从根儿上理解MySQL》这本书的个人笔记总结专栏，这里十分推荐大家精读一下这本书，也是目前市面上个人所知的讲述Mysql原理的为数不多的好书之一，好了废话不多说我们下面进入正题。
上篇：从零开始学Mysql - 字符集和编码（上）
由于这个系列涉及的知识点还是挺多的，这里先根据文章的知识点汇总了一份个人思维导图：幕布地址
回顾上篇
因为上一篇和本篇编写的间隔时间比较久，这里我们先来回顾一下上一篇讲了什么内容：
- 在Mysql数据中，字符串的大小比较本质上是通过下面两种方式进行比较，简而言之字符串的大小比较是依赖字符集和比较规则来进行比较的。
- 将字符统一转为大写或者小写再进行二进制的比较
- 或者大小写进行不同大小的编码规则编码
- 简述和掌握几个比较常用的字符集：
- ASCII 字符集：收录128个字符，
- ISO 8859-1 字符集：在ASCII 字符集基础上进行扩展，共256个字符，字符集叫做latin1，也是Mysql5.7之前默认的字符集（Mysql8.0之后默认字符集为utf8mb4）
- GB2312：首先需要注意的是不仅仅只有“汉字”哦，比较特殊的是采用了变长编码规则，变长编码规则值得是根据字符串的内容进行不同的字符集进行编码，比如'啊A'中‘啊’使用两个字节编码，'A'因为可以使用ASCII 字符集表示所以可以只使用一个字节进行编码
- GBK 字符集：对于GB2312进行字符集的扩展，其他和GB2312编码规则一致
- UTF8字符集：UTF-8规定按照1-4个字节的变长编码方式进行编码，最后UTF8和gbk一样也兼容了ASCII的字符集
提示：这里有一个思考题目那就UTF-8mb3和UTF8-mb4的字符集有什么区别？这里也隐藏了一个历史遗留问题带来的坑，如果主要使用Mysql数据库这个坑有必要仔细了解一下，在上篇的文章最后给出答案，这里不再赘述。
- 查看字符集命令：
show charset;，比如：show charset like 'big%';
- 比较规则查看：
show collation [like 匹配模式]，比如show collation like 'utf_%';
- 字符集和比较规则的级别分为四种：
- 服务器级别：可以通过配置文件进行设置，但是启动之后无法修改服务器级别的字符集或者比较规则。
- 数据库级别：如果没有指定数据库级别比较规则或者字符集，则默认使用服务器的。
- 表级别：表级别在默认的情况下使用数据库级别的字符集和比较规则。
- 列级别：列级别规则使用比较少，通常在建表的时候指定，但是通常不建议同一个表使用不同字符集的列。
- 最后，我们回顾一下字符集和比较规则的常见命令。
| 数据库级别 |
查看字符集 |
查看比较规则 |
系统变量 |
修改/创建方式 |
案例 |
| 服务器级别 |
show variables like 'character_set_server'; |
SHOW VARIABLES LIKE 'collation_server' |
character_set_server<br />：当前服务器比较规则collation_server：当前服务器比较规则 |
修改配置文件<br />[server]<br /> character_set_server=gbk<br />collation_server=gbk_chinese_ci |
CREATE DATABASE charset_demo_db<br /> CHARACTER SET gb2312<br /> COLLATE gb2312_chinese_ci; |
| 数据库级别 |
show variables like 'character_set_database'; |
show variables LIKE 'collation_database'; |
character_set_database：**当前数据库字符集 Collation_database：****当前数据库**比较规则 |
alter database 数据库名<br /> [[DEFAULT] CHARACTER SET 字符集名称]<br /> [[DEFAULT] COLLATE 比较规则名称]; |
CREATE DATABASE charset_demo_db<br /> CHARACTER SET gb2312<br /> COLLATE gb2312_chinese_ci; |
| 表级别 |
show table status from '数据库名称' like '数据表名称' |
SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = '数据表名称' |
未设置情况下默认参考数据库的级别设置 |
CREATE TABLE 表名 (列的信息)<br />DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称<br /><br />ALTER TABLE 表名<br />[[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称] |
create table test(<br /> id int auto_increment primary key<br />) character set utf8mb4 <br />COLLATE utf8mb4_0900_ai_ci |
| 列级别 |
show full columns from admin like 'username'; |
show full columns from admin like 'username'; |
未设置情况下默认参考数据表的级别设置 |
CREATE TABLE 表名(<br /> 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列...<br />);<br />ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci; |
|
文章目的
在介绍正文之前，这里先提前总结本文的主要内容。
- 为什么在进行mysql查询的时候会出现乱码，通过一个简单查询了解来龙去脉。
- 不同操作系统如何获取系统字符集？
- 一个Sql请求的字符集转换规则细节讲述（重点）
- 不同比较规则下字符串的比较差别讨论和一些mysql的默认规则补充。
版本说明
为了防止读者误解，这里提供一下本文的基本的操作环境：
- mysql版本号：8.0.26
- 操作系统：MacOs M1 2020
查询中的乱码是怎么来的？
在乱码的世界中有一个十分经典的词：“锟斤拷”，下面是关于“锟斤拷”的百科介绍：
是一串经常在搜索引擎页面和其他网站上看到的乱码字符。乱码源于GBK字符集和Unicode字符集之间的转换问题。除了锟斤拷以外，还有两组比较经典的乱码，分别是"烫烫烫"和"屯屯屯"，这两个乱码产生自VC，这是debug模式下VC对内存的初始化操作。
乱码的本质其实就是字符串的编码方式和解码方式的不统一，比如使用UTF8的编码情况下“我”这字符会在别的字符集中被翻译为“我”，由于UTF8的“我”使用的是三个字节的编码，当我这个字符被转为另一个编码的时候就会因为不同字符集被解析为不同的字符，读取和编码不是使用同一种方式最终就出现问题了。
如何获取系统字符集？
获取系统的字符集需要针对不同的操作系统进行解释，我们这里简单提一下，由于mysql基本都会部署到linux系统，我们就来看一下linux操作系统的字符集（下面为使用macos查看）：
shell> echo $LC_ALL
zh_CN.UTF-8
shell> echo $LC_CTYPE
shell> echo $LANG
这三个变量的优先级是：$LC_ALL>$LC_CTYPE>$LANG，从结果可以看到这里仅仅为$LC_ALL有值，毫无疑问就是用它作为参考了，这里读者可能会问：如果$LC_ALL也没有值怎么办，这时候就会使用操作系统的默认字符集。
下面是windows系统如何了解字符集，在windows操作系统中字符集也叫做代码页，也就意味着一个字符集对应一个唯一的字符集，比如常用地936代表了GBK，65001代表了UTF-8，最后我们可以在windows的窗口chcp进行查看，由于Linux上使用mysql场景较少，这里就不演示了。
Linux底层获取字符集的函数为：nl_langinfo(CONDESET)，windows则为：GetConsoleCP感兴趣可以了解一波。
一个请求的编码历程
我们都知道mysql的请求无非就是发送一条sql语句，服务器收到命令之后将数据进行筛选整理最终进行编码返回结果，这个传输过程本质上是字符串与字符串的传输，而字符串的本质其实也只是一段字节的特定编码规则翻译过后便于理解而已，另外只要稍微了解一下mysql的数据行存储规则就会知道一个数据行实际存储的是一段字节编码，以innodb为例你可以简单的认为我们存储的所有数据类型其实本质上都是字符串，而对于文本内容则会根据系统的字符集对于内容进行不同的处理，那么这里就涉及到一个问题了，我们的字符是在客户端的请求的时候编码的，服务器又是如何解码返回给客户端的？还是说在送到mysql应用程序之后内部有一套转化的规则？下面我们一起来看一下一个请求的实际传输过程：
在具体的讲述之前，我们先来了解一下为什么书中不建议使用navicat这样的工具进行验证请求编码的规则处理，这里我们通过通过实操来了解：
首先我们通过工具navicat创建一个在数据库，并且创建的时候指定字符集和比较规则。
提示：需要注意的是此时utf8是utf8mb3的
接着我们构建一个简单的表，这表里面只有id和name两列数据。
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='测试字符集和编码';
接着我们插入一条数据：
INSERT INTO `test`.`test` (`id`, `name`) VALUES (1, '我');
最后我们可以随便执行一条sql语句进行测试，这当然不会出现任何问题，但是这里我们可以玩一点花样，比如把字符集和编码改成下面的形式，这时候你会发现你还是可以照常插入中文也可以插入任何数据，这是为什么？其实看一下Navicat对应的DLL建表语句就可以看出端倪。
-- DDL建表语句
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='测试字符集和编码';
可以看到如果你胡乱修改表的字符集，列的字符集会根据存储的内容选择兼容的方案，比如这里使用了gbk的编码格式进行处理。但是如果你通过下面的语句修改列的字符集，就会发现这条语句无法执行通过。
ALTER TABLE test MODIFY name VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-- 报错：1366 - Incorrect string value: '\xCE\xD2\xCA\xC7' for column 'name' at row 1, Time: 0.004000s
通过上面的案例，我们可以看到navicat“偷偷”在细节做了很多操作，如果不是为了了解底层这种处理当然很方便，但是如果我们想要知道字符集的处理流程，就不得不脱离可视化工具使用命令行来操作了。
下面我们就使用命令行来看一下如何进行操作。其实关于字符集和编码的转化规则很简单，只要一个命令就可以了解，从结果可以看到居然涉及到9个变量，而且有的看起来比较相似，比如client和connection的区别是什么？另外可以从下面的内容也可以看到字符集的存储位置，由于个人使用了macos做实验，所以存储的位置就是/usr/local/mysql-8.0.26-macos11-arm64/share/charsets/，这里建议读者可以自己连接一下自己的数据库看一下配置。
mysql> show variables like 'character_%';
+--------------------------+-------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.26-macos11-arm64/share/charsets/ |
+--------------------------+-------------------------------------------------------+
8 rows in set (0.00 sec)
一个请求的处理流程还是比较繁琐的，为了简化介绍我们通过画图来进行解释，我们可以直接看下图：
这里需要注意关于character_set_client，character_set_connection，character_set_results三个参数，这三个参数都是Session级别的，意味着每一个客户端的连接都维护了自己的一份字符集，这也是为什么设置character_set_client这一个参数的意义，另外如果Mysql不支持当前操作系统的字符集，就会把客户端的字符集设置为Mysql默认的字符集，我们可以通过下面的示例图了解到客户端的字符集转化的。
提示：Mysql5.7（包含）以及之前的版本中使用latin1作为默认字符集，Mysql8.0之后默认字符集为utf8mb4。
从上面的这个图中我们可以基本了解到下面的信息：
- 如果character_set_results 转化后的字符集和操作系统的字符集不同，那就很可能出现乱码的可能。
- 不管客户端使用的是什么样形式的编码，最终都会转化为character_set_connection，虽然character_set_connection看上去没什么用但是如果character_set_connection和character_set_client字符集不一致，有可能由于无法编码导致Mysql出现警告。
- 如果客户端使用的字符集和服务端所使用的character_set_client 字符集不一致的话，就很可能出现服务器无法理解客户端请求的情况
- 一个请求的字符集转化会在客户端和服务端交互的时候完成两次，在服务器内部完成三次的转化操作，看上去十分繁琐，所以记住三个关键参数即可。
下面我们来实验一下上面出现可能乱码的情况：
首先是最直观的也是在windows的操作系统中使用mysql最容易产生的问题，那就是我们有可能会把查询的结果内容出现乱码的情况。这里我们根据上面实验提到的表进行测试，直接通过修改results的字符集就可以看到效果：
mysql> set character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character_%';
+--------------------------+-------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | latin1 //被修改 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.26-macos11-arm64/share/charsets/ |
+--------------------------+-------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | ?? |
| 2 | ? |
+----+------+
2 rows in set (0.00 sec)
接下来 我们来尝试一下如果character_set_client和character_set_connection不一样会有什么问题，
mysql> show variables like 'character_%';
+--------------------------+-------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | ascii |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.26-macos11-arm64/share/charsets/ |
+--------------------------+-------------------------------------------------------+
8 rows in set (0.01 sec)
mysql> set character_set_client=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | ?? |
| 2 | ? |
+----+------+
2 rows in set (0.00 sec)
mysql> select * from test where name= '我';
Empty set, 1 warning (0.01 sec)
//==========注意关键点在这===========
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xE6\x88\x91' from utf8mb4 to ascii |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
我们把client设置为latin1，把connection设置为ascii，从报错可以看到虽然我们进行基本查询的时候没啥问题，但是一旦向服务器传输字符集设置有汉字就会出现报错了，所以在设置mysql的这三个参数的配置的时候，一定要把他们配置为同一个字符集，否则这个错误可能并不是那么容易发现。（当然这个案例还是很容易发现问题）
最后我们来试一下如何让服务端无法理解客户端的请求，其实也比较简单，就是让服务端采用的字符集范围比客户端使用的字符集范围小就可以了，比如把客户端设置为uft8，服务端设置为ascii，下面我们同样进行试验，为了不让代码过多，这里省去了修改字符集的其他命令后直接查看结果，这串英文告诉我们的是这两个字符集无法比较，也就出现前面说的服务端无法理解客户端请求的情况下：
mysql> select * from test where name ='我';
ERROR 1267 (HY000): Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (ascii_general_ci,COERCIBLE) for operation '='
将上面的内容实验完成之后，这时候我们会想要怎么把字符集修改回来，可以发现我们基本上主要使用的字符集也就 character_set_client、character_set_connection、character_set_results，这三个字符集兜兜转转一个个改实在是麻烦，Mysql也考虑到了这个问题，所以同样提供了一个快捷操作的命令：set name 字符集 （其实这个命令个人觉得也挺容易误解的），命令的效果大致等同于下面的命令：
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;
// 等同于
set name 字符集
在个人事迹操作的时候发生了一个比较有意思的事，在设置字符集的时候mysql给了提示说后续会在设置utf8字符集的时候默认把字符集改为;utf8mb4。
mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set character_set_client=utf8
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
另外还有一种方法是在配置文件里面进行配置，当然这是针对整个服务端启动的配置参数了，一般还是建议显示配置一下，以免不必要的麻烦：
[client]
default-character-set=utf8
啰嗦了这么多内容，其实可以一句话进行总结：我们通常都把 character_set_client、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。就是这么简单，只要这样设置就没有那么多奇怪的字符集和比较规则的问题。
比较规则的影响
说完了字符集下面说说比较规则，，之前说过字符集影响了字符串的的内容显示，那么比较规则则是影响了字符的比较操作，而比较这一操作则影响了字符串的比较和排序操作，为了说明对于比较规则的影响，这里我们同样用一个简单的案例来理解并进行介绍：
补充：比较规则的设计要比字符集的设置要直观一些，分为三个变量 collation_connection，collation_database，collationserver，见名知义，可以分为连连接级别，数据库级别和server服务器级别，关于比较规则使用规律在 从零开始学Mysql - 字符集和编码（上） 进行了讨论，这里就不展开了：<br />mysql> show variables like 'collation%';<br />+----------------------+--------------------+
<br />| Variable_name | Value |
<br />+----------------------+--------------------+
<br />| collation_connection | utf8_general_ci |
<br />| collation_database | utf8_general_ci |
<br />| collation_server | utf8mb4_0900_ai_ci |
<br />+----------------------+--------------------+
根据前文的表我们先插入几条随机数据：
INSERT INTO `test`.`test` (`id`, `name`) VALUES (1, '我是');
INSERT INTO `test`.`test` (`id`, `name`) VALUES (2, '我');
INSERT INTO `test`.`test` (`id`, `name`) VALUES (3, '我');
INSERT INTO `test`.`test` (`id`, `name`) VALUES (4, 'ABCD');
INSERT INTO `test`.`test` (`id`, `name`) VALUES (5, 'A');
INSERT INTO `test`.`test` (`id`, `name`) VALUES (6, 'a');
INSERT INTO `test`.`test` (`id`, `name`) VALUES (7, 'B');
INSERT INTO `test`.`test` (`id`, `name`) VALUES (8, 'c');
接着我们按照名称的顺序查询一下排序，这里在执行之前可以先查看一下当前的比较规则：
mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
8 rows in set (0.01 sec)
> select * from test order by name desc;
1 我是
2 我
3 我
8 c
7 B
4 ABCD
5 A
6 a
最后我们可以尝试把字符集改为其他的字符集再看一下排序的结果，可以看到排序的结果发生了改变：
备注： gbk_bin 是直接比较字符的编码，所以是区分大小写的
ALTER TABLE test MODIFY name VARCHAR(255) COLLATE gbk_bin;
1 我是
2 我
3 我
8 c
6 a
7 B
4 ABCD
5 A
这一点还是比较好理解的，不同的字符集比较规则是不一样的，但是如果是下面的比较操作，Mysql又是如何区分的呢？
select 'a' = 'A'
这个结果倒是对还是错，其实要看两个参数，第一个参数是：character_set_connection，他负责规定客户端传输到服务端实际需要转化的字符集，另一个参数是collaction_connection，他负责设置当前字符集的比较规则，所以可以通过改变这两个值改变上面的查询结果，这里也可以根据上面的实验尝试修改，这里就不进行演示，所以这个结果有可能是1，也有可能是0（这不是废话么！）。
另外还有一种情况是如果character_set_connection的字符集是gbk，而某一个表的数据列使用的字符集和比较规则是utf8的，这时候要以谁为准？这里也有一个硬性规定：默认以Mysql列所指定的字符集的规则为主，这也意味着如果以utf8进行比较，会先进行一次转化把gbk转为utf8之后在进行比较，当然一般也没人闲着没事某个列的字符集，这里仅仅作为一个小知识即可，记不住也没关系，等实际踩坑的时候在数据列和系统的字符集上留个心眼就好了。
总结
最后再总结一波，通过本文我们了解到一个字符串本身是通过字符集进行编码的，使用的是本文主要了解了一个请求是如何经过mysql处理的，他的处理过程如下：
- 请求先通过客户端的字符集转为character_set_client的字符集解码，然后通过将字符串通过 character_set_connection 的格式进行编码。
- 如果character_set_client和character_set_connection一致，则进行下一步操作，否则的话会尝试将请求中的字符串从 character_set_connection的字符集转换为具体操作的列 使用的字符集，如果转为操作列的字符集操作还是失败，则可能会拒绝处理的情况。
- 把某列的字符集转为character_set_results的字符集编码结果，同时发送给客户端，如果此时客户端和results的编码集不一致，那么就会出现乱码的情况。
- 客户端最终使用操作系统的字符集解析收到的结果集字节串。
而对于比较规则细节比较少，只要记住比较规则会影响内容的排序即可，如果某一次查询的排序结果和预期不符合，那么这时候可以从排序规则入手看一下是否可以通过排序规则调整可以更好的符合预期结果。
写在最后
Mysql的数据关于字符集的内容细节还是比较多的，个人也认为字符集的转化确实有点绕，所以这一块的知识点需要多回顾才行。
发表于 2022-1-25 22:57
