Mysql saves emoji expressions (nickname of WeChat developers ..)

This article mainly introduces how to use mysql to save emoji expressions (developer nickname ..)

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94' for colum n 'name' at row 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)

When the preceding error is reported, the fields in the java code do not match the field type or encoding in the database. in this case, you only need to unify the format or encoding.

This section describes the error and solution for inserting images into the database of emoji.

When using the mysql database, if the character set is UTF-8 and on the java server, when storing emoji expressions, the above exception will be thrown (such as the development to get the user nickname, some users use emoji images for nicknames)

This is because the character set does not support exceptions, because UTF-8 encoding may be two, three, and four bytes, of which the Emoji expression is four bytes, while mysql UTF-8 encoding can be up to three bytes, therefore, data cannot be inserted.
Solution:
I. solve the problem at the database level (mysql supports utf8mb4 versions 5.5.3 + and must be upgraded to a newer version)
Note:
(1. modify the database, table, and column character sets

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;ALTER TABLE table_name CHANGE column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

(2. modify mysql configuration file my. cnf (window is my. ini)

[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'

(3. Use a java Server. upgrade or make sure that the mysql connection version is later than 5.1.13. Otherwise, you cannot try utf8mb4.
(4. db configuration file on the server side

jdbc.driverClassName=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUEjdbc.username=rootjdbc.password=password

If mysql-connector ctor is upgraded, characterEncoding = utf8 can be automatically recognized as utf8mb4 (compatible with the original utf8 ).
AutoReconnection? The default value is false. it is strongly recommended that this attribute be ignored, which may cause caching,
The latest configuration of the database is not read. as a result, the utf8mb4 character set cannot be used for trial;

2. solve problems at the application layer
Encode the data before it is stored in the data inventory:

URLEncoder.encode(nickName, "utf-8");

Decode the data when you prepare to display the data from the database,

URLDecoder.decode(nickname, "utf-8");

When solving the problem from the application layer, we recommend that you do not directly encode it in the getter and setter methods of the object, because the setter method will encode the nickname when it is put into the object, when you insert a database, it is equivalent to calling the getter method from the object to retrieve the reference. this will re-decode the nick name encoded by setter. This means that no operation is performed on the nick name. The above problems still occur.

For more articles on saving emoji expressions (developer nickname...) in mysql, please follow the PHP Chinese website!