上一篇呢，从理论上介绍了MYSQL字符集和校对规则是什么以及如何正确的使用字符集；关于第一部分的内容可参考：深入理解MySQL字符集及校对规则(一)
下面这部分呢，主要介绍MySQL字符编码转换原理以及字符集转化流程案例测试，通过实际的案例来分析字符集转化的原理；
一、MySQL字符编码转换原理
有这样一个疑问：若character_set_client为UTF8，而character_set_database为GBK，则会出现需要进行编码转换的情况，字符集转换的原理是什么？
解答如下：假设gbk字符集的字符串“你好”，需要转为utf8字符集存储，实际就是对于“你好”字符串中的每个汉字去utf8编码表里面查询对应的二进制，然后存储。
下面我们通过一幅图来看一下字符集转换的过程：
①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系统
实例案例一：
1、查看当前数据库实例的字符集
代码语言：javascript复制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
代码语言：javascript复制root@localhost [wjqtest]>create table charset_test(id int,col1 varchar(30)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)3、改变连接字符集为gbk
代码语言：javascript复制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，这里插入数据会出现报错
代码语言：javascript复制root@localhost [wjqtest]>insert into charset_test values(1,'中国');
ERROR 1366 (HY000): Incorrect string value: 'xADxE5x9BxBD' for column 'col1' at row 1注意：这里在测试的过程中，出现一些其他的情况，插入其他的汉字是没有问题（比如插入北京），那为什么会出现这种情况呢？是什么原因呢？
代码语言：javascript复制root@localhost [wjqtest]>insert into charset_test(col1) values('北京');
Query OK, 1 row affected (0.04 sec)那么我们来看一下为什么北京可以插入，而中国不可以插入呢？这就涉及到了一个字符转码的问题；我们来看一下转码的问题
得到中文‘北京’的gbk下的16进制编码
代码语言：javascript复制root@localhost [wjqtest]>SELECT hex(CONVERT( '北京' USING gbk ));
------------------------------------
| hex(CONVERT( '北京' USING gbk )) |
------------------------------------
| E58C97E4BAAC |
------------------------------------
1 row in set (0.00 sec)反推gbk的16进制编码，取回中文
代码语言：javascript复制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进制编码
代码语言：javascript复制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进制编码取回中文
代码语言：javascript复制root@localhost [wjqtest]>SELECT CONVERT( unhex('E98D96E6A4BEE590AB') USING utf8);
--------------------------------------------------
| CONVERT( unhex('E98D96E6A4BEE590AB') USING utf8) |
--------------------------------------------------
| 北京 |
--------------------------------------------------
1 row in set (0.00 sec)下面按照同样的方式编码取出‘中国’的GBK和utf8编码
代码语言：javascript复制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，就可插入成功了
代码语言：javascript复制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、查看数据，虽然插入成功了，但是发现返回的结果是乱码
代码语言：javascript复制root@localhost [wjqtest]>select * from charset_test;
---- ------
| id | col1 |
---- ------
| 1 | |
---- ------
1 row in set (0.00 sec)7、改变character_set_results（服务器反馈SQL语句结果使用的字符集）字符集为utf8后，再次查询数据显示就正常了
代码语言：javascript复制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字符集，然后继续插入数据
root@localhost [wjqtest]>insert into charset_test(col1) values('涓);
Query OK, 1 row affected (0.02 sec)9、查看数据发现还是显示乱码
代码语言：javascript复制root@localhost [wjqtest]>select * from charset_test;
------ --------
| id | col1 |
------ --------
| NULL | 涓 |
| NULL | 涓 |
------ --------
2 rows in set (0.00 sec)10、下面将SecureCRT工具改回utf8字符集,查询数据显示就正常了
代码语言：javascript复制root@localhost [wjqtest]>select * from charset_test;
------ --------
| id | col1 |
------ --------
| NULL | 中国 |
| NULL | 中国 |
------ --------
2 rows in set (0.00 sec)11、把客户端字符集修改为gbk，插入数据还是会报错
代码语言：javascript复制（注意: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: 'xADxE5x9BxBD' for column 'col1' at row 112、把连接变量的字符串修改为uft8，可以成功插入数据，但是会有警告
代码语言：javascript复制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: 'xE4xB8xADxE5x9BxBD' |
--------- ------ ----------------------------------------------------------
1 row in set (0.00 sec)13、再次查看数据，发现刚刚虽然插入了，但是发现是乱码
代码语言：javascript复制root@localhost [wjqtest]>select hex(col1),col1 from charset_test;
---------------- ---------
| hex(col1) | col1 |
---------------- ---------
| E4B8ADE59BBD | 中国 |
| E4B8ADE59BBD | 中国 |
| E6B6933FE6B597 | 涓?浗 |
---------------- ---------
3 rows in set (0.00 sec)实验案例二：
注意：当前我的机器的终端编码为UTF8,数据库的编码设定以下
代码语言：javascript复制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
代码语言：javascript复制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字符集编码的转换过程。
好了,重点来了,下面插入数据：
代码语言：javascript复制root@localhost [wjqtest]> insert into charset_test1 values("中文", "中文", "中文");
ERROR 1366 (HY000): Incorrect string value: 'xE4xB8xADxE6x96x87' for column 'latin_utf8' at row 1
root@localhost [wjqtest]> insert into charset_test1 values("北京", "北京", "北京");
ERROR 1366 (HY000): Incorrect string value: 'xE5x8Cx97xE4xBAxAC' for column 'latin_utf8' at row 1发现在插入的时候出现上面的报错信息，为了方便后面的测试，修改session级别的sql_mode为非严格模式，这样就可以插入成功了
代码语言：javascript复制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)虽然是插入成功了，但是会有一个告警信息
代码语言：javascript复制root@localhost [wjqtest]>show warnings;
--------- ------ -------------------------------------------------------------------------------------
| Level | Code | Message |
--------- ------ -------------------------------------------------------------------------------------
| Warning | 1366 | Incorrect string value: 'xE4xB8xADxE6x96x87' for column 'latin_utf8' at row 1 |
--------- ------ -------------------------------------------------------------------------------------
1 row in set (0.00 sec)查看测试表的内容，发现latin_utf8字段出现了乱码
代码语言：javascript复制root@localhost [wjqtest]>select * from charset_test1;
-------- -------- ------------
| gbk | utf8 | latin_utf8 |
-------- -------- ------------
| 中文 | 中文 | ?? |
-------- -------- ------------
1 row in set (0.00 sec)下面就查看一下测试表中实际存储的内容:
代码语言：javascript复制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,因此”中文”字节表示为xE4xB8xADxE6x96x87。 （2）server端接收到该语句会当作utf8编码,由于character_set_client=utf8,接下来是会进行第一步转换,即将语句从character_set_client转成character_set_connection的编码,由于我们这里这2个编码同样,实际就不会转换(此外,假设插入的数据前面有latin1或者utf8等introducer标记,也不会转换,由于introducer标记已经指明了字面值字符的编码)。 （3）接下来,数据要存储到数据库了,这个时候实际要插入的三个字段的编码都是原始编码xE4xB8xADxE6x96x87,这个时候发生第二次编码转换,即由character_set_connection编码转换为数据表字段指定的编码.那么接下来,我们能够看到,由本身的UTF8编码与字段utf8同样,不须要进行转换。接下来看gbk字段,它的编码是gbk,这时会将原始编码s=”xE4xB8xADxE6x96x87″依照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字段会经过’xD6xD0xCExC4’.decode(‘gbk’).encode(‘utf8’)返回,这样我们在utf8编码的mysqlclient可以正常显示gbk字段.同理,因为utf8字段本身与character_set_results,所以不会发生编码转换,原样返回xE4xB8xADxE6x96x87,因此也是能正常显示的.而latin_utf8字段本身存储的就是3F3F,再经过编码转换,尽管utf8编码可以兼容latin1,可是本身的编码是3F3F,所以终于结果就是”??”。
依据上面的分析,为了表charset_test1中的latin_utf8字段可以正常的插入内容,在不设置character_set_client和character_set_connection的情况下,那么有个好的方法就是增加introducer,关于introducer可以參见mysql官方文档.那么我们的插入语句改为
代码语言：javascript复制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字段实际存储的还是xE4xB8xADxE6x96x87.这点能够通过以下的命令来验证:
代码语言：javascript复制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’),从而导致在查询的时候会乱码.
代码语言：javascript复制root@localhost [wjqtest]>select * from charset_test1;
-------- -------- ----------------
| gbk | utf8 | latin_utf8 |
-------- -------- ----------------
| 中文 | 中文 | ?? |
| 中文 | 中文 | ä¸æ–‡ |
-------- -------- ----------------
2 rows in set (0.00 sec)那么解决办法,就是select语句中的字段前面加上binary标识,表示该字段查询结果不须要经过character_set_results的转换.例如以下:
代码语言：javascript复制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
