总结:被MySQL UTF8编码坑的惨痛教训...
云栖号资讯:【点击查看更多行业资讯】
在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来!
最近遇到几个项目被MySQL的utf8编码坑,想起之前编码问题被坑的惨痛教训,记录一下,警示自己。
曾几何时,每次建库都选utf8,觉得自己比那些用乱七八糟编码的人不知道酷到哪里去了。直到好多年前的某次课程设计做项目的时候,愉快的建了个用户表:
CREATE TABLE `test_user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
然后愉快的新增用户:INSERT INTO test_user(name) VALUES("我是😁"),接着愉快的反思人生:
Incorrect string value: '\xF0\x9F\x98\x81' for column 'name' at row 1
我是谁?我来自哪里?我在干嘛?难道是我代码里面的字符集用错了?不对啊我所有地方都用的utf8啊……
MySQL的UTF8编码是什么?
首先来看官方文档:
The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. The utf8mb4 character set uses a maximum of four bytes per character supports supplementary 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, whereas utf8mb4 requires four bytes to store it. Because utf8 cannot store the character at all, you have no supplementary characters in utf8 columns and need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.
我们再看看维基百科对UTF8编码的解释:
UTF-8 is a variable width character encoding capable of encoding all 1,112,064 valid code points in Unicode using one to four 8-bit bytes.
可以看出,MySQL中的utf8实质上不是标准的UTF8。MySQL中,utf8对每个字符最多使用三个字节来表示,所以一些emoji甚至是一些生僻汉字就存不下来了,比如“𡋾”。
MySQL一直不承认这是一个bug,他们在2010年发布了“utf8mb4”字符集来绕过这个问题,在MySQL中,utf8mb4才应该是标准的utf8编码,并且官方很鸡贼的偷偷在最新的文档中加上了,算是认识到错误了吧:更多的关于设计,原理知识点的问题,可以在互联网架构师后台回复2T获取。
utf8 is an alias for the utf8mb3 character set.
The utf8mb3 character set will be replaced by utf8mb4 in some future MySQL version. Although utf8 is currently an alias for utf8mb3, at that point utf8 will become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8.
MySQL UTF8问题简史
MySQL从4.1版本开始支持utf8,即2003年,但是现在的utf8标准(RFC 3629)是在其后发布的。MySQL在2002年3月28日的4.1预览版中使用了旧版的utf8标准(RFC 2279),该标准最多支持每个字符6个字节,同年9月MySQL调整其utf8字符集最多支持3字节,而这个调整可能只是为了优化空间(05年前推荐使用CHAR类字段,而一个utf8的CHAR将会占用6字节长度)和时间性能(05年前在MySQL中使用CHAR字段会有更优的速度)。嗯可以在GitHub中看到大家对这个坑的吐槽:
但是这个字符编码发布出来,就不能轻易的修改,因为如果已经有用户开始使用了,就需要这些用户重新构建其数据库。
怎么补救呢?在上面最新文档中可以看出,他们将当前的utf8作为utf8mb3的别名,并且在将来的某一天会把utf8重新作为utf8mb4别名,这样来解决这个多年的巨坑。
啥是UTF8
略
utf8mb4_unicode_ci 和 utf8mb4_general_ci
字符除了存储,还需要排序或者比较,这个操作与编码字符集有关,称为collation,与utf8mb4对应的是utf8mb4_unicode_ci 和 utf8mb4_general_ci这两个collation。
准确性
utf8mb4_unicode_ci 是基于标准Unicode来进行排序比较的,能保持在各个语言之间的精确排序;
utf8mb4_general_ci 并不基于Unicode排序规则,因此在某些特殊语言或者字符上的排序结果可能不是所期望的。
性能
utf8mb4_general_ci 在比较和排序时更快,因为其实现了一些性能更好的操作,但是在现代服务器上,这种性能提升几乎可以忽略不计。
utf8mb4_unicode_ci 使用Unicode的规则进行排序和比较,其排序规则为了处理一些特殊字符,实现更加复杂。
现在基本没有理由继续使用utf8mb4_general_ci了,因为其带来的性能差异很小,远不如更好的数据设计,比如使用索引等等。
MySQL用错编码怎么救
- 备份,不然崩了就只有删库跑路了;
- 升级MySQL服务端到5.3.3及以上版本,以支持utf8md4;
- 将数据库、表、列的字符编码、collation改为utf8md4:
# For each database: ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; # For each table: ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # For each column: ALTER TABLE table_name CHANGE column_name column_name VARCHAR(length) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4.检查列和索引键的最大长度;
5.修改连接、客户端、服务端的字符集;
6.修复和优化所有的表,以免出现一些莫名其妙的错误,可以使用如下的方式:
# For each table REPAIR TABLE table_name; OPTIMIZE TABLE table_name;
或者是使用mysqlcheck
工具:
$ mysqlcheck -u root -p --auto-repair --optimize --all-databases
**其他坑
**
MySQL表字段字符集不同导致的索引失效问题
参考
https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html
https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4
【云栖号在线课堂】每天都有产品技术专家分享!
课程地址:https://yqh.aliyun.com/zhibo立即加入社群,与专家面对面,及时了解课程最新动态!
【云栖号在线课堂 社群】https://c.tb.cn/F3.Z8gvnK
原文发布时间:2020-05-11
本文作者:互联网架构师
本文来自:“互联网架构师 微信公众号”,了解相关信息可以关注“互联网架构师”
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
为什么要放弃 JSP ?
云栖号资讯:【点击查看更多行业资讯】在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来! 前言 以前的项目大多数都是Java程序猿又当爹又当妈,既搞前,又搞后端。 随着时代的发展,渐渐的许多大中小公司开始把前后端的界限分的越来越明确,前端工程师只负责前端的事情,后端工程师只管后端的事情。正所谓术业有专攻,一个人如果什么都会,那么每一样都很难达到精通。 大中型公司需要专业人才,小公司需要全才,但是对于个人职业发展来说,我建议是分开。你要是这辈子就吃 Java 这碗饭,就不要去研究什么 css,js 等等。 把你的精力专注在 Java,JVM 原理,Spring原理,mysql锁,事务,多线程,大并发,分布式架构,微服务,以及相关的项目管理等等,这样你的核心竞争力才会越来越高,正所谓你往生活中投入什么,生活就会反馈给你什么。 曾几何时 我们的Java Web项目都是使用了若干后台框架进行开发,Spring、Spring MVC、MyBatis、Hibernate等等。 而且大多数项目在Java后端都是分了三层,控制层、业务层、持久层。控制层负责接收参数,调用相关业务层,封装数据,...
- 下一篇
完美网络体验背后的技术力量,《阿里云互联网多媒体存储解决方案蓝皮书》震撼上线!
前言 中国互联网从1994年正式接入国际网络至今,以非凡的力量改变了世界,重新塑造了商业、政治、社会,甚至改变了全球几十亿的人的生活。中国互联网络信息中心(CNNIC)第45次《中国互联网络发展状况统计报告》中显示:截至2020年3月28,我国网民规模为9.04亿,互联网普及率达64.5%。依托于环境,启蒙于困境,多媒体技术在互联网坚实的基础之上,逐步进入快速发展的阶段,阿里云多媒体数据存储解决方案应运而生。《阿里云互联网多媒体存储解决方案蓝皮书》(以下简称“蓝皮书”)的发布,为多媒体行业的数字化进程提供有力保障。 (文末扫码进群,一键下载蓝皮书) 新型行业引领需求方向 蓝皮书中提到,5G时代到来,移动网络提速,运营商流量资费不断下调等多个因素推动,多媒体行业一直处于快速发展的状态,用户基数与数据量飞升,与此同时,2020年在线直播、在线教育、视频会议等领域迎来高速发展,根据艾媒咨询的数据显示,2016—2019年中国在线直播用户规模呈上升趋势,预测2020年中国在线直播用户规模将达到5.24亿人;在刚刚过去的2019年中国在线直播用户规模则为5.01亿人,技术发展进程中也不断遇到机遇...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS关闭SELinux安全模块
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- Hadoop3单机部署,实现最简伪集群
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
- CentOS6,CentOS7官方镜像安装Oracle11G
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- SpringBoot2全家桶,快速入门学习开发网站教程
- CentOS8编译安装MySQL8.0.19
- CentOS6,7,8上安装Nginx,支持https2.0的开启