从零开始学Mysql - 字符集和编码（下）

引言

这个系列的文章是依据《MySQL是怎么运行的：从根儿上了解MySQL》这本书的集体笔记总结专栏，这里非常举荐大家精读一下这本书，也是目前市面上集体所知的讲述Mysql原理的为数不多的好书之一，好了废话不多说咱们上面进入正题。

上篇：从零开始学Mysql - 字符集和编码（上）

因为这个系列波及的知识点还是挺多的，这里先依据文章的知识点汇总了一份集体思维导图：幕布地址

回顾上篇

因为上一篇和本篇编写的间隔时间比拟久，这里咱们先来回顾一下上一篇讲了什么内容：

  1. 在Mysql数据中，字符串的大小比拟实质上是通过上面两种形式进行比拟，简而言之字符串的大小比拟是依赖字符集和比拟规定来进行比拟的。

    1. 将字符对立转为大写或者小写再进行二进制的比拟
    2. 或者大小写进行不同大小的编码规定编码

  2. 简述和把握几个比拟罕用的字符集：

    1. ASCII 字符集：收录128个字符，
    2. ISO 8859-1 字符集：在ASCII 字符集根底上进行扩大，共256个字符，字符集叫做latin1，也是Mysql5.7之前默认的字符集（Mysql8.0之后默认字符集为utf8mb4）
    3. GB2312：首先须要留神的是不仅仅只有“汉字”哦，比拟非凡的是采纳了变长编码规定，变长编码规定值得是依据字符串的内容进行不同的字符集进行编码，比方'啊A'中‘啊’应用两个字节编码，'A'因为能够应用ASCII 字符集示意所以能够只应用一个字节进行编码
    4. GBK 字符集：对于GB2312进行字符集的扩大，其余和GB2312编码规定统一
    5. UTF8字符集：UTF-8规定依照1-4个字节的变长编码方式进行编码，最初UTF8和gbk一样也兼容了ASCII的字符集
提醒：这里有一个思考题目那就UTF-8mb3和UTF8-mb4的字符集有什么区别？这里也暗藏了一个历史遗留问题带来的坑，如果次要应用Mysql数据库这个坑有必要认真理解一下，在上篇的文章最初给出答案，这里不再赘述。
  1. 查看字符集命令：show charset;，比方：show charset like 'big%';
  2. 比拟规定查看：show collation [like 匹配模式]，比方show collation like 'utf_%';

  3. 字符集和比拟规定的级别分为四种：

    1. 服务器级别：能够通过配置文件进行设置，然而启动之后无奈批改服务器级别的字符集或者比拟规定。
    2. 数据库级别：如果没有指定数据库级别比拟规定或者字符集，则默认应用服务器的。
    3. 表级别：表级别在默认的状况下应用数据库级别的字符集和比拟规定。
    4. 列级别：列级别规定应用比拟少，通常在建表的时候指定，然而通常不倡议同一个表应用不同字符集的列。
  4. 最初，咱们回顾一下字符集和比拟规定的常见命令。
数据库级别查看字符集查看比拟规定零碎变量批改/创立形式案例
服务器级别show variables like 'character_set_server';SHOW VARIABLES LIKE 'collation_server'character_set_server
：以后服务器比拟规定collation_server：以后服务器比拟规定		批改配置文件
[server]
character_set_server=gbk
collation_server=gbk_chinese_ci		CREATE DATABASE charset_demo_db
CHARACTER SET gb2312
COLLATE gb2312_chinese_ci;
数据库级别show variables like 'character_set_database';show variables LIKE 'collation_database';character_set_database：以后数据库字符集 Collation_database：以后数据库比拟规定alter database 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比拟规定名称];		CREATE DATABASE charset_demo_db
CHARACTER SET gb2312
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 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比拟规定名称]]

ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比拟规定名称]		create table test(
id int auto_increment primary key
) character set utf8mb4
COLLATE utf8mb4_0900_ai_ci
列级别show full columns from admin like 'username';show full columns from admin like 'username';未设置状况下默认参考数据表的级别设置CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比拟规定名称], 其余列...
);
ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;

