在开发移动应用的时候,用户在输入了emoji表情后,系统异常,我是做服务器端的,发现错误如下:
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94' for column 'name' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
网上有解决办法。就是把数据库对应字段编码改为utf8mb4,这个编码可以看做是utf8升级版,完全向下兼容utf8,所以基本不用担心有什么乱码问题。
但是在openfire也做相同处理以支持发emoji表情的时候,发现只改字段编码还是不行。各种和之前的表进行对比,发现没什么不同;思前想后,觉得问题不是出在数据库上,应该是连接驱动有问题!果然,openfire用的驱动包是mysql.jar,没看是什么版本;我把之前好使的mysql驱动包拿来,果然可以了!
总结:
让mysql支持保存emoji表情,需进行如下两步。
1、数据库对应字段编码改为utf8mb4;
2、mysql连接驱动至少在5.1.16以上,我用的是mysql-connector-java-5.1.16-bin.jar
3、你的连库串上不要指定编码格式,如 jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf-8 需要把?useUnicode=true&characterEncoding=utf-8去掉
==========================================
纠正一个问题!如果你的mysql版本在5.6以上的话,用mysql-connector-java-5.1.16-bin.jar在后台管理查看用户的时候是会报错的!错误信息如下:
2014.05.27 11:08:54 org.jivesoftware.openfire.user.DefaultUserProvider - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=1000' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=1000' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1595)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2268)
at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at com.mysql.jdbc.Statement$$EnhancerByProxool$$a23ade6c.executeQuery()
at org.jivesoftware.openfire.user.DefaultUserProvider.getUsernames(DefaultUserProvider.java:277)
at org.jivesoftware.openfire.user.DefaultUserProvider.getUsers(DefaultUserProvider.java:300)
at org.jivesoftware.openfire.user.UserManager.getUsers(UserManager.java:282)
at org.jivesoftware.openfire.admin.user_002dsummary_jsp._jspService(user_002dsummary_jsp.java:232)
这个跟mysql有关,只能是升级mysql驱动版本,据说是要换到mysql-connector-java-5.1.22-bin.jar以上。楼主暂时还未测试。如果有问题,后续会继续说明!