• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

深入理解MySQL字符集及校对规则(二)

MySQL SEian.G 2年前 (2019-02-21) 552次浏览 已收录 0个评论
文章目录[隐藏]

上一篇呢,从理论上介绍了MYSQL字符集和校对规则是什么以及如何正确的使用字符集;关于第一部分的内容可参考:深入理解MySQL字符集及校对规则(一)

下面这部分呢,主要介绍MySQL字符编码转换原理以及字符集转化流程案例测试,通过实际的案例来分析字符集转化的原理;

一、MySQL字符编码转换原理

有这样一个疑问:若character_set_client为UTF8,而character_set_database为GBK,则会出现需要进行编码转换的情况,字符集转换的原理是什么?

解答如下:假设gbk字符集的字符串“你好”,需要转为utf8字符集存储,实际就是对于“你好”字符串中的每个汉字去utf8编码表里面查询对应的二进制,然后存储。

下面我们通过一幅图来看一下字符集转换的过程:
深入理解MySQL字符集及校对规则(二)

①MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;

②进行内部操作前将请求数据从character_set_connection转换为内部操作字符集

  • 确定步骤:
    –使用每个数据字段的CHARACTER SET设定值;
    –若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值;
    –若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
    –若上述值不存在,则使用character_set_server设定值;

③将操作结果从内部操作字符集转换为character_set_results。

二、字符集常见处理操作

1.查看字符集编码设置

mysql> show variables like ‘%character%’;

2、设置字符集编码

mysql> set names ‘utf8’;

相当于同时:

  • set character_set_client = utf8;
  • set character_set_results = utf8;
  • set character_set_connection = utf8;

3、修改数据库字符集

mysql> alter database database_name character set xxx;

只修改库的字符集,影响后续创建的表的默认定义;对于已创建的表的字符集不受影响。(一般在数据库实现字符集即可,表和列都默认采用数据库的字符集)

4、修改表的字符集

mysql> alter table table_name character set xxx;

只修改表的字符集,影响后续该表新增列的默认定义,已有列的字符集不受影响。

mysql> alter table table_name convert to character set xxx;

同时修改表字符集和已有列字符集,并将已有数据进行字符集编码转换。

5、修改列字符集

格式:

ALTER TABLE table_name MODIFY column_name {CHAR | VARCHAR | TEXT} (column_length)

[CHARACTER SET charset_name]

[COLLATE collation_name]

mysql> alter table table_name modify col_name varchar(col_length) character set xxx;

6、字符集的正确实践:

MySQL软件本身是没有字符集的,主要是因为工具所在的OS的字符集(Windows:gbk、Linux:utf8),所以字符集的正确实践非常重要:

(1)对于insert来说,character_set_client、character_set_connection相同,而且正确反映客户端使用的字符集

(2)对于select来说,character_set_results正确反映客户端字符集

(3)字符集转换最多发生一次,这就要求character_set_client、character_set_connection相同

(4)所有的字符集转换都发生在数据库端

综述:
1、建立数据库的时候注意字符集(gbk、utf8);
2、连接数据库以后,无论是执行dml还是select,只要涉及到varchar、char列,就需要设置正确的字符集参数。

三、MYSQL字符编码转化流程测试

使用实验来进一步说明MySQL服务器字符集以及连接字符集之间的作用以及关系。这里我们就简单演示这几种情况,这里我使用SecureCRT(utf8字符集)连接Linux系统

深入理解MySQL字符集及校对规则(二)

实例案例一:

1、查看当前数据库实例的字符集

root@localhost [wjqtest]>show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

root@localhost [wjqtest]>show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

2、创建测试表,字符集指定为utf8

root@localhost [wjqtest]>create table charset_test(id int,col1 varchar(30)) charset=utf8;            
Query OK, 0 rows affected (0.01 sec)

3、改变连接字符集为gbk

root@localhost [wjqtest]>set names gbk;
Query OK, 0 rows affected (0.00 sec)

root@localhost [wjqtest]>show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | gbk                              |
| character_set_connection | gbk                              |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | gbk                              |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

root@localhost [wjqtest]>show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | gbk_chinese_ci     |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

4、由于连接字符集修改成了gbk,这里插入数据会出现报错

root@localhost [wjqtest]>insert into charset_test values(1,'中国'); 
ERROR 1366 (HY000): Incorrect string value: '\xAD\xE5\x9B\xBD' for column 'col1' at row 1

注意:这里在测试的过程中,出现一些其他的情况,插入其他的汉字是没有问题(比如插入北京),那为什么会出现这种情况呢?是什么原因呢?

root@localhost [wjqtest]>insert into charset_test(col1) values('北京');
Query OK, 1 row affected (0.04 sec)

