mysql如何支持emoji/原文: mysql数据库中utf8与utf8mb4的区别:记一次sqlalchemy的InternalError

遇到的问题
使用sqlalchemy执行下面语句的时候包错了。

insert into datasource value('YnWZ8Q4928ruM4z63I+9IQ==','','盖乐世社区','S8','刚刚看到一个贴,特搞笑。这头说完下面就有人评论打脸了?    bixby不说最好,但怎么说也是在现有的语音技术基础上面提升一大步。(注意:现有的技术基础提升)真的搜不出来的话可能也许大概是输入的命令不对而已,可以换一句话试一试嘛。都说了bixby更人性化一点,肯定跟其他的不一样的嘛。当然,所有的语音都不说最好的,还有待提升。    好了,屁话不多说,回到我们图上这位小哥,估计是果粉,然后尝新鲜买了个s8。但还是偏向iPhone吧应该,然后一肚子火跑来撒气。但是!!我们看到评论这边,哈哈哈,秒打脸','2017-12-14','负向','http://www.galaxyclub.cn/thread-445992-4-61.html')

错误类型是InternalError,错误信息

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x82\\x09b...' for column 'content' at row 1") [SQL: "insert into datasource value('YnWZ8Q4928ruM4z63I+9IQ==','','盖乐世社区','S8','刚刚看到一个贴,特搞笑。这头说完下面就有人评论打脸了?\tbixby不说最好,但怎么说也是在现有的语音技术基础上面提升一大步。(注意:现有的技术基础提升)真的搜不出来的话可能也许大概是输入的命令不对而已,可以换一句话试一试嘛。都说了bixby更人性化一点,肯定跟其他的不一样的嘛。当然,所有的语音都不说最好的,还有待提升。\t好了,屁话不多说,回到我们图上这位小哥,估计是果粉,然后尝新鲜买了个s8。但还是偏向iPhone吧应该,然后一肚子火跑来撒气。但是!!我们看到评论这边,哈哈哈,秒打脸','2017-12-14','负向','http://www.galaxyclub.cn/thread-445992-4-61.html')"]

按照之前的经验,百分之八十问题是出在这个贱贱的表情上。复查下整个连接、建表中的数据。

  • 链路上的字符集设置

1 链接引擎

engine = create_engine("mysql+pymysql://root:root@localhost:3306/bi_bigeye?charset=utf8")

2 建表

create table if not exists datasource_type( siteName varchar(20) primary key, field varchar(10) ,scope varchar(10)) Default charset='utf8'

从整个链路上看,已经使用了utf8字符集了,理论上不应该出现emoji表情无法存储的情况了。

峰回路转

网上查了篇文档也是说吧字符集设置称为utf8mb4就可以了,为什么我这还报错。。。等等,为什么mb4是什么?

这一查才知道,原来mysql中utf8字符也藏了这么复杂的坑。

utf8这种是一种变长编码方法,也就是说到使用1到4个字节来表示一个字符。但是在mysql中的utf8却最多只能存储3个字节所表示的字符,也只能支持0x0000到0xFFFF的字符串,也就是Basic Muiltilingual Plane所规划的范围,而这个范围是不包含emoji的,所以在碰到这个贱贱表情是hold不住报错了。

解决问题

既然是使用字符串的问题,那么修改下相关的字符串编码即可。对于连接引擎来说,解决起来还是很简单的,将utf8替换成utf8mb4即可,对于数据库而言已经存储了不少内容,自然无法删掉重建。这时候就需要使用平时很少用到的ALTER语法。

ALTER TABLE `datasource` DEFAULT CHARACTER SET utf8mb4 ; # 更改表的编码,不影响各个字段的编码

alter table datasource convert to character set utf8mb4;  # 更改表的编码,删除各个字段的编码

通过show create table targetTable查看各个字段的编码,更改了之后问题也就消除了。

小结

在这个过程中,了解了在mysql环境下使用utf8编码还是不够的,而要使用utf8mb4;并且学习了使用alter 更改数据库的字符集的方法。

原文

http://zhouchen.tech/2018/03/15/mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%ADutf8%E4%B8%8Eutf8mb4%E7%9A%84%E5%8C%BA%E5%88%AB%EF%BC%9A%E8%AE%B0%E4%B8%80%E6%AC%A1sqlalchemy%E7%9A%84InternalError/