关于MYSQL保存emoji表情及openfire处理办法

在开发移动应用的时候，用户在输入了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(<generated>) 
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以上。楼主暂时还未测试。如果有问题，后续会继续说明！