那么我们来看一下为什么北京可以插入,而中国不可以插入呢?这就涉及到了一个字符转码的问题;我们来看一下转码的问题


得到中文‘北京’的gbk下的16进制编码

root@localhost [wjqtest]>SELECT hex(CONVERT( '北京' USING gbk ));    
+------------------------------------+
| hex(CONVERT( '北京' USING gbk ))   |
+------------------------------------+
| E58C97E4BAAC                       |
+------------------------------------+
1 row in set (0.00 sec)

反推gbk的16进制编码,取回中文

root@localhost [wjqtest]>SELECT CONVERT( unhex('E58C97E4BAAC') USING gbk);            
+-------------------------------------------+
| CONVERT( unhex('E58C97E4BAAC') USING gbk) |
+-------------------------------------------+
| 北京                                      |
+-------------------------------------------+
1 row in set (0.00 sec)

从gbk的16进制编码直接转成utf8的16进制编码

root@localhost [wjqtest]>SELECT HEX(CONVERT(CONVERT( unhex('E58C97E4BAAC') USING gbk) USING utf8));            
+--------------------------------------------------------------------+
| HEX(CONVERT(CONVERT( unhex('E58C97E4BAAC') USING gbk) USING utf8)) |
+--------------------------------------------------------------------+
| E98D96E6A4BEE590AB                                                 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

通过utf8的16进制编码取回中文

root@localhost [wjqtest]>SELECT CONVERT( unhex('E98D96E6A4BEE590AB') USING utf8);              
+--------------------------------------------------+
| CONVERT( unhex('E98D96E6A4BEE590AB') USING utf8) |
+--------------------------------------------------+
| 北京                                             |
+--------------------------------------------------+
1 row in set (0.00 sec)

下面按照同样的方式编码取出‘中国’的GBK和utf8编码

