mysql 字符串有有乱码 转码_MySQL字符集转换引发插入乱码问题

问题背景

在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.01sec)

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.00sec) #报错

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.00sec)

jinyizhou@localhost : test 05:58:55>select * fromabc;+------+-----------------------+

| 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.00sec)

jinyizhou@localhost : test 06:04:12>select * fromabc;+------+-----------------------+

| 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.00sec)

jinyizhou@localhost : test 06:14:49>select * fromabc;+------+-------------------------------------------+

| id | c |

+------+-------------------------------------------+

| 1 | 我们 |

| 2 | cardName:鏍″洯缃 |

| 1 | 校园网 |

| 2 | cardName:校园网;offlineCardType:campus |

+------+-------------------------------------------+

到此问题找到了,原来是char()函数的问题。那就在

CHAR() returns a binary string. To produce a string in a given character set, usethe optional USING clause:

#char()返回的是一个二进制字符串,可选择使用USING语句产生一个给出的字符集中的字符串:

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65USING 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.00sec)

jinyizhou@localhost : test 06:22:24>select * fromabc;+------+-------------------------------------------+

| 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.00sec)

jinyizhou@localhost : test 09:38:50>select * fromabc;+------+-------------------------------------------+

| 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.00sec)

jinyizhou@localhost : test 09:39:38>select * fromabc;+------+-------------------------------------------+

| id | c |

+------+-------------------------------------------+

| 1 | 我们 |

| 2 | cardName:鏍″洯缃 |

| 1 | 校园网 |

| 2 | cardName:校园网;offlineCardType:campus |

| 2 | cardName:校园网;offlineCardType:campus |

| 2 | ; |

| 2 | 鎴 |

+------+-------------------------------------------+

这里就看出来问题所在了,最终问题定位在concat+char上面。

上面已经知道char()在官方的说明了,继续在

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 inthis 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.00sec)

jinyizhou@localhost : test 10:19:13>select * fromabc;+------+-------------------------------------------+

| 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。总的一句话就是保证客户端,服务端和表的字符集一致,尽量避免不一致引发的问题。参考:

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:数字20 设计师:CSDN官方博客 返回首页

打赏作者

weixin_39736150

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值