首页   >   代码编程

MySQL保存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服务;

QQ群: 686430774  /  718410762

站长Q: 1347384268

如果文章有帮到你,可以考虑请博主喝杯咖啡!

分享到:

欢迎分享本文,转载请注明出处!

作者:不忘初心

发布时间:2019-04-15

永久地址:https://www.jiweichengzhu.com/article/2d5d05d5f46344409a3171428bcf9e2d

评论