关于MYSQL Incorrect string value
问题描述
- 当用户发表有某些特殊字符的内容时,存入MYSQL数据库的内容会被截断;
- 数据库、程序文件等都是UTF-8编码
- 确认SQL语句正常
问题发生原因
- MYSQL中UTF-8编码最多只能是3个字节(5.5.3版本后,有 utf8mb4类型可支持4个字节的utf8);
- utf8是一种1-4个字节的可变字符编码(英文1个字符,汉字3个字符);
- 某些特殊字符(emoji表情符号等)是4字节的utf8编码;
- MYSQL在遇到超过最大字节范围的字符时,会忽略其后面的字符串,从而导致内容丢失。
解决思路
- 升级MYSQL到5.5.3版本以上,并将字段的编码设置为utf8mb4类型;
- 通过程序,将字符串中4字节的utf8字符替换或者删除即可。
剔除UTF8字符串中4字节的字符方法
1 2 3 | function removeByte4( $str ){ return preg_replace( '/[\xF0-\xF7].../s' , '' , $str ); } |
性能
台式机,4.6W字符,0.006s,对程序性能影响基本可以忽略。
扩展研究(其他的一些相关知识)
问题底层原因
该问题是mysql本身的一个bug,可参考"Incorrect string value" error (1366) when inserting special characters“
UTF8 4字节各系统的支持情况
- windows xp: xp系统不支持4字节utf8字符, 浏览器用占位符显示;
- windows 7: 支持4字节utf8字符;
- mac os x: 支持4字节utf8字符;
- iPhone/iPad: 支持4字节utf8字符;
如何支持手机上的表情
此问题可参考mysql utf8mb4与emoji表情。
- 将特殊的4字节字符用相应的图片表情代替;
- 替换的时候,注意不要堵塞用户的处理流程,当文本内容较长时,这是一个很耗性能的处理,建议先忽略4字节字符,然后将内容存到文件或者其他地方,后续使用脚本对这些内容进行替换处理等操作。;
- 新浪微博等有做相应处理;
- 特殊符合以及对应表情可参考[网站](http://www.charbase.com/);
四种不同剔除utf8字符串中4字节字符的性能比对
测试机:普通pc; 测试文本:4.6W字节的中英文、特殊字符等混合;
极快,推荐使用:0.006s
1 2 3 | function removeByte4($str){ return preg_replace( '/[\xF0-\xF7].../s' , '' , $str); } |
较慢,参考用:0.2s
1 2 3 | function removeByte4_2($str){ return preg_replace( '/[\x{10000}-\x{10FFFF}]/u' , '' , $str); } |
慢,但方便对特殊字符做不同替换:3s
1 2 3 4 5 6 7 8 9 10 | function removeByte4_1( $str ){ //移除utf-8编码中4字节及以上的字符 mb_internal_encoding( "UTF-8" ); $len = mb_strlen( $str ); $res = '' ; for ( $i =0; $i < $len ; $i ++){ $t = mb_substr( $str , $i ,1); $res .= strlen ( $t )<=3? $t : '' ; } return $res ; } |
极慢,不具使用价值,仅参考:10.9s
1 2 3 4 5 6 7 8 9 10 | function remove_4_byte( $string ) { $char_array = preg_split( '/(?<!^)(?!$)/u' , $string ); $len = count ( $char_array ); for ( $x =0; $x < $len ; $x ++) { if ( strlen ( $char_array [ $x ])>3) { $char_array [ $x ] = "" ; } } return implode( $char_array , "" ); } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· PPT革命!DeepSeek+Kimi=N小时工作5分钟完成?
· What?废柴, 还在本地部署DeepSeek吗?Are you kidding?
· DeepSeek企业级部署实战指南:从服务器选型到Dify私有化落地
· 程序员转型AI:行业分析
· 重磅发布!DeepSeek 微调秘籍揭秘,一键解锁升级版全家桶,AI 玩家必备神器!