一个往mysql中插入字符串被截断的坑

手里有个爬虫收到报警没成功,然后去查数据库发现状态码都是对的,最终原因是mysql的一个MEDIUMTEXT字段存的一个json字符串化的数据被截断了(直接导致这个数据解析失败)。

一开始我还以为是我用的GUI管理工具一次性没法取回这么大数据,于是换了另一个GUI工具还是不行,然后换命令行操作发现还是截断的,这就明白了的确是字符串被截断了。我们知道MEDIUMTEXT字段最大可以存16M字节的数据,截断的字符串才几十KB肯定不会是字段限制原因,而原始的json字符串因为太大又没有放进log里导致很难重现。

于是折腾了许久以后发现原因是,mysql的InnoDB引擎的utf8字符集只能最多支持3个字节的utf8字符,如果是4个以上的就会插入错误导致截断,mysql针对这个问题方案是utf8_mb4字符集(为了向后兼容没有修改utf8字符集的默认行为)。

举个例子:

$result_json = "aaaqué \xF0\x9D\x92\xB3 tal";
$this->model->result_json = $result_json;
$this->model->created_at=time();
$this->model->save(false);

插入数据库后发现被截断,数据库里只有aaaqué这个字符串。解决方案无非就是把mysql的utf8字符集不支持的字符替换掉就ok了,见这个问题里得票最高的答案,当然了这是php版本,java/python语言遇到这种情况也应该做对应的处理。

完成这个替换后可以看到问题得以解决。爬虫爬到的数据字符集可能出现各种情况,所以这个问题还是需要注意的。其实一般插入后遇到截断的话,sql的warning会记录截断信息的,通过这一点也提醒我插入以后最好检查一下mysql的warning是否为空。

update:
大家以后mysql注意charset最好选:utf8mb4_unicode_ci
mysql字符集选择问题:http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

已有的utf8转变成utfmb4细节(我们现在有的表还是utf8的):http://mp.weixin.qq.com/s?__biz=MzAwMDU2ODU3MA==&mid=2247484084&idx=1&sn=e3740e1087dc73ffcdc4b56bfeaaaa6d&chksm=9ae7bf21ad9036370e8174995ff73775a0ff8c8a51b9995fc8675a994a768a136d187e2aa76d

一个往mysql中插入字符串被截断的坑》有8个想法

  1. $result_json = “aaaqué xF0x9Dx92xB3 tal”;//http://stackoverflow.com/questions/8491431/how-to-replace-remove-4-byte-characters-from-a-utf-8-string-in-php echo preg_replace(‘/[x{10000}-x{10FFFF}]/u’, “xEFxBFxBD”, $result_json) ;//为什么输出空

发表评论

电子邮件地址不会被公开。 必填项已用*标注