表情提交失败的解决方案

最新推荐文章于 2023-04-15 18:45:00 发布

ChangeYoung

最新推荐文章于 2023-04-15 18:45:00 发布

阅读量1.6k

点赞数

分类专栏：编程文章标签： emoji 表情 mysql java

本文链接：https://blog.csdn.net/sinat_28762477/article/details/53887306

版权

编程专栏收录该内容

14 篇文章 0 订阅

订阅专栏

当客户端在调用用户评论接口,提交了emoji表情的时候,会报如下的错误:

### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row 1
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row 1
org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row 1
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO tbl_ugc_comment(    ugc_comment_id,    del_flag,    create_by,    create_date,    update_by,    update_date,    ugc_id,    comment_user_id,    comment_user_name,    content,    reply_user_id,    reply_user_name,    comment_time   ) VALUES (    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?   )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row 1
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' 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:74)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
        at com.sun.proxy.$Proxy37.insert(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:253)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
        at com.sun.proxy.$Proxy84.insert(Unknown Source)
        at com.thinkgem.jeesite.spider.api.ugc.service.AppUgcCommentService.saveUgcComment(AppUgcCommentService.java:81)
        at com.thinkgem.jeesite.spider.api.ugc.service.AppUgcCommentService$$FastClassBySpringCGLIB$$1c470838.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
        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:281)
        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.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at com.thinkgem.jeesite.spider.api.ugc.service.AppUgcCommentService$$EnhancerBySpringCGLIB$$f757220a.saveUgcComment(<generated>)
        at com.thinkgem.jeesite.spider.api.ugc.service.AppUgcCommentService$$FastClassBySpringCGLIB$$1c470838.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
        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:281)
        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.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
        at com.thinkgem.jeesite.spider.api.ugc.service.AppUgcCommentService$$EnhancerBySpringCGLIB$$8db3b078.saveUgcComment(<generated>)
        at com.thinkgem.jeesite.spider.api.ugc.web.AppUgcController.addUgcComment(AppUgcController.java:244)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
        at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
        at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
        at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
        at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
        at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
        at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:436)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        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\x98\x8A' for column 'content' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1307)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
        at sun.reflect.GeneratedMethodAccessor134.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
        at com.sun.proxy.$Proxy206.execute(Unknown Source)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
        at sun.reflect.GeneratedMethodAccessor313.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
        at com.sun.proxy.$Proxy204.update(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)
        at sun.reflect.GeneratedMethodAccessor335.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)
        ... 79 more

百度了一下,发现有很多人遇到同样的问题,并且有很多的解决方案.主要原因是现在我们的数据库编码一般都是UTF-8,UTF-8编码有可能是两个、三个、四个字节。Emoji表情是4个字节，而Mysql的utf8编码最多3个字节，所以数据插不进去。
从 MySQL 5.5.3 开始，MySQL 支持一种 utf8mb4 的字符集，这个字符集能够支持 4 字节的 UTF8 编码的字符。 utf8mb4 字符集能够完美地向下兼容 utf8 字符串。在数据存储方面，当一个普通中文字符存入数据库时仍然占用 3 个字节，在存入一个Unified Emoji 表情的时候，它会自动占用 4个字节。所以在输入输出时都不会存在乱码的问题了。所以解决方案就是：将Mysql的编码从utf8转换成utf8mb4。网上修改编码的方式有很多,我是参照这篇文章让mysql支持emoji 来修改的.修改方式如下:


1 解决方案：将Mysql的编码从utf8转换成utf8mb4。
   需要 >= MySQL 5.5.3版本、从库也必须是5.5的了、低版本不支持这个字符集、复制报错

2 my.cnf 文件添加
  [mysqld]
  character-set-server = utf8mb4
  collation-server = utf8mb4_unicode_ci
  init_connect='SET NAMES utf8mb4'

3修改需要添加库 表 字段的字符集
    修改数据库字符集：
    ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

    修改表的字符集：
    ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    修改字段的字符集：
    ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE     utf8mb4_unicode_ci;

    如果只是某个字段需要 只需要修改那个字段的字符集就可以了

4 如果修改以上都不行请查询sql 

    mysql> show variables like '%sql_mode%'; 
    +---------------+--------------------------------------------+
    | Variable_name | Value                                      |
    +---------------+--------------------------------------------+
    | sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
    +---------------+--------------------------------------------+
    1 row in set (0.00 sec)
    如果是以上结果恭喜你是存储不了的
    去设置这个sql_mod 模式（注意这里修改看之后要退出控制台要不然还是看不到效果的，而
    且这个配置写my.cnf 重启服务器是不生效的，如果要问为什么请去mysql顾问群）
    mysql> set global sql_mode = 'NO_ENGINE_SUBSTITUTION'；
    mysql> show variables like '%sql_mode%';
    +---------------+------------------------+
    | Variable_name | Value                  |
    +---------------+------------------------+
    | sql_mode      | NO_ENGINE_SUBSTITUTION |
    +---------------+------------------------+
    1 row in set (0.00 sec)
6  做完这些就完成了，让MySQL支持Emoji表情

本文出自 “信不信由你” 博客，请务必保留此出处http://312461613.blog.51cto.com/965442/1718999

之前的项目中,按照这样的操作都没有问题,但这个项目却怎么修改都不起作用.然后,又各种排查,发现我们现在的项目修改了数据库连接池.现在用的是DruidDataSource,而在它的属性中缺少了这行配置,只要加上就行了

<property name="connectionInitSqls" value="set names utf8mb4;"/>

ChangeYoung

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录