文档章节

关于 MySQL UTF8 编码下生僻字符插入失败/假死问题的分析

大数据之路
 大数据之路
发布于 2014/11/12 02:24
字数 2315
阅读 16394
收藏 49

1、问题:mysql 遇到某些中文插入异常

最近有同学反馈了这样一个问题:

上述语句在脚本中 load 入库的时候会 hang 住,web 前端、命令行操作则要么抛出 

Incorrect string value: '\xF0\xA1\x8B\xBE\xE5\xA2...' for column 'name',

要么存入MYSQL数据库的内容会被截断或者乱码,而换做其它的中文则一切正常。

嗯,看起来有点奇怪哈,按理说 utf8 编码是覆盖了所有中文的,不应该出现上述问题。

2、原因:此 utf8 非彼 utf8

那我们先来看看插入异常的中文和正常的中文有啥区别:

可以看到上面插入异常的文字占了 4 个字节,而我们插入正常的则只占了 3 个字节。但是 utf8 字符编码不就是可变长,支持 1-4 字节的么?会和这个有关?

嗯,看看官方文档就知道了:

10.1.10.6 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters:

  • For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length.

  • For a supplementary character, utf8 cannot store the character at all, while utf8mb4 requires four bytes to store it. Since utf8 cannot store the character at all, you do not have any supplementary characters in utf8 columns and you need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.

  • utf8mb4 is a superset of utf8.

由官方文档可知,mysql 支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。也就是说,任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。

最初的 UTF-8 格式使用一至六个字节,最大能编码 31 位字符。最新的 UTF-8 规范只使用一到四个字节,最大能编码21位,正好能够表示所有的 17个 Unicode 平面。
utf8 是 Mysql 中的一种字符集,只支持最长三个字节的 UTF-8字符,也就是 Unicode 中的基本多文本平面。
Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢?我想了一下,可能是因为 Mysql 刚开始开发那会,Unicode 还没有辅助平面这一说呢。那时候,Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数,对于 CHAR 数据类型来说,需要为字符串保留足够的长。当使用 utf8 字符集时,需要保留的长度就是 utf8 最长字符长度乘以字符串长度,所以这里理所当然的限制了 utf8 最大长度为 3,比如 CHAR(100)  Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持,我想一个是为了向后兼容性的考虑,还有就是基本多文种平面之外的字符确实很少用到。
要在 Mysql 中保存 4 字节长度的 UTF-8 字符,需要使用 utf8mb4 字符集,但只有 5.5.3 版本以后的才支持(查看版本: select version();)。我觉得,为了获取更好的兼容性,应该总是使用 utf8mb4 而非 utf8.  对于 CHAR 类型数据,utf8mb4 会多消耗一些空间,根据 Mysql 官方建议,使用 VARCHAR  替代 CHAR。

3、解决方案

知道原因了,当然得谈谈有哪些方案可以解决开头的问题。

3.1 升级 mysql 版本,并将utf8字符集升级到utf8mb4

升级你的 mysql 到 5.5.3 之后即可,查看当前环境版本: 

select version();

MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8bp4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。
所以好的技术就是,采用对当前而言最好的解决方案,然后再逐步迭代满足新的需求。

3.1.1 直接修改表结构

-- 方法一,如果遇到某个列字符集转换完后字节数超限了,会提示错误
--1、修改数据库字符集,或修改表默认字符集 alter table j1 default character set utf8mb4;
ALTER DATABASE test CHARACTER SET = utf8mb4;
--2、随后再修改所有字符型列的字符集 alter table j1 modify name varchar(20) character set utf8mb4 not null default '';
ALTER TABLE `test` CHANGE COLUMN `name` `name` varchar(12) CHARACTER SET utf8mb4;

-- 方法二,如果遇到某个列字符集转换完后字节数超限了,则会将这个列数据类型转换成可以容纳更大长度的类型,比如从 TEXT 转成 LONGTEXT 等。
--直接转换表字符集
alter table test convert to character set utf8mb4;

--方法三
--如果不放心,可以用mysqldump逻辑备份方式,用utf8mb4字符集把数据备份出来,新建表,恢复回去,应该也可以的。

 

3.1.2 修改数据库默认配置

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

P.S. 如果你使用的是java语言,需要将jdbc驱动包升级到 mysql-connector-java-5.1.14.jar。

