MySQL字符集转换引发插入乱码问题
根据http://www.cnblogs.com/cchust/p/4601536.html进行验证测试
问题背景
在mysql上面执行一条普通的insert语句，结果报错:
Incorrect string value: '\x91;offl...' for column 'c' at row 1
重现：
1）连接MySQL字符集是UTF8
mysql --default-character-set=utf8 test
2）表结构
CREATE TABLE `abc` ( `id` int(11) DEFAULT NULL, `c` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk
3）SQL
jinyizhou@localhost : test 05:58:25>insert into abc values(1,'我们'); Query OK, 1 row affected (0.01 sec) jinyizhou@localhost : test 05:58:32>insert into abc values(2,concat('cardName:校园网',char(59),'offlineCardType:campus')); Query OK, 1 row affected, 1 warning (0.00 sec) #报错 jinyizhou@localhost : test 05:58:51>show warnings; +---------+------+----------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\x91;offl...' for column 'c' at row 1 | +---------+------+----------------------------------------------------------------+ 1 row in set (0.00 sec) jinyizhou@localhost : test 05:58:55>select * from abc; +------+-----------------------+ | id | c | +------+-----------------------+ | 1 | 我们 | | 2 | cardName:鏍″洯缃 | +------+-----------------------+ 2 rows in set (0.01 sec)
分析：
上面看到第2个插入乱码了，八九不离十是字符集的问题了，看warnings就知道。这里有疑问，为什么第一条插入正常，第二条就报错？2个插入的字符集都是一样的（客户端字符集），要是字符集出错，第一个也会报错。即使表的字符集是gbk，连接字符集是utf8，大家都知道utf8字符集范围要大于gbk，难道是”校内网“这3个中文超出了gbk范围？测试下：
jinyizhou@localhost : test 05:59:02>insert into abc values(1,'校园网'); Query OK, 1 row affected (0.00 sec) jinyizhou@localhost : test 06:04:12>select * from abc; +------+-----------------------+ | id | c | +------+-----------------------+ | 1 | 我们 | | 2 | cardName:鏍″洯缃 | | 1 | 校园网 | +------+-----------------------+ 3 rows in set (0.00 sec)
插入成功，上面说明和中文没有关系，其实MySQL内部会进行转换，所以问题不在这里。既然问题在第2个insert，那直接查看下是否乱码：
jinyizhou@localhost : test 06:04:14>select concat('cardName:校园网',char(59),'offlineCardType:campus'); +----------------------------------------------------------------+ | concat('cardName:校园网',char(59),'offlineCardType:campus') | +----------------------------------------------------------------+ | cardName:校园网;offlineCardType:campus | +----------------------------------------------------------------+
显示没有问题，没有乱码，但是写入却出现乱码。这里注意到了一个函数：char(56)，我们把这个函数转成字符串看看：
jinyizhou@localhost : test 06:10:09>insert into abc values(2,concat('cardName:校园网',';','offlineCardType:campus')); Query OK, 1 row affected (0.00 sec) jinyizhou@localhost : test 06:14:49>select * from abc; +------+-------------------------------------------+ | id | c | +------+-------------------------------------------+ | 1 | 我们 | | 2 | cardName:鏍″洯缃 | | 1 | 校园网 | | 2 | cardName:校园网;offlineCardType:campus | +------+-------------------------------------------+
到此问题找到了，原来是char()函数的问题。那就在手册里看看char函数的相关知识：
CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause:
#char()返回的是一个二进制字符串，可选择使用USING语句产生一个给出的字符集中的字符串：
mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8)); +---------------------+--------------------------------+ | CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) | +---------------------+--------------------------------+ | binary | utf8 | +---------------------+--------------------------------+
按照上面的提醒，使用using utf8 给出一个字符串，即把二进制转换成了字符串。指定字符集后再次执行：
jinyizhou@localhost : test 06:14:52>insert into abc values(2,concat('cardName:校园网',char(59 using utf8),'offlineCardType:campus')); Query OK, 1 row affected (0.00 sec) jinyizhou@localhost : test 06:22:24>select * from abc; +------+-------------------------------------------+ | id | c | +------+-------------------------------------------+ | 1 | 我们 | | 2 | cardName:鏍″洯缃 | | 1 | 校园网 | | 2 | cardName:校园网;offlineCardType:campus | | 2 | cardName:校园网;offlineCardType:campus | +------+-------------------------------------------+
看到成功插入，问题现在很明显了，就是出在char()函数上面？再推敲下：
jinyizhou@localhost : test 09:38:17>insert into abc values(2,char(59)); Query OK, 1 row affected (0.00 sec) jinyizhou@localhost : test 09:38:50>select * from abc; +------+-------------------------------------------+ | id | c | +------+-------------------------------------------+ | 1 | 我们 | | 2 | cardName:鏍″洯缃 | | 1 | 校园网 | | 2 | cardName:校园网;offlineCardType:campus | | 2 | cardName:校园网;offlineCardType:campus | | 2 | ; | +------+-------------------------------------------+
问题要是出在char()上面的话，上面的应该报错，那为什么没问题呢，再看看和concat一起用：
jinyizhou@localhost : test 09:38:53>insert into abc values(2,concat('我',char(59))); Query OK, 1 row affected, 1 warning (0.00 sec) jinyizhou@localhost : test 09:39:38>select * from abc; +------+-------------------------------------------+ | id | c | +------+-------------------------------------------+ | 1 | 我们 | | 2 | cardName:鏍″洯缃 | | 1 | 校园网 | | 2 | cardName:校园网;offlineCardType:campus | | 2 | cardName:校园网;offlineCardType:campus | | 2 | ; | | 2 | 鎴 | +------+-------------------------------------------+
这里就看出来问题所在了，最终问题定位在concat+char上面。
上面已经知道char()在官方的说明了，继续在手册里看看concat函数的相关知识：
If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent string form. to avoid that and produce a nonbinary string, you can use an explicit type cast, as in this example: SELECT CONCAT(CAST(int_col AS CHAR), char_col); 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)
用charset查看类型：
jinyizhou@localhost : test 10:58:21>select charset(concat('cardName:校园网',char(56),'offlineCardType:campus')); +------------------------------------------------------------------------+ | charset(concat('cardName:校园网',char(56),'offlineCardType:campus')) | +------------------------------------------------------------------------+ | binary | +------------------------------------------------------------------------+ 1 row in set (0.00 sec)
按照上面的提醒，使用cast给出一个字符串，即把二进制转换成了字符串：
jinyizhou@localhost : test 10:18:43>insert into abc values(2,concat('cardName:校园网',cast(char(59) as char),'offlineCardType:campus')); Query OK, 1 row affected (0.00 sec) jinyizhou@localhost : test 10:19:13>select * from abc; +------+-------------------------------------------+ | id | c | +------+-------------------------------------------+ | 1 | 我们 | | 2 | cardName:鏍″洯缃 | | 1 | 校园网 | | 2 | cardName:校园网;offlineCardType:campus | | 2 | cardName:校园网;offlineCardType:campus | | 2 | ; | | 2 | 鎴 | | 2 | cardName:校园网;offlineCardType:campus | +------+-------------------------------------------+
看到成功插入。说到这里大家都知道原因了：
char()函数返回的是一个二进制，concat()函数里只要有一个参数是二进制，则结果就是一个二进制。由于把一个二进制的值转成十六进制再写入到表，所以在插入的时候就会出现乱码，为什么不报错呢？因为转换出来的编码在字符集里找不到，虽然不报错，但会变成乱码。乱码的原因是：char()函数返回的是一个binary类型字符串，在进行concat时，会导致'cardName:校园网'字符串到binary的转换，整个结果返回是一个binary。UTF8字符集的三个汉字“校园网”占了9个字节。由于目标表字符集是GBK，因此在入库时，还会发生一次binary到GBK的转码，此时由于utf8和gbk字符集的范围不一样导致转换出错。要是表的字符集是utf8，则不会报错。
解决：
1）直接把char函数用其转换好的结果表示：如用";"来代替char(56)。
2）直接转化成字符串：char(59 using utf8) 来替换char(56)。
3）直接转化成字符串：cast(char(59) as char) 来代替char(56)。
4）直接把表的字符集改成utf8。
总结：
MySQL乱码的问题有很多情况，大致的原理可以看 http://www.cnblogs.com/zhoujinyi/p/4618887.html。总的一句话就是保证客户端，服务端和表的字符集一致，尽量避免不一致引发的问题。
参考：
http://www.cnblogs.com/cchust/p/4601536.html
http://www.cnblogs.com/zhoujinyi/p/4618887.html
~~~~~~~~~~~~~~~
万物之中,希望至美
~~~~~~~~~~~~~~~