root@localhost [wjqtest]>SELECT hex(CONVERT( '中国' USING gbk ));
+-----------------------------------+
| hex(CONVERT( 'SING gbk ))     |
+-----------------------------------+
| E4B8ADE59BBD                      |
+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [wjqtest]>SELECT CONVERT( unhex('E4B8ADE59BBD') USING gbk);            
+-------------------------------------------+
| CONVERT( unhex('E4B8ADE59BBD') USING gbk) |
+-------------------------------------------+
| 中国                                      |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [wjqtest]>
root@localhost [wjqtest]>SELECT HEX(CONVERT(CONVERT( unhex('E4B8ADE59BBD') USING gbk) USING utf8));            
+--------------------------------------------------------------------+
| HEX(CONVERT(CONVERT( unhex('E4B8ADE59BBD') USING gbk) USING utf8)) |
+--------------------------------------------------------------------+
| E6B6933FE6B597                                                     |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [wjqtest]>SELECT CONVERT( unhex('E6B6933FE6B597') USING utf8);                  
+----------------------------------------------+
| CONVERT( unhex('E6B6933FE6B597') USING utf8) |
+----------------------------------------------+
|                                          |
+----------------------------------------------+
1 row in set (0.00 sec)

通过上面的测试发现,北京经过转码回来之后依旧是北京,而中国在转码回来就不在是中国字符了;


好,那就接着上面的步骤4继续进行实验测试;

5、这里改变客户端的字符集为utf8,就可插入成功了

root@localhost [wjqtest]>set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [wjqtest]>show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | gbk                              |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | gbk                              |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

root@localhost [wjqtest]>insert into charset_test values(1,'中国');
Query OK, 1 row affected (0.01 sec)

6、查看数据,虽然插入成功了,但是发现返回的结果是乱码

root@localhost [wjqtest]>select * from charset_test;
+----+------+
| id | col1 |
+----+------+
|  1 |     |
+----+------+
1 row in set (0.00 sec)

7、改变character_set_results(服务器反馈SQL语句结果使用的字符集)字符集为utf8后,再次查询数据显示就正常了

root@localhost [wjqtest]>set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [wjqtest]>select * from charset_test;
+----+--------+
| id | col1   |
+----+--------+
|  1 | 中国   |
+----+--------+
1 row in set (0.00 sec)

8、下面接着进行测试,将SecureCRT工具调整为GB2312字符集,然后继续插入数据

深入理解MySQL字符集及校对规则(二)

root@localhost [wjqtest]>insert into charset_test(col1) values('涓);
Query OK, 1 row affected (0.02 sec)

9、查看数据发现还是显示乱码

root@localhost [wjqtest]>select * from charset_test;
+------+--------+
| id   | col1   |
+------+--------+
| NULL | 涓  |
| NULL | 涓  |
+------+--------+
2 rows in set (0.00 sec)

10、下面将SecureCRT工具改回utf8字符集,查询数据显示就正常了

root@localhost [wjqtest]>select * from charset_test;
+------+--------+
| id   | col1   |
+------+--------+
| NULL | 中国   |
| NULL | 中国   |
+------+--------+
2 rows in set (0.00 sec)

11、把客户端字符集修改为gbk,插入数据还是会报错

(注意:gbk往utf8中插入报错,utf8往gbk可以插入成功)
root@localhost [wjqtest]>set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)

root@localhost [wjqtest]>show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | gbk                              |
| character_set_connection | gbk                              |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

root@localhost [wjqtest]>insert into charset_test(col1) values('中国');
ERROR 1366 (HY000): Incorrect string value: '\xAD\xE5\x9B\xBD' for column 'col1' at row 1

12、把连接变量的字符串修改为uft8,可以成功插入数据,但是会有警告

root@localhost [wjqtest]>set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)

root@localhost [wjqtest]>insert into charset_test(col1) values('中国');
Query OK, 1 row affected, 1 warning (0.00 sec)

root@localhost [wjqtest]>show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1300 | Invalid gbk character string: '\xE4\xB8\xAD\xE5\x9B\xBD' |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

13、再次查看数据,发现刚刚虽然插入了,但是发现是乱码

root@localhost [wjqtest]>select hex(col1),col1 from charset_test;
+----------------+---------+
| hex(col1)      | col1    |
+----------------+---------+
| E4B8ADE59BBD   | 中国    |
| E4B8ADE59BBD   | 中国    |
| E6B6933FE6B597 | 涓?浗   |
+----------------+---------+
3 rows in set (0.00 sec)

实验案例二:

注意:当前我的机器的终端编码为UTF8,数据库的编码设定以下

root@localhost [wjqtest]>show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

首先,创建一张测试表charset_test1

root@localhost [wjqtest]>CREATE TABLE `charset_test1` (
    ->   `gbk` varchar(2) CHARACTER SET gbk DEFAULT NULL,
    ->   `utf8` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
    ->   `latin_utf8` varchar(6) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

注意:测试表的编码是latin1,而表中三个字段的编码各不同样,分别为gbk编码,utf8编码以及latin1编码.之所以这样创建,正是为了验证mysql字符集编码的转换过程

好了,重点来了,下面插入数据:

root@localhost [wjqtest]> insert into charset_test1 values("中文", "中文", "中文");   
ERROR 1366 (HY000): Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'latin_utf8' at row 1

root@localhost [wjqtest]> insert into charset_test1 values("北京", "北京", "北京");
ERROR 1366 (HY000): Incorrect string value: '\xE5\x8C\x97\xE4\xBA\xAC' for column 'latin_utf8' at row 1

发现在插入的时候出现上面的报错信息,为了方便后面的测试,修改session级别的sql_mode为非严格模式,这样就可以插入成功了

root@localhost [wjqtest]>set session sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)

root@localhost [wjqtest]>show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)

root@localhost [wjqtest]> insert into charset_test1 values("中文", "中文", "中文");
Query OK, 1 row affected, 1 warning (0.00 sec)

虽然是插入成功了,但是会有一个告警信息

root@localhost [wjqtest]>show warnings;
+---------+------+-------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                             |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'latin_utf8' at row 1 |
+---------+------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看测试表的内容,发现latin_utf8字段出现了乱码

root@localhost [wjqtest]>select * from charset_test1;
+--------+--------+------------+
| gbk    | utf8   | latin_utf8 |
+--------+--------+------------+
| 中文   | 中文   | ??         |
+--------+--------+------------+
1 row in set (0.00 sec)

下面就查看一下测试表中实际存储的内容:

root@localhost [wjqtest]>select hex(gbk),hex(utf8),hex(latin_utf8) from charset_test1;
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8)    | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F            |
+----------+--------------+-----------------+
1 row in set (0.00 sec)

能够发现直接select查看的时候latin_utf8字段乱码了,而通过hex函数查看发现原来latin_utf8字段存储的内容有问题。出现这个问题的解决办法就是编码转换过程出了错,依照之前的原理来分析下整个编码转换过程:

(1)首先我们mysql客户端发送插入语句insert into charset_test1 values(“中文”, “中文”, “中文”);,注意到”中文”的编码是跟我们的环境相关的,我这里是UTF8,因此”中文”字节表示为\xE4\xB8\xAD\xE6\x96\x87。

