原创

关于Emoji表情存入数据库报错的原因调查Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8E\xB1' for

最近在做关于社交项目时,有一个发红包功能,其中涉及到红包描述,一直报错,后来看日志,才发现竟是因为输入了Emoji表情的原因,由于我的mysql数据库是utf8字符集,而且Emoji表情等特殊符号要占四个字节,所以导致数据库不能正常存入。

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8E\xB1' for column 'redbag_describe' at row 1

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]

at sun.reflect.GeneratedMethodAccessor95.invoke(Unknown Source) ~[na:na]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]

at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.23.jar!/:na]

at com.sun.proxy.$Proxy138.execute(Unknown Source) ~[na:na]

at sun.reflect.GeneratedMethodAccessor95.invoke(Unknown Source) ~[na:na]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.4.6.jar!/:3.4.6]

at com.sun.proxy.$Proxy139.execute(Unknown Source) ~[na:na]

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) ~[mybatis-3.4.6.jar!/:3.4.6]

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.4.6.jar!/:3.4.6]

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.4.6.jar!/:3.4.6]

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.4.6.jar!/:3.4.6]

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.4.6.jar!/:3.4.6]

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ~[mybatis-3.4.6.jar!/:3.4.6]

at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) ~[mybatis-3.4.6.jar!/:3.4.6]

at sun.reflect.GeneratedMethodAccessor261.invoke(Unknown Source) ~[na:na]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_161]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_161]

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.2.jar!/:1.3.2]

... 60 common frames omitted

找了很多方法,都没有从根源上解决问题,很是头疼。最后还是发现github上有个很牛逼的轻量级开源工具叫emoji-java,通过这个工具类基本上解决了我大部分的问题,同时配合前端限制Emoji表情输入,才算把问题解决。当然,还有一种方法,就是更改数据库的默认编码utf-8 为utf-8mb4,这个也可以很好的解决问题。
解决方法请参考:Java 存储mysql数据库时如何进行Emoji表情转换和处理

正文到此结束
该篇文章的评论功能已被站长关闭
本文目录