MySQL命令行插入数据乱码分析

MySQL命令行插入数据乱码分析

1.起因

在开发过程中， 在linux 的上MySQL 的客户端提交插入数据的SQL, 然后数据在页面展示的时候乱码，在网上查找了一些资料，说是MySQL 的客户端连接设置是Latin1导致的；(当然肯定还是有是由其他的原因也会导致一样数据乱码的情况，这里就不说其他的情况，就只说这一一种情况）

后续也是修改了客户端的连接方式为UTF8就可以；

2.修改步骤

1.先看MySQL 的客户端连接的字符集

[root@localhost sh]# mysql --help
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
.....
default-character-set             latin1


//使用说明
  --default-character-set=name 
                      Set the default character set.

1
2
3
4
5
6
7
8
9
10
11

2.在连接的时候添加该参数

[root@localhost sh]# mysql -uxxxx -pxxxxxx   --default-character-set=utf8
1

3.在执行原来有中文的SQL 无乱码发生

3.思考

在使用网上的这种方法修改后，虽然是成功的让数据不乱码； 但是本身还是很好奇，

为什么会出现这样的情况？ 为什么修改了客户端的参数后就可以恢复正常？ 已经MySQL 数据在网络传输中的编码字符集到底是什么？数据在磁盘存储是怎么存储的，带着这些疑问，我就展开了后面的思考；

4.MySQL 中的字符集变量

在很久之前的学习中，知道有个命令可以查看MySQL所有相关的字符集，但是不太清除这些字符集对应的含义已经作用是什么？

SHOW VARIABLES LIKE 'character%';
//  --default-character-set=utf8 客户端中执行
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


//  --default-character-set=latin1 客户端中执行
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

然后这里也收集了一些资料是关于这些变量的含义的([MySQL ：： MySQL 5.6 参考手册 ：： 5.1.7 服务器系统变量](https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html))

• character_set_client ：从客户端到达的语句的字符集。 此变量的会话值是使用字符设置的客户端连接到 服务器
• character_set_connection：用于指定不带 字符集介绍器和用于数字到字符串的转换
• character_set_database：默认数据库使用的字符集
• character_set_filesystem：文件系统字符集。此变量用于 解释引用文件名的字符串文本(varchar 类型)
• character_set_results：用于将查询结果返回到 客户
• character_set_server ：服务器默认字符集
• character_set_system： 服务器用于存储标识符的字符集，该值始终为。utf8
• character_sets_dir ：安装字符集的目录

可以看到修改连接方式后，下面三个发生了修改

• character_set_client
• character_set_connection
• character_set_results

5.不同编码客户端执行相同SQL

创建表

-- utf8 客户端  我们关注存储数据  这里那个客户端执行都一样 
create table charcode(
    id int auto_increment primary key ,
    name varchar(64) comment '姓名'
) charset =utf8 engine =innodb;
1
2
3
4
5

charcode.idb 数据文件 【16进制】

6.不同编码客户端读取数据

UTF8客户端读取数据(在latin 多插入了几条数据，前面只写了id 5,6,7，就拿这三个举例子好了 )

-- utf8 客户端
mysql> select * from charcode;
+----+----------------------------+
| id | name                       |
+----+----------------------------+
|  1 | 语异常                  |
|  2 | 语法异常               |
|  3 | 语法异常               |
|  4 | 语法异常               |
|  5 | 语法异常               |
|  6 | aaaaaaaaa                  |
|  7 | 语法异常                   |
|  8 | bbbbbbbbb                  |
+----+----------------------------+
8 rows in set (0.00 sec)

---latin1 客户端
mysql> select *  from charcode;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 语异常    |
|  2 | 语法异常 |
|  3 | 语法异常 |
|  4 | 语法异常 |
|  5 | 语法异常 |
|  6 | aaaaaaaaa    |
|  7 | ????         |
|  8 | bbbbbbbbb    |
+----+--------------+

mysql>
//语法异常  utf 编码 e8afade6b395e5bc82e5b8b8
//语法异常    utf 编码 C3A8C2AFC2ADC3A6C2B3E280A2C3A5C2BCE2809AC3A5C2B8C2B8  ? 就是存储数据的时候数据已经乱码？
				iso-8859-1 ：E8AFADE6B32EE5BC2CE5B8B8	
这里可以理解 底层存数据是按照utf 存储的数据 也就是该表就是utf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

1.客户端从控制台标准输入读取一行命令文本，其编码为操作系统编码； 语法异常 utf8 编码 e8 af ad e6 b3 95 e5 bc 82 e5 b8 b8

2.客户端将命令文本发送给服务器；

3.服务器把收到的文本解码为character_set_client latin1编码，这个编码通常与客户端charset_info一致； （客户端以为和shell 客户端一样导致），此时是latin1，服务端以为 e8afade6b395e5bc82e5b8b8 是ISO-8859 ，然后数据表设置的是UTF8，服务器会把e8afade6b395e5bc82e5b8b8 以ISO-8859 解码(单字节)然后再以UTF8 编码，存储到服务器是就是C3A8C2AFC2ADC3A6C2B3E280A2C3A5C2BCE2809AC3A5C2B8C2B8 就是下面的低4步

4.服务器执行命令转成，产生结果 ；使用内部操作字符集(UTF8 (其中涉及到服务器字符集，数据库字符集，表字符集，字段字符集，优先级越来越高)) ；我们这里是表设置的UTF8 ，字段没有设置，所以最后的操作字符集是UTF8

C3A8C2AFC2ADC3A6C2B3E280A2C3A5C2BCE2809AC3A5C2B8C2B8 存到底层文件中

两个客户单读取数据

latin1

5.将结果转码为character_set_results发送给客户端 （latin1）；服务器输出是 E8AFADE6B32EE5BC2CE5B8B8

E8AFADE6B32EE5BC2CE5B8B8

6.客户端（latin1）将结果转码为操作系统编码，输出到控制台标准输出

E8AFADE6B32EE5BC2CE5B8B8 转为系统编码输出 语法异常

utf8

5.将结果转码为character_set_results发送给客户端 （utf8）

输出 ：C3A8C2AFC2ADC3A6C2B3E280A2C3A5C2BCE2809AC3A5C2B8C2B8

6.客户端（utf8）将结果转码为操作系统编码，输出到控制台标准输出 语法异常 出现乱码；

7.结论

问题的根源出现在 客户端没有根据操作系统的编码集在做一次转换； MySQL 会把操作系统的输出流，输入到服务器，服务器使用 default_charcter_set 来判断输入的编码；

当操作系统cmd 的编码和MySQL的客户端编码不一致的时候 ，乱码会出现； 系统标准输入（utf-8）–> 服务器以为是Latin1 ,然后解码 出错，存数据也会出错

当操作系统cmd 的编码和MySQL的客户端编码一致的时候，这个时候，是不换乱码的；MySQL就可以正确的解析出输出的流的编码，进而可以正确的转换；

为了验证这个：

修改操作系统，标准输入流字符集为GBK , 在修改 连接为 --default-character-set=gbk

插入中文的情况下，在GBK 和UTF8 的客户端下，均是正常中文；

命令行情况下：只要操作系统的标准输入和MySQL 客户端的编码保持一致，就没数据乱码；