文章目标

在介绍注释之前，这里先提前总结本文的次要内容。

  1. 为什么在进行mysql查问的时候会呈现乱码，通过一个简略查问理解前因后果。
  2. 不同操作系统如何获取零碎字符集？
  3. 一个Sql申请的字符集转换规则细节讲述（重点）
  4. 不同比拟规定下字符串的比拟差异探讨和一些mysql的默认规定补充。

版本阐明

为了避免读者误会，这里提供一下本文的根本的操作环境：

  • mysql版本号：8.0.26
  • 操作系统：MacOs M1 2020

查问中的乱码是怎么来的？

在乱码的世界中有一个非常经典的词：“锟斤拷”，上面是对于“锟斤拷”的百科介绍：

是一串常常在搜索引擎页面和其余网站上看到的乱码字符。乱码源于GBK字符集和Unicode字符集之间的转换问题。除了锟斤拷以外，还有两组比拟经典的乱码，别离是"烫烫烫"和"屯屯屯"，这两个乱码产生自VC，这是debug模式下VC对内存的初始化操作。

乱码的实质其实就是字符串的编码方式和解码形式的不对立，比方应用UTF8的编码状况下“我”这字符会在别的字符集中被翻译为“æ‘”，因为UTF8的“我”应用的是三个字节的编码，当我这个字符被转为另一个编码的时候就会因为不同字符集被解析为不同的字符，读取和编码不是应用同一种形式最终就呈现问题了。

如何获取零碎字符集？

获取零碎的字符集须要针对不同的操作系统进行解释，咱们这里简略提一下，因为mysql根本都会部署到linux零碎，咱们就来看一下linux操作系统的字符集（上面为应用macos查看）：

shell> echo $LC_ALLzh_CN.UTF-8shell> echo $LC_CTYPEshell> 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。

从下面的这个图中咱们能够根本理解到上面的信息：

  1. 如果character_set_results 转化后的字符集和操作系统的字符集不同，那就很可能呈现乱码的可能。
  2. 不论客户端应用的是什么样模式的编码，最终都会转化为character_set_connection，尽管character_set_connection看上去没什么用然而如果character_set_connection和character_set_client字符集不统一，有可能因为无奈编码导致Mysql呈现正告。
  3. 如果客户端应用的字符集和服务端所应用的character_set_client 字符集不统一的话，就很可能呈现服务器无奈了解客户端申请的状况
  4. 一个申请的字符集转化会在客户端和服务端交互的时候实现两次，在服务器外部实现三次的转化操作，看上去非常繁琐，所以记住三个要害参数即可。

上面咱们来试验一下下面呈现可能乱码的状况：

首先是最直观的也是在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，collation_server，见名知义，能够分为连连贯级别，数据库级别和server服务器级别，对于比拟规定应用法则在 从零开始学Mysql - 字符集和编码（上） 进行了探讨，这里就不开展了：
mysql> show variables like 'collation_%';
+----------------------+--------------------+

| Variable_name | Value |

+----------------------+--------------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8mb4_0900_ai_ci |

+----------------------+--------------------+

依据前文的表咱们先插入几条随机数据：

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  c7  B4  ABCD5  A6  a

最初咱们能够尝试把字符集改为其余的字符集再看一下排序的后果，能够看到排序的后果产生了扭转：

备注： gbk_bin 是间接比拟字符的编码，所以是辨别大小写的
ALTER TABLE test MODIFY name VARCHAR(255) COLLATE gbk_bin;1  我是2  我3  我8  c6  a7  B4  ABCD5  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的数据对于字符集的内容细节还是比拟多的，集体也认为字符集的转化的确有点绕，所以这一块的知识点须要多回顾才行。