Mysql 支持emoji表情存储
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
1 |
ALTER TABLE UGC_REVIEW_CONTENT MODIFY CONTENT TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '内容'; |
2. 再去修改表字符集utf8mb4
|
1 |
ALTER TABLE UGC_REVIEW_CONTENT CHARSET=utf8mb4 COMMENT='内容'; |
3. 再去修改数据库的字符集utf8mb4
|
1 2 3 |
cd /etc/ vim my.cnf |
|
1 2 3 |
[mysqld] init-connect='SET NAMES utf8mb4' character-set-server=utf8mb4 |
4. 重启mysql服务
|
1 |
service mysqld restart |
5.修改连接字符串
|
1 |
jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf-8 |
后面字符集参数去掉，改为
|
1 |
jdbc:mysql://localhost:3306/testdb |
第二种保存emoji标签的方式的方式 （通过java代码在程序中处理,将emoji转换成utf8字符集保存）
/**
*将emoji标签转换成utf8字符集保存进数据库
*@param str 需要转换的字符串
*@return
*/
public static String emojiConvert(String str) {
String patternString = “([\x{10000}-\x{10ffff}\ud800-\udfff])”;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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(); } |
参考资料：
https://www.jianshu.com/p/b0f5eb5d7cc3
https://my.oschina.net/xiejunbo/blog/513741