(2)server端接收到该语句会当作utf8编码,由于character_set_client=utf8,接下来是会进行第一步转换,即将语句从character_set_client转成character_set_connection的编码,由于我们这里这2个编码同样,实际就不会转换(此外,假设插入的数据前面有latin1或者utf8等introducer标记,也不会转换,由于introducer标记已经指明了字面值字符的编码)。

(3)接下来,数据要存储到数据库了,这个时候实际要插入的三个字段的编码都是原始编码\xE4\xB8\xAD\xE6\x96\x87,这个时候发生第二次编码转换,即由character_set_connection编码转换为数据表字段指定的编码.那么接下来,我们能够看到,由本身的UTF8编码与字段utf8同样,不须要进行转换。接下来看gbk字段,它的编码是gbk,这时会将原始编码s=”\xE4\xB8\xAD\xE6\x96\x87″依照utf8编码转换为GBK编码,即运行s.decode(‘utf8’).encode(‘gbk’),所以存储的是D6D0CEC4,也没有问题. 最后,看latin_utf8字段,相同须要转换编码,因为latin1表示不了utf8编码的范围,所以s.decode(‘utf8’).encode(‘latin1′)这个转换过程会出错,导致的结果就是latin_utf8字段存储的是??,即3F3F。

(4)最后就是select语句返回的结果分析,这是第三个须要转换编码的地方,即将字段从字段编码转换为character_set_results指定的编码.这也是我们上面为什么gbk字段和utf8字段都能正常显示中文的原因,因为在返回结果的时候,gbk字段会经过’\xD6\xD0\xCE\xC4’.decode(‘gbk’).encode(‘utf8’)返回,这样我们在utf8编码的mysqlclient可以正常显示gbk字段.同理,因为utf8字段本身与character_set_results,所以不会发生编码转换,原样返回\xE4\xB8\xAD\xE6\x96\x87,因此也是能正常显示的.而latin_utf8字段本身存储的就是3F3F,再经过编码转换,尽管utf8编码可以兼容latin1,可是本身的编码是3F3F,所以终于结果就是”??”。

依据上面的分析,为了表charset_test1中的latin_utf8字段可以正常的插入内容,在不设置character_set_client和character_set_connection的情况下,那么有个好的方法就是增加introducer,关于introducer可以參见mysql官方文档.那么我们的插入语句改为

root@localhost [wjqtest]> insert into charset_test1 values("中文", "中文", _latin1"中文"); 
Query OK, 1 row affected (0.01 sec)

由于指定了latin_utf8字段的introducer为_latin1,这样在第一次由character_set_client转换为character_set_connection的时候会忽略latin_utf8的转换,所以还是保持原来的utf8字符,接下来将其存入到latin1字段中,亦不会有问题,由于编码同样,不须要转换,所以latin_utf8字段实际存储的还是\xE4\xB8\xAD\xE6\x96\x87.这点能够通过以下的命令来验证:

root@localhost [wjqtest]>select hex(gbk),hex(utf8),hex(latin_utf8) from charset_test1;    
+----------+--------------+-----------------+
| hex(gbk) | hex(utf8)    | hex(latin_utf8) |
+----------+--------------+-----------------+
| D6D0CEC4 | E4B8ADE69687 | 3F3F            |
| D6D0CEC4 | E4B8ADE69687 | E4B8ADE69687    |
+----------+--------------+-----------------+
2 rows in set (0.00 sec)

那么我们假设直接select查询,还会是乱码吗?答案是会的;

由于如前所说,查询的时候会将字段编码转换为character_set_results编码的,显然gbk和utf8字段都没有问题,可是对于latin_utf8字段,其值会通过s.decode(‘latin1’).encode(‘gbk’),从而导致在查询的时候会乱码.

root@localhost [wjqtest]>select * from charset_test1;
+--------+--------+----------------+
| gbk    | utf8   | latin_utf8     |
+--------+--------+----------------+
| 中文   | 中文   | ??             |
| 中文   | 中文   | 中文         |
+--------+--------+----------------+
2 rows in set (0.00 sec)

那么解决办法,就是select语句中的字段前面加上binary标识,表示该字段查询结果不须要经过character_set_results的转换.例如以下:

root@localhost [wjqtest]>select gbk, utf8, binary latin_utf8 from charset_test1;        
+--------+--------+-------------------+
| gbk    | utf8   | binary latin_utf8 |
+--------+--------+-------------------+
| 中文   | 中文   | ??                |
| 中文   | 中文   | 中文              |
+--------+--------+-------------------+
2 rows in set (0.00 sec)

关于字符集Introducers详细可参考官方文档说明:

https://dev.mysql.com/doc/refman/5.7/en/charset-introducer.html

深入理解MySQL字符集及校对规则(二)


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:深入理解MySQL字符集及校对规则(二)
喜欢 (2)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址