emoji表情怎么录入mysql_Emoji表情符号录入MySQL数据库报错的解决方式

前言：手机app应用评论的时候，恢复表情符号。提示失败。​1，查看tomcat后台日志，核心报错信息例如以下：Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1at 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)... 46 more[root@mysqlvm2 ~]# mysql -root -pmysql> use test;Database changedmysql> show tables;Ignoring query to other databasemysql> Ctrl-C -- exit!Aborted忘记输入-u參数了，冲洗你输入。OK，例如以下所看到的：[root@mysqlvm2 ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.12-log Source distributionCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>mysql> use test;Database changedmysql> show tables;+------------------------+| Tables_in_test         |+------------------------+| c                      || lubin_test             || test                   || tt                     |+------------------------+10 rows in set (0.00 sec)这样的输入Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...'的问题，多数都是字符集的。曾经从latain改成gbk。从gbk改成utf8。而我的CONTENT字段已经utf8了。那么比utf8很多其它的就仅仅有utf8mb4了，所以去改动表字段的字符集吧。

mysql>2，先去改动表字段字符集为utf8mb4：ALTER TABLE UGC_REVIEW_CONTENT MODIFY `CONTENT` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '评论内容';运行完后，再在手机端的app測试，依旧报相同的错误。3。再去改动表字符集utf8mb4：ALTER TABLE UGC_REVIEW_CONTENT  CHARSET=utf8mb4 COMMENT='晒单/推荐的评论内容';运行完后，再在手机端的app測试，依旧报相同的错误。4，再去改动数据库的字符集utf8mb4:vim my.cnfinit-connect='SET NAMES utf8mb4'character-set-server=utf8mb4重新启动mysql数据库[root@mysqlvm4 ~]# service mysql restartShutting down MySQL....                                    [确定]Starting MySQL.............................................[确定]..[root@mysqlvm4 ~]#查看db的字符集mysql> show variables like '%char%';+--------------------------+----------------------------------+| Variable_name            | Value                            |+--------------------------+----------------------------------+| character_set_client     | utf8mb4                          || character_set_connection | utf8mb4                          || character_set_database   | utf8mb4                          || character_set_filesystem | binary                           || character_set_results    | utf8mb4                          || character_set_server     | utf8mb4                          || character_set_system     | utf8                             || character_sets_dir       | /usr/local/mysql/share/charsets/ |+--------------------------+----------------------------------+8 rows in set (0.00 sec)mysql>在手机app端又一次评论，输入表情符号，点击提交提示评论成功。5。问题分析总结：㈠ 原因普通的字符串或者表情都是占位3个字节，所以utf8足够用了，可是移动端的表情符号占位是4个字节，普通的utf8就不够用了，为了应对无线互联网的机遇和挑战、避免 emoji 表情符号带来的问题、涉及无线相关的 MySQL 数据库建议都提前採用 utf8mb4 字符集，这必需要作为移动互联网行业的一个技术选型的要点㈡ 限制需要 >= MySQL 5.5.3版本号、从库也必须是5.5的了、低版本号不支持这个字符集、复制报错。參考文章地址：http://bbs.csdn.net/topics/390055415