表情提交失败的解决方案
来源:互联网 发布:vmware virtualbox知乎 编辑:程序博客网 时间:2024/06/05 06:13
当客户端在调用用户评论接口,提交了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 1org.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](http://312461613.blog.51cto.com/965442/1718999) 来修改的.修改方式如下:
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;"/>
0 0
- 表情提交失败的解决方案
- 阿里RDS中插入emoji 表情插入失败的解决方案
- github提交代码失败解决方案
- PHP 表单提交disabled数据失败解决方案
- Android-->键盘表情切换的终极解决方案
- textarea 禁止输入表情的解决方案
- DHCP 失败的解决方案
- 代码提交的时候可以插入表情了-GitHub表情的使用
- MySQL解决插入emoji表情失败的问题
- 手机端添加emo表情到数据库失败的问题解决
- Android表情解决方案
- UGUI表情系统解决方案
- emoji表情解决方案
- CoCreateInstance失败的一种解决方案
- MSSQL2008安装失败的解决方案
- 创建组件失败的解决方案
- OpenCV Cmake失败的解决方案
- SVN cleanup失败的解决方案
- spring boot errorPageFilter 冲突问题。
- Android中Loader及LoaderManager的使用(附源码下载)
- 自定义可左滑删除,且自动归位的listview
- Jenkins(四)---Jenkins添加密钥对
- 几种shell执行模式的区别
- 表情提交失败的解决方案
- 数字拼接
- 关于iOS的后台下载和断点续传,说一说自己的理解
- log4j 每天生成一个日志文件
- Unity3d之Shader编程:子着色器、通道与标签的写法 & 纹理混合
- 自定义圆形头像CircleImageView的使用和源码分析
- Linked List Cycle
- 随笔录 之 spring 自学杂记(七) --Transaction(TX) (二)
- OpenStack Neutron网络状态详解