MySQL 字符集

问题复现

连接字符集:UTF8

表结构:

  1. CREATE TABLE `ggg` (
  2. `id` int(11) DEFAULT NULL,
  3. `c` varchar(100) DEFAULT NULL
  4. ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
  5. root@test 06:13:48>insert into ggg values(1,concat('cardName:校园网',char(59),'offlineCardType:campus'));
  6. Query OK, 1 row affected, 1 warning (2.51 sec)
  7. root@test 06:14:36>show warnings\G
  8. *************************** 1. row ***************************
  9. Level: Warning
  10. Code: 1366
  11. Message: Incorrect string value: '\x91;offl...' for column 'c' at row 1

查看结果

  1. root@test 06:16:06>select * from ggg where id=1;
  2. *************************** 1. row ***************************
  3. id: 1
  4. c: cardName:鏍″洯缃

问题分析

从报错的结果来看,感觉是字符集转换引起的问题,而且由于连接串的字符集是UTF8,表的字符集是GBK,更容易引起怀疑。但是,即使是字符集转换,也不应该导致插入报错,因为语句中的中文字符“校园网”都是普通汉字,UTF8->GBK不应该存在问题。
那再回过头来看看insert语句,唯一特殊的是使用了concatchar两个函数。会不会跟这两个函数有关系?char(59)实际是字符“;”,为了验证想法,做了两个实验:

char(59)替换成’;
  1. insert into ggg values(1,concat('cardName:校园网',';','offlineCardType:campus'));

设置连接串字符集为GBK

  1. insert into ggg values(1,concat('cardName:校园网',char(59),'offlineCardType:campus'));

果然,两种情况执行结果都是OK的,查询结果如下:

  1. root@test 09:22:32>select * from ggg\G
  2. *************************** 1. row ***************************
  3. id: 1
  4. c: cardName:鏍″洯缃
  5. *************************** 2. row ***************************
  6. id: 1
  7. c: cardName:校园网;offlineCardType:campus
  8. *************************** 3. row ***************************
  9. id: 1
  10. c: cardName:校园网;offlineCardType:campus

跟踪了下源代码,找到了原因。char()函数返回的是一个binary类型字符串,在进行concat时,会导致’cardName:校园网’字符串到binary的转换。转换前,mysql将字符串‘cardName:校园网’看作是9个英文字符和3个汉字字符;转换后,mysql将其看作是18个字节的二进制串,其中,UTF8字符集的三个汉字“校园网”占了9个字节。由于目标表字符集是GBK,因此在入库时,还会发生一次binary到GBK的转码,“校园网”的二级制编码是E6A0A1 E59BAD E58DA1,在转码过程中,由于GBK字符集只包含一个字节(编码值<128)和二个字节的字符(汉字和特殊字符),“校园网”的二进制串会按照两个字节拆分E6A0 A1E5 9BAD E58D A1,前面四个变为“鏍″洯缃”,解析到A1时,由于A1既不是单字节字符,又不能与后面的字节组成一个合法的GBK字符,导致转换出错。
现在就很好解释为啥改变语句后,两种情况都OK了。第一种情况,将char(59)直接替换成‘;’,由于不涉及UF8到binary的转换,只有utf8到gbk转码的过程,这个转换是OK的,不会出现乱码;第二种情况,将连接串的字符集设置为GBK,那么会涉及GBK到binary的转换,然后再从binary转换到GBK,由于整个转换过程并没有二进制数据丢失,所以也是OK的。

问题产生的两个关键点

  1. 连接字符集与表字符集不匹配
  2. 使用了char函数

    解决办法

  3. char函数提供了using语法来实现返回特定字符集的字符串,比如:char(59 using utf8)

  4. 保证连接字符集与表字符集一致。