3.2 强行过滤掉生僻字符串

从业务和技术的角度综合考虑,可以做个折中,将生僻字符串提前过滤掉,因为这类字符串本来就使用的很少,即使存进数据库了,展示、查询的时候也会多少有其它的问题,不如直接过滤掉,mysql 不支持四字节的 utf8 一方面可能是历史包袱,另一方面估计也是为了省空间。

3.2.1 shell 过滤

比如,咱们可以直接先用 sed、awk、python、perl 处理下要 load 入库的脚本,将四字节的生僻字全过滤再入库:


 

3.2.2 java 中的过滤操作

判断MySql支持Unicode字符的方法,伪码为:

for i=1->n
int c=str.codePointAt(i);
if (c<0x0000||c>0xffff) {
    return false;
}

稍作修改即可。

3.3 避开客户端乱码:二进制存储与查询

为避免web页面或者终端本身不支持utf8四字节,可以采用二进制的方式来操作

create table t1(name varchar(20) charset utf8mb4);
insert into t1 values(0xF0A09080);

set charset binary;
select * from t1;

 

4、应用、系统对 utf-8 四字节字符的支持

最后顺便总结下4字节utf8字符的系统支持情况:

  • windows xp: 我所测试的xp系统都不支持4字节utf8字符, 浏览器用占位符显示

  • windows 7: 支持4字节utf8字符

  • mac os x: 支持4字节utf8字符

  • iPhone/iPad: 支持4字节utf8字符

  • 许多的数据库软件或者shell终端都不支持4字节utf8字符, 比如 Navicat、SecureCRT

以 php 场景为例说明:

  • php连接会话设置编码utf8, mysql后端字段为text character set utf8: 写入内容从4字节utf8字符处被截断

  • php连接会话设置编码utf8mb4, mysql后端字段为text character set utf8: 内容可以完整写入, 但是4字节utf8字符被替换为问号"?"

  • php连接会话设置编码utf8mb4, mysql后端字段为text character set utf8mb4: 完整支持4字节utf8字符

从平台支持上来看, 随着winxp的逐步淘汰, 对4字节utf8字符的支持还是有必要的.
官方手册对utf8mb4字符的说明中指出, utf8mb4是utf8的超集, 因此可放心升级.

5、最后的问题

看到这里,不知道细心的你有没有发现,本文的代码为毛都是图呢?要知道我自己写文章很少把代码搞成图的,那是因为。。。

哇哈哈,真是哪壶不开提哪壶啊。。。。。。。。。

6、Refer:

[1] 谈谈字符集与字符编码

http://my.oschina.net/leejun2005/blog/232732#OSC_h3_4

[2] sed matching any ascii code/hex byte

http://forums.gentoo.org/viewtopic-t-770576-start-0.html

[3] 10.1.10.6 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

[4] Mysql 中的 utf8

http://blog.haipo.me/?p=1149

[5] Mysql中4字节UTF8字符集问题

http://bbs.chinaunix.net/thread-3766853-1-1.html

[6] MySQL,UTF-8和emoji

http://shadyxu.com/mysql_utf8.html

[7] 关于MYSQL截断内容问题解决

http://www.momotime.me/2014/10/mysql-utf8mb4/

[8] MySQL储存4字节字符

http://www.web-tinker.com/article/20643.html

[9] 关于UTF-8编码的MySql抛出incorrect string value的问题

http://blog.csdn.net/tannasu/article/details/8064021

[10] 关于MYSQL截断内容问题解决

http://daiweiyang.com/mysql_data_truncated/

[11] mysql汉字16进制编码转换方法

http://blogread.cn/it/article/3016?f=wb

[12] MySQL多字节字符集造成主从数据不一致问题  

http://backend.blog.163.com/blog/static/20229412620133274030845/

[13] 如何转义emoji表情,让它可以存入utf8的数据库?

http://segmentfault.com/q/1010000003711491

[14] MySQL表字段字符集不同导致的索引失效问题

http://bit.ly/2eO2rDp

© 著作权归作者所有

大数据之路
粉丝 1605
博文 515
码字总数 331782
作品 0
武汉
架构师
私信 提问
加载中

评论(13)

L
Lionet
顺带一提,NaviCat 11.2.7是支持的mb4的
L
Lionet
非常感谢
Syion
Syion
非常感谢,解我问题57
U
UtillYou
楼主,我参照你的3.1方案为什么还是不行啊?
我先运行了
ALTER DATABASE anj CHARACTER SET = utf8mb4;
接着运行
insert into user(`name`,`user_type`,`user_degree`,current_grade,total_grade,can_post_task,can_do_task,deleted,create_time) values('Hello Vicky','si',1,0,0,0,0,0,now())
还是不行.
insert 语句中有一个unicode 字符,但是我发不上了,因为oschina也不支持
J
JAVA小学徒
学习了
大数据之路
大数据之路 博主

引用来自“jelly_oy”的评论

许多的数据库软件或者shell终端都不支持4字节utf8字符, 比如 Navicat、SecureCRT

请问下,有支持utf8mb4的数据库软件吗?
目前我也没有特意考证过究竟市面上有没有支持的。
jelly_oy
jelly_oy
许多的数据库软件或者shell终端都不支持4字节utf8字符, 比如 Navicat、SecureCRT

请问下,有支持utf8mb4的数据库软件吗?
随想风暴
别墅?
f
fangzz
全面又准确,相当好!已参照3.1方案执行,顺利解决问题。感谢!!
大数据之路
大数据之路 博主
@红薯 大哥顺便看看最后的这个问题~3
mysql中Incorrect string value乱码问题解决方案

你是否遇到过类似以下错误? java.sql.SQLException: Incorrect string value: 'xF0x9Fx92x9C' for column 'content' at row 1. 产生这种异常的原因在于,mysql中的utf8编码最多会用3个字节存......

拉风小野驴
2016/03/17
12.3K
1
mysql/Java服务端对emoji或者生僻字的支持

最近开发的iOS项目因为需要用户文本的存储,自然就遇到了emoji等表情符号如何被mysql DB支持的问题。困扰了数日,在就要绝望放弃的边缘,终于完成了转换和迁移。在此特别分析和整理,方便更多...

文文1
2016/07/13
299
0
MySQL字符编码解决乱码问题

这几天查找了很多关于mysql对中文字符编码的处理,读了各种零散的文章,最后做了全面的总结,现和大家分享: 字符编码 MySQL字符编码 GBK、GB2312、UTF8区别:http://kongjian.baidu.com/wang...

牧北
2011/11/26
748
0
MySQL中如何处理中文字符问题

最有力的解决办法链接: http://www.busfly.net/post/58.html -------------------------------------------------------------------------- MySQL的老手们: 本人用MySQL时不能够处理中文,......

小梅菜鸟
2012/05/15
231
0
数据库~dotnetcore连接Mysql插入中文失败

到目录 在dotnetcore里,连接mysql数据,插入中文时出现无法识别,并提示插入失败的情况,分析后得知它是编码问题,即数据库编码问题,你的中文在数据表里无法被识别! 解决方法(一) 进行m...

张占岭
2018/09/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

正则表达式匹配

请实现一个函数用来匹配包括 '.' 和 '*' 的正则表达式。模式中的字符 '.' 表示任意一个字符,而 '*' 表示它前面的字符可以出现任意次(包含 0 次)。 在本题中,匹配是指字符串的所有字符匹配...

Garphy
今天
7
0
Laravel 5.1的多路由文件的配置

默认的路由配置文件只有一个, \app\Http\routes.php。 在同一个文件中写路由容易起冲突,文件会越来越大,就需要定义多个路由文件。 找到加载\app\Http\routes.php的文件, 打开\app\Provid...

mdoo
今天
5
0
Hibernate 5 开始使用指南前言

同时在面向对象软件和关系型数据库进行工作,可能会非常复杂和费时。数据在对象和数据库之间可能会不一致,然后导致开发成本会非常高。 Hibernate 是一个针对 Java 环境的对象关系映射(Obj...

honeymoose
今天
6
0
聊聊nacos ServiceManager的UpdatedServiceProcessor

序 本文主要研究一下nacos ServiceManager的UpdatedServiceProcessor ServiceManager.init nacos-1.1.3/naming/src/main/java/com/alibaba/nacos/naming/core/ServiceManager.java @Compone......

go4it
今天
7
0
正则表达式的使用(QQ格式的判断与空格的切割)

//正则表达式的使用 public static void main(String[] args) throws IOException, ClassNotFoundException { //test1("123456"); test2("-1 99 kk"); } /** * ......

zhengzhixiang
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部