您现在的位置是：网站首页 > 代码编程 > 数据库数据库

【原】MySQL保存emoji表情失败的原因和解决方案

不忘初心 不忘初心 2019-04-15 围观() 评论() 点赞() 【数据库】

简介：在使用MySQL的时候，字符集选择上，我从来都是默认utf8，没有做过多的关注，在设计博客数据库的时候，没有考虑到emoji表情，近日在编写文章时，在参考资料中

在使用MySQL的时候，字符集选择上，我从来都是默认utf8，没有做过多的关注，在设计博客数据库的时候，没有考虑到emoji表情，近日在编写文章时，在参考资料中拷贝信息时，带过来的文字有emoji表情，导致保存的时候报错了：

### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xAD\xF0\x9F...' for column 'text' at row 1
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x90\xAD\xF0\x9F...' for column 'text' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xAD\xF0\x9F...' for column 'text' at row 1
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
	at com.sun.proxy.$Proxy21.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
	at com.sun.proxy.$Proxy24.insert(Unknown Source)
	at com.wolffy.jwcz.service.impl.ArticleServiceImpl.insert(ArticleServiceImpl.java:119)
	at com.wolffy.jwcz.service.impl.ArticleServiceImpl.insert(ArticleServiceImpl.java:53)
	at com.wolffy.jwcz.service.impl.ArticleServiceImpl$FastClassBySpringCGLIB$9dca5f66.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
	at com.wolffy.jwcz.aop.BaseAspect.doAround(BaseAspect.java:39)
	at sun.reflect.GeneratedMethodAccessor267.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
	at com.wolffy.jwcz.service.impl.ArticleServiceImpl$EnhancerBySpringCGLIB$6fa81ca5.insert(<generated>)
	at com.wolffy.jwcz.controller.ArticleController$1.doExecute(ArticleController.java:102)
	at com.wolffy.core.common.ServiceExecutor.execute(ServiceExecutor.java:84)
	at com.wolffy.jwcz.controller.ArticleController.saveOrUpdate(ArticleController.java:106)
	at com.wolffy.jwcz.controller.ArticleController$FastClassBySpringCGLIB$71f5b32e.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:52)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
	at com.wolffy.jwcz.controller.ArticleController$EnhancerBySpringCGLIB$acc4c9ff.saveOrUpdate(<generated>)
	at sun.reflect.GeneratedMethodAccessor341.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:220)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:134)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xAD\xF0\x9F...' for column 'text' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
	at sun.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy97.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:157)
	at sun.reflect.GeneratedMethodAccessor313.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
	... 86 more

由于数据库的字符集是utf8，所以建表的时候也没有特殊指定，表中字段的字符集和排序规则也都是utf8

MySQL保存emoji表情失败的原因和解决方案

因为utf8字符集只支持3个字节，而emoji是4个字节，所以兼容不了，可能在设计utf8字符集的时候，官方并没有考虑到后面会有emoji这个玩意儿。

原因算是找到了，要解决此问题，就需要将字符集更改为utf8mb4。

首先，将数据库的字符集先更改为utf8mb4：

MySQL保存emoji表情失败的原因和解决方案

再将表的字符集更改为utf8mb4：

ALTER TABLE article CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

改完之后，可以看到表中的字段也都已经是utf8mb4的字符集了

MySQL保存emoji表情失败的原因和解决方案

本以为问题就这么轻松的解决了，结果却发现不行，插入还是失败，继续查找资料，看到有大佬说不要在jdbc中配置characterEncoding=utf8，我尝试了一下，结果却更糟糕了，所有的varchar类型的字段都乱码了

MySQL保存emoji表情失败的原因和解决方案

继续查阅资料，看到有大佬提到修改my.cnf，在配置中追加字符集的配置：character-set-server=utf8mb4

[mysqld]

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

character-set-server=utf8mb4

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

innodb_file_per_table=1

lower_case_table_names=1

#skip-grant-tables=1

# Disabling symbolic-links is recommended to prevent assorted security risks
# symbolic-links=0

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

# log-error=/var/log/mariadb/mariadb.log
# pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
# !includedir /etc/my.cnf.d

修改完配置之后，需要重启MySQL服务让配置生效

[root@iZbp16sksdu04rk7tj72xdZ mysql]# service mysql restart
Shutting down MySQL.....                                   [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@iZbp16sksdu04rk7tj72xdZ mysql]# 
[root@iZbp16sksdu04rk7tj72xdZ mysql]# 
[root@iZbp16sksdu04rk7tj72xdZ mysql]# 
[root@iZbp16sksdu04rk7tj72xdZ mysql]# 
[root@iZbp16sksdu04rk7tj72xdZ mysql]#

这一次，问题得到解决！！！

解决方法总结：

1、更改数据库字符集为utf8mb4;

2、更改相应的表字符集为utf8mb4；

3、在my.cnf中增加字符集配置：character-set-server=utf8mb4；

4、重启mysql服务；

mysqlemojiutf8utf8mb4

看完文章，有任何疑问，请加入群聊一起交流！！！

很赞哦！ ()

上一篇：MySQL数据库主键冲突的三种解决方案

下一篇：MySQL字符集utf8、utf8mb4、utf8mb4_general_ci、utf8mb4_unicode_ci的区别

相关文章

文章评论

    • 请先说点什么
    人参与，条评论

请使用电脑浏览器访问本页面，使用手机浏览器访问本页面会导致下载文件异常！！！

雨落无影

关注上方公众号，回复关键字【下载】获取下载码

用完即删，每次下载需重新获取下载码

若出现下载不了的情况，请及时联系站长进行解决

热门阅读

IntelliJ IDEA 2020.1版本最新激活方法（向下兼容）IntelliJ IDEA 2020.1版本最新激活方法（向下兼容）

站长推荐

写给关注支持我的朋友和攻击我网站的人（封面针对后者）写给关注支持我的朋友和攻击我网站的人（封面针对后者）

标签云

猜你喜欢

站点信息

  • 网站程序：spring + freemarker
  • 主题模板：《今夕何夕》
  • 文章统计：篇文章
  • 标签管理：标签云
  • 微信公众号：扫描二维码，关注我们