Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

报错的原因主要是####

普通的字符串或者表情都是占位3个字节，所以utf8足够用了，但是移动端的表情符号占位是4个字节，普通的utf8就不够用了，为了应对无线互联网的机遇和挑战、避免 emoji 表情符号带来的问题、涉及无线相关的 MySQL 数据库建议都提前采用 utf8mb4 字符集

但我们是可以修改的 ：

第一种保存emoji标签的方式的方式 (mysql版本要5.5+)####

1. 修改表字段字符集为utf8mb4

{

ALTER TABLE UGC_REVIEW_CONTENT MODIFY CONTENT TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '内容';

}

2. 再去修改表字符集utf8mb4

{

ALTER TABLE UGC_REVIEW_CONTENT CHARSET=utf8mb4 COMMENT='内容';

}

3. 再去修改数据库的字符集utf8mb4

{

cd /etc/ ./vim my.cnf

init-connect='SET NAMES utf8mb4'

character-set-server=utf8mb4

}

4. 重启mysql服务

第二种保存emoji标签的方式的方式 (通过java代码在程序中处理,将emoji转换成utf8字符集保存)

/**

*将emoji标签转换成utf8字符集保存进数据库

*@param str 需要转换的字符串

*@return

*/

public static String emojiConvert(String str) {

String patternString = "([\x{10000}-\x{10ffff}\ud800-\udfff])";

Pattern pattern = Pattern.compile(patternString);

Matcher matcher = pattern.matcher(str);

StringBuffer sb = new StringBuffer();

while(matcher.find()) {

try {

matcher.appendReplacement(sb,"[[" + URLEncoder.encode(matcher.group(1),"UTF-8") + "]]");

} catch(UnsupportedEncodingException e) {

logger.error("emojiConvert error"+ e.getMessage());

return str;

}

}

matcher.appendTail(sb);

logger.debug("emojiConvert " + str + " to " + sb.toString() + ", len：" + sb.length());

return sb.toString();

}

/**

* @Description 还原utf8数据库中保存的含转换后emoji表情的字符串

* @param str

* 转换后的字符串

* @return 转换前的字符串

*/

public static String emojiRecovery(String str) {

String patternString = "\\[\\[(.*?)\\]\\]";

Pattern pattern = Pattern.compile(patternString);

Matcher matcher = pattern.matcher(str);

StringBuffer sb = new StringBuffer();

while(matcher.find()) {

try {

matcher.appendReplacement(sb,

URLDecoder.decode(matcher.group(1), "UTF-8"));

} catch(UnsupportedEncodingException e) {

logger.error("emojiRecovery error"+ e.getMessage());

return "";

}

}

matcher.appendTail(sb);

logger.debug("emojiRecovery " + str + " to " + sb.toString());

return sb.toString();

}