上一篇呢，从理论上介绍了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、查看当前数据库实例的字符集
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字符集，然后继续插入数据
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字符集及校对规则(二)
