欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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 &#128516;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"/>
相关标签: Mybatis Emoji