MySQL乱码问题终极指南

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
全局流量管理 GTM,标准版 1个月
简介:
mysql的字符集设置众多,从客户端到连接到结果集,从服务器到库到表到列,都可以设置字符集,灵活很强大,但就是很容易出问题,如果不了解其机制,很容易就出现乱码问题。
为了普度众生,让大家尽量在工作中少受或者不受乱码的骚扰、困扰,这里我结合之前其它同学在论坛的发帖,并结合自己的理解和实践,详细分析总结了一下,以飨各位看官。

关于字符集和乱码的基础知识这里就不详细说明了(请自行搜索),但有一个问题需要特别强调一下:乱码是怎么产生的
这个问题相信很多同学都是模棱两可,或者没有认真想过,反正理解就是”字符编码“不对导致乱码,但没有真正想过为什么”字符编码“会导致乱码。
答案其实很简单:“转换导致乱码”!
根据这个原则来判断,各种情况就很简单了:
1)数据传送过程中不会导致乱码
2)数据存储不会导致乱码
3)数据输入和输出(包括显示)可能导致乱码
4)数据接收和发送可能导致乱码

更详细的解释:转换导致乱码是指本来是A字符集的数据被当成了B字符集进行解析,而不是说正确的A字符集转换为B字符集。
例如:如下mysql字符处理机制流程图中,mysql客户端发送的实际上是2个gbk字符(4字节),但character_set_connection
设置了utf8,于是mysql服务器将收到的4字节gbk数据按照utf8解析,得到1个中文字符+1个字节,这时就产生乱码了;

如果character_set_connection 设置为gbk,mysql服务器收到数据后按照gbk解析,得到两个正确的中文,然后再转换为这两个中文对应的utf8编码,这就不会产生乱码。)

【mysql的字符处理机制】
详细的处理机制如下图:
 

我们模拟一下一条数据从插入到读取的处理流程,看看在整个流程中,字符集是如何辗转腾挪的。
【插入流程】
1. 客户端设定了自己的编码(character_set_client),接收用户的输入;
2. 客户端将用户的输入“转换”成连接的编码(character_set_connection) =====> 第一次转换
3. 客户端将转换后的数据发送给服务器;                               =====> 传输不会导致编码转换
4. 服务器收到客户端的数据,再判断数据列的字符集,进行字符转换       =====> 第二次转换
5. 服务器将数据存储(例如磁盘)                                     =====> 存储不会导致编码转换  
【读取流程】
略去前面的sql语句处理流程,从数据读取开始
1. 服务器从存储(例如磁盘)读取数据                                 =====> 存储不会导致编码转换,因此从存储读取也不需要
2. 服务器判断当前连接返回结果的字符集(character_set_results),
   将读取的数据转换为结果集要求的数据                               =====> 逆向的第一次转换,对应正向的第二次编码转换
3. 服务器将数据发送给客户端                                         =====> 传输不会导致编码转换
4. 客户端收到服务器的数据,根据客户端的字符集(character_set_client)进行编码转换          =====> 逆向第二次转换,对应正向第一次编码转换
5. 客户端显示数据                                                   =====> 你能看到乱码的时候


有了这个流程,我们就很容易定位乱码可能产生的地方,以及产生乱码的字符集配置究竟是哪个了。
理想的情况是整个流程中,所有涉及字符转换的地方都不需要转换,这样就不会产生乱码了。

有了上面的理论分析后,我们再结合一个乱码的抓包实例,加深理解,其中有一些问题,请大家思考一下,看看是否真的理解了。
环境:
+--------------------------+-----------------------------------------------------+
| Variable_name            | Value                                               |
+--------------------------+-----------------------------------------------------+
| character_set_client     | latin1                                              |
| character_set_connection | latin1                                              |
| character_set_database   | utf8                                                |
| character_set_filesystem | binary                                              |
| character_set_results    | latin1                                              |
| character_set_server     | utf8                                                |
测试语句是插入一个中文字符“你”,其utf8编码为"0xE4 0xBD 0xA0",

1. latin1发送包 
思考一下1:为什么客户端和连接都设置了latin1,但最终发送的是正确的utf8编码呢?

2. latin1接收包
 
思考一下2:为什么接收到的还是正确的utf8编码?

3. latin1不显示乱码
 
思考一下3:为什么latin1显示了正确的utf8字符?

4. utf8接收包
 
思考一下4:为什么连接的字符集和数据库的字符集设置成一样了,接收的数据反而不是utf8了?(请与latin1接收数据包对比)

5. utf8显示包


思考一下5:为什么连接的字符集和数据库的字符集设置成一样了,显示反而乱码了? 

怎么样,上面的思考题是否都有答案了,如果没有,相信下面这幅图能够帮助你:
这个抓包案例的字符变化图解:

 

附:mysql字符编码操作技巧
【查看字符集设置】
mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------------+
| Variable_name            | 说明                                                |
+--------------------------+-----------------------------------------------------+
| character_set_client     | 客户端字符集                                        |
| character_set_connection | 当前连接字符集                                      |
| character_set_database   | 数据库字符集                                        |
| character_set_filesystem | 文件系统字符集,不要修改,使用binary即可            |
| character_set_results    | 返回结果集字符集                                    |
| character_set_server     | 服务器默认字符集,当数据库、表、列没有设置时,      |
|                          |     默认使用此字符集                                |
| character_set_system     | 固定为utf8                                          |
+--------------------------+-----------------------------------------------------+
 
