emoji表情无法存储到mysql解决方案
程序员文章站
2022-04-21 10:12:05
...
一、出现的问题
当我们需要把输入法自带的Emoji表情存储到MySQL数据库中,例如将
An ????awesome ????string ????with a few ????emojis!
这段字符串保存到MySQL数据库时,会出现下面的错误:
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x80aw...' for column 'content' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199)
这是由于MySQL的utf8编码的一个字符最多3个字节,但是一个emoji表情为4个字节,所以utf8不支持存储emoji表情。
二、解决办法
使用第三方插件(emoji-java,项目主页:https://github.com/vdurmont/emoji-java)将表情符号转为别名的形式。
1、引入maven依赖
<dependency>
<groupId>com.vdurmont</groupId>
<artifactId>emoji-java</artifactId>
<version>4.0.0</version>
</dependency>
2、将表情符号转为别名:
String str = "An ????awesome ????string with a few ????emojis!";
String result = EmojiParser.parseToAliases(str);
System.out.println(result);
// Prints:
// "An :grinning:awesome :smiley:string with a few :wink:emojis!"
3、将别名转为表情符号:
String str = "An :grinning:awesome :smiley:string 😄with a few :wink:emojis!";
String result = EmojiParser.parseToUnicode(str);
System.out.println(result);
// Prints:
// "An ????awesome ????string ????with a few ????emojis!"
4、使用mybatis的TypeHandler
public class EmojiTypeHandler extends BaseTypeHandler<String> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, EmojiParser.parseToAliases(parameter));
}
@Override
public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
String content = rs.getString(columnName);
return rs.wasNull() ? null : EmojiParser.parseToUnicode(content);
}
@Override
public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String content = rs.getString(columnIndex);
return rs.wasNull() ? null : EmojiParser.parseToUnicode(content);
}
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String content = cs.getString(columnIndex);
return cs.wasNull() ? null : EmojiParser.parseToUnicode(content);
}
}
5、使用EmojiTypeHandler
insert:
#{content,typeHandler=com.haferd.autoclub.post.util.EmojiTypeHandler,jdbcType=VARCHAR}
select:
<result column="content" property="content" jdbcType="VARCHAR" typeHandler="com.haferd.autoclub.post.util.EmojiTypeHandler"/>