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

2018-06-18 00:07:19来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

一、基本概念(这里引用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

 

 

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL 命令行修改密码

下一篇:MySQL中的存储函数和存储过程的简单示例