【修改字符集设置】
服务器的配置在服务器建立的时候就由DBA设置好了,不推荐后续再改
通过SET NAMES utf8命令同时设置character_set_client/character_set_connection/character_set_results的字符集
建议所有配置都设置成utf8

【问题答案】
思考一下1:为什么客户端和连接都设置了latin1,但最终发送的是正确的utf8编码呢?
客户端设置了latin1,而我的语句是从notepad++中写好的,是utf8格式的;
中文utf8是3个字节,而latin1是按照单个字节解析的,虽然进行了转换,但不会导致二进制内容的变化,但实际上mysql客户端认为我输入了3个latin1字符;
如果客户端设置的编码是2个字节的gbk,这时转换就会发生乱码,utf8的3个字节会被转换为1个gbk字符(可能是乱码,也可能不是乱码)加上一个西欧字符(小于128就是英文,大于128就是其它西欧文)

思考一下2:为什么接收到的还是正确的utf8编码?
这是因为mysql服务器从将数据从“列”的编码(utf8)转换为latin1了,而列存储的数据并不是真正的utf8的中文“你”对应的"0xe4 0xbd 0xa0",
而是后面抓包看到的“c3a4 c2bd c2a0”(6个字节),mysql服务器将utf8的c3a4转换为latin1的0xe4,c2bd转换为0xbd, c2a0转换为0xa0

思考一下3:为什么latin1显示了正确的utf8字符?
因为mysql客户端收到了mysql服务器转换后的"0xe4 0xbd 0xa0",并把这个数据当做latin1的3个字符处理,然后抛给终端(我的是SecureCRT),
SecureCRT又把这三个latin1当做uft8处理,结果中文的“你”就显示出来了。

思考一下4:为什么连接的字符集和数据库的字符集设置成一样了,接收的数据反而不是utf8了?(请与latin1接收数据包对比)
字符集都一样的情况下,整个流程中不需要进行编码转换,直接将存储的“c3a4 c2bd c2a0”返回给客户端

思考一下5:为什么连接的字符集和数据库的字符集设置成一样了,显示反而乱码了?
参考思考4,客户端收到数据后也直接抛给终端显示,终端认为是两个utf8字符,并且找到了对应字符并显示,但我们看不懂,所以知道是乱码了,但这两个字符显示并没有错,如果真正找不到字符,可能会显示问号或者字符集规定的缺省符号

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
关系型数据库 MySQL
MySQL实战基础知识入门(5):命令行汉字中文输出乱码的解决方案
MySQL实战基础知识入门(5):命令行汉字中文输出乱码的解决方案
86 0
|
关系型数据库 MySQL API
Mysql 8.0 C API连接和获取数据实例(附解决乱码的彩蛋)
Mysql 8.0 C API连接和获取数据实例(附解决乱码的彩蛋)
Mysql 8.0 C API连接和获取数据实例(附解决乱码的彩蛋)
|
JSON 数据库 数据格式
mysql-connector-java json数据乱码
经测试版本最低也5.1.40以上,于是升级到了5.1.40.现在的最新版本是5.1.49,果然数据库的json数据查询正常了,如果com.mysql.jdbc.Driver报错,删除scope,查询时候要使用json函数:json_extract(json字段名,’$’);
mysql-connector-java json数据乱码
|
关系型数据库 MySQL
Mysql输出中文显示乱码处理
Mysql输出中文显示乱码处理
438 0
Mysql输出中文显示乱码处理
|
SQL 关系型数据库 MySQL
MySQL - Navicat MySQL 乱码解决
MySQL - Navicat MySQL 乱码解决
145 0
MySQL - Navicat MySQL 乱码解决
|
关系型数据库 MySQL
最讨厌的乱码之 MySQL 乱码
乱码原因 解决方法 原因 总结
最讨厌的乱码之 MySQL 乱码
|
存储 SQL 关系型数据库
【肝了三天-建议收藏】实战-万字长文-带你刨析MySQL乱码、字符集和比较规则
【肝了三天-建议收藏】实战-万字长文-带你刨析MySQL乱码、字符集和比较规则
193 0
【肝了三天-建议收藏】实战-万字长文-带你刨析MySQL乱码、字符集和比较规则
|
关系型数据库 MySQL Java
【MySQL】SpringBoot数据库操作乱码问题
【MySQL】SpringBoot数据库操作乱码问题
439 0
|
关系型数据库
MySQL命令窗口下中文显示乱码的解决过程
MySQL命令窗口下中文显示乱码的解决过程在dos客户端输出窗口中查询表中的数据,还有项目部署到服务器上时前台的页面,中文数据都显示成乱码,如下图所示: 这个问题困扰了我一天,后来解决了才发现原来我的方向错了,一直我以为是SpringBoot项目的原因,因为之前我已经检查过数据库这边的字符集问题,...
1192 0
|
数据可视化 MySQL 关系型数据库
mysql命令框中向表中插入中文字符,在可视化工具MySQL Workbeach或phpMyAdmin中不显示或显示乱码的问题解决
mysql命令框中向表中插入中文字符,在可视化工具MySQL Workbeach或phpMyAdmin中不显示或显示乱码的问题解决一、问题导出     在刚刚开始学习MySQL数据库应用与开发这门课程的时候,老师建议我们使用可视化工具MySQL Workbeach进行数据库的相关管理,但是小编在mysql命令框使用insert命令向表中插入中文数据的时候发现出现了warnings警告,并且打开MySQL Workbeach查看输入数据的时候发现,输入的数据为空或者是?????几个问号,于是便想到了字符格式的问题。
1291 0