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)

報(bào)錯(cuò)的原因主要是####

<code>
普通的字符串或者表情都是占位3個(gè)字節(jié)，所以u(píng)tf8足夠用了，但是移動(dòng)端的表情符號(hào)占位是4個(gè)字節(jié)，普通的utf8就不夠用了，為了應(yīng)對(duì)無線互聯(lián)網(wǎng)的機(jī)遇和挑戰(zhàn)、避免 emoji 表情符號(hào)帶來的問題、涉及無線相關(guān)的 MySQL 數(shù)據(jù)庫(kù)建議都提前采用 utf8mb4 字符集
</code>

但我們是可以修改的 ：

第一種保存emoji標(biāo)簽的方式的方式 (mysql版本要5.5+)####

<pre>
1. 修改表字段字符集為utf8mb4
{
ALTER TABLE UGC_REVIEW_CONTENT MODIFY CONTENT TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '內(nèi)容';
}
2. 再去修改表字符集utf8mb4
{
ALTER TABLE UGC_REVIEW_CONTENT CHARSET=utf8mb4 COMMENT='內(nèi)容';
}
3. 再去修改數(shù)據(jù)庫(kù)的字符集utf8mb4
{
cd /etc/ ./vim my.cnf
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
}
4. 重啟mysql服務(wù)
</pre>

第二種保存emoji標(biāo)簽的方式的方式 （通過java代碼在程序中處理,將emoji轉(zhuǎn)換成utf8字符集保存）

<pre>
/**
*將emoji標(biāo)簽轉(zhuǎn)換成utf8字符集保存進(jìn)數(shù)據(jù)庫(kù)
*@param str 需要轉(zhuǎn)換的字符串
*@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數(shù)據(jù)庫(kù)中保存的含轉(zhuǎn)換后emoji表情的字符串
 * @param str
 * 轉(zhuǎn)換后的字符串
 * @return 轉(zhuǎn)換前的字符串
 */
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();
}

</pre>