mysqlutf-8不⽀持⽣僻字_关于MySQLUTF8编码下⽣僻字符插
⼊失败假死问题的分析...
1、问题：mysql 遇到某些中⽂插⼊异常
最近有同学反馈了这样⼀个问题：
上述语句在脚本中 load ⼊库的时候会 hang 住，web 前端、命令⾏操作则要么抛出
Incorrect string value: '\xF0\xA1\x8B\xBE\' 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, sa
me 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 dePointAt(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字符的系统⽀持情况:
中文字符unicode查询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] 谈谈字符集与字符编码
[2] sed matching any ascii code/hex byte
[3] 10.1.10.6 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
[4] Mysql 中的 utf8
[5] Mysql中4字节UTF8字符集问题
[6] MySQL，UTF-8和emoji
[7] 关于MYSQL截断内容问题解决
[8] MySQL储存4字节字符
[9] 关于UTF-8编码的MySql抛出incorrect string value的问题
[10] 关于MYSQL截断内容问题解决
[11] mysql汉字16进制编码转换⽅法
[12] MySQL多字节字符集造成主从数据不⼀致问题
[13] 如何转义emoji表情，让它可以存⼊utf8的数据库？
[14] MySQL表字段字符集不同导致的索引失效问题

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。