深入了解mysql数据传输编码原理

一、基本概念（这里引用http://www.laruence.com/2008/01/05/12.html）

1、 给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码(Encoding)。例如，我们给字符’A'赋予数值0，给字符’B'赋予数值1，则0就是字符’A'的编码；

2、 给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集(Character Set)。例如，给定字符列表为{‘A’,'B’}时，{‘A’=>0, ‘B’=>1}就是一个字符集；

3、字符序(Collation)是指在同一字符集内字符之间的比较规则；

4、确定字符序后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系；

5、每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符序，其中有一个是默认字符序(Default Collation)；

6、MySQL中的字符序名称遵从命名惯例：以字符序对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如：在字符序“utf8_general_ci”下，字符“a”和“A”是等价的；

二、名词解释

1、character_set_client:客户端数据解析、编码的字符集。

2、character_set_connection:连接层字符集。

3、character_set_server:服务器内部操作字符集。

4、character_set_results:查询结果字符集。

5、character_set_database:当前数据库的字符集。

6、character_set_system:系统源数据(字段名等)字符集。

注：

1、还有以collation_开头的同上面对应的变量，用来描述字符序。

2、服务端编码、解析时，是按照前一环节的编码进行解析的，按照各自的字符集进行编码的。

3、character_set_server是mysql数据库内存的操作字符集。如果创建数据库时，没有指定数据库的字符集，则使用character_set_server的字符集作为默认字符集；如果创建表时，没有指定表的字符集，则使用character_set_database的字符集作为默认字符集；如果在创建字段时，没有指定字段的字符集，则使用表的字符集作为默认字符集。

4、set names gbk;等同于同时设置character_set_client，character_set_connection，character_set_results这三个字符集。

三、数据传输过程中字符集编码、解析

1.客户端以及编码

        我们使用jdbc操作数据的程序、navicate操作工具、操作系统操作数据库这些都认为是客户端。客户端navicate的编码为utf8，windows默认的编码为gbk。一般情况下，utf8编码的中文占三个字节，gbk占用两个字节(一个字节是8位二进制，也就是两个十六进制)。

Navicate操作（utf8）

mysql> show variables like '%char%';

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

| Variable_name            | Value                      |

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

| character_set_client     | utf8                       |

| character_set_connection | utf8                       |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | utf8                       |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

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

8 rows in set

 

mysql> select hex('我很帅');

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

| hex('我很帅')      |

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

| E68891E5BE88E5B885 |

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

1 row in set

Windows上操作（gbk）

mysql> show variables like '%char%';

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

| Variable_name            | Value                      |

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

| character_set_client     | gbk                        |

| character_set_connection | gbk                        |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | gbk                        |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

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

8 rows in set

 

mysql> select hex('我很帅');

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

| hex('我很帅')      |

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

| CED2BADCCAA7       |

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

1 row in set

2.解析过程

a.sql语句通过客户端编码发送到mysql服务器上;

b.character_set_client对接收到的数据进行解码，这里解码按照character_set_client编码进行解码，最后按照自身字符集进行编码。

c.character_set_connection收到来自client的编码，这里进行字符集转换。注意这里s.decode(character_set_client).encode(character_set_connection)。

d.character_set_server这里是服务器内部使用的字符集，如果单独给字段添加字符集，这里取的是字段字符集。这里收到connection的编码，进行字符集转换。e.decode(character_set_connection).encode(character_set_server)。

3.查询过程

a.mysql服务器转换为character_set_results发送到客户端，其实这里你只要知道最后从服务器出来的时候是按照character_set_results编码的。

b.发送到客户端之后，按照客户端编码进行解码。所以如果character_set_results和客户端编码不一致，会导致查询乱码。

ps：这里我创建一个gbk表，里面插入有数据（自己构造，带有中文）。

Navicate操作（utf8）

mysql> select @@character_set_results;

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

| @@character_set_results |

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

| utf8                    |

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

1 row in set

 

mysql> select name_man from wsyy_marry where id = 1;

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

| name_man |

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

| 赫立广   |

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

1 row in set

 

mysql> set @@session.character_set_results = 28;

Query OK, 0 rows affected

 

mysql> select @@character_set_results;

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

| @@character_set_results |

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

| gbk                     |

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

1 row in set

 

mysql> select name_man from wsyy_marry where id = 1;

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

| name_man |

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

| ������   |

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

1 row in set

Windows操作（gbk）

mysql> select @@character_set_results;

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

| @@character_set_results |

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

| gbk                     |

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

1 row in set

 

mysql> select name_man from wsyy_marry where id = 1;

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

| name_man |

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

| 赫立广   |

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

1 row in set

 

mysql> set @@session.character_set_results = 33;

Query OK, 0 rows affected

 

mysql> select @@character_set_results;

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

| @@character_set_results |

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

| utf8                    |

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

1 row in set

 

mysql> select name_man from test.wsyy_marry where id = 1;

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

| name_man |

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

| 璧珛骞�   |

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

1 row in set

四、总结

1、字符集设置33，代表utf8；28代表gbk字符集设置33；

2、字符集出现乱码的地方最大可能在两个地方，character_set_client和character_set_results。如果这两个地方的编码个客户端编码不一致会乱码。告诉你，有可能存都存不进去。

3、后面也有可能出现编码问题，如果中文字符串，latin1解码不了中文，则会出现乱码。也就是说进行编码转换的时候可能出现不兼容的情况，latin1编码的都能被utf8兼容，反之就可能出现”？？”这样的情况。

4、看下来之后老老实实不要乱设置character_set_client这些值。如果能保持所有的都是utf8，那肯定没问题。

五、疑问

客户端编码	client	connection	server	结果
utf8	gbk	gbk	gbk/utf8	插入失败
utf8	gbk	utf8	gbk/utf8	插入乱码
utf8	utf8	gbk	gbk/utf8	正常插入
utf8	urf8	utf8	gbk/utf8	正常插入

 

 

 

 

 

 

 

 

我做了如下统计，客户端编码和character_set_client编码不一致有可能出现插入乱码，也有可能出现数据插都插不进去。我也不知道为啥会不能插入数据库。下面这两种情况很大都是可能是乱码导致的报错。

1、Incorrect string value: '\xB6' for column 'NAME_MAN' at row 1。

2、SQLException errorcom.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'NAME_MAN' at row 1。

如果对这里面介绍有异议或者能有更全面的理解可以在下面留言，大家共同学习。

六、参考资料

1.http://www.jianshu.com/p/96ee5b2adef3

2.http://blog.csdn.net/kxcfzyk/article/details/37723367

3.http://www.laruence.com/2008/01/05/12.html