Emoji emoji input MySQL database error solution

Original: http://blog.itpub.net/26230597/viewspace-1243233/

1, view the Tomcat background log, the core error message is as follows:
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)
... More

[Email protected] ~]# mysql-root-p
mysql> use test;
Database changed
Mysql> Show tables;
Ignoring query to other database
Mysql> Ctrl-c--exit!
Aborted
Forget to enter the-u parameter, rinse you input, OK, as follows:
[Email protected] ~]# mysql-uroot-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 4
Server Version:5.6.12-log Source Distribution
Copyright (c), +, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
Mysql>
mysql> use test;
Database changed
Mysql> Show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| C |
| Lubin_test |
| Test |
| tt |
+------------------------+
Rows in Set (0.00 sec)
This input incorrect string value: ' \xf0\x9f\x98\x97\xf0\x9f ... ' problem, most of them are character sets, formerly changed from Latain to GBK, from GBK to UTF8, And my content field has been utf8, then more than UTF8 is only utf8mb4, so go to modify the table field character set.

Mysql>


2, first go to modify the table field character set to UTF8MB4:
ALTER TABLE ugc_review_content MODIFY ' content ' TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT ' comments ';
After the execution, and then on the mobile app Test, still reported the same error.


3, then go to modify the table character set UTF8MB4:
ALTER TABLE ugc_review_content charset=utf8mb4 comment= ' sunburn/Recommended comment content ';
After the execution, and then on the mobile app Test, still reported the same error.


4, then go to modify the database character set UTF8MB4:
Vim MY.CNF
init-connect= ' SET NAMES utf8mb4 '
Character-set-server=utf8mb4

Restart MySQL Database
[[Email protected] ~]# service MySQL restart
Shutting down MySQL .... Determine
Starting MySQL ..... ..... ..... ..... ..................... [OK].
[Email protected] ~]#

View the character set for 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>

re-comment on the phone app, enter emoji, click Submit to comment success.

5, the Problem Analysis summary:
㈠ reasons
Ordinary string or expression is a placeholder 3 bytes, so utf8 enough, but the mobile side of the emoji placeholder is 4 bytes, the ordinary utf8 is not enough, in order to deal with the wireless internet opportunities and challenges, to avoid the problem of emoji emoticons, involving wireless-related MySQL Database recommendations are in advance using the UTF8MB4 character set, which must be a key element in the mobile Internet industry's technology selection
㈡ restrictions
Requires >= MySQL 5.5.3 version, from the library must be 5.5, the lower version does not support this character set, copy error.

Emoji emoji input MySQL database error solution