mysql saves emoji expressions (WeChat development user 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 above error is reported, it may be the field type or encoding in the java code and the database If they don’t match, in this case, just unify the format or encoding.

Here we mainly introduce the error of inserting emoji images into the database and the solutions

When using mysql database, if the character set is UTF-8 and in java On the server, when storing emoji expressions, the above exception will be thrown (for example, when WeChat develops to obtain user nicknames, some users' nicknames use emoji images)

This is an exception due to the character set not supporting it. , because UTF-8 encoding may be two, three, or four bytes, of which Emoji expressions are four bytes, and MySQL's UTF-8 encoding can be up to three bytes, so the data cannot be inserted.
Solution:
1. Solve it from the database level (the version of mysql that supports utf8mb4 is 5.5.3+ and must be upgraded to a newer version)
Note:
(1. Modify database, table ,column character set

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 the mysql configuration file my.cnf (window is my.ini)

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

(3. If you are using a java server, upgrade or ensure that the mysql connection version is higher than 5.1.13, otherwise you still cannot try utf8mb4
(4. Server-side db configuration File

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
jdbc.username=root
jdbc.password=password

If mysql-connector is upgraded, characterEncoding=utf8 can be automatically recognized as utf8mb4 (compatible with the original utf8), and
autoReconnection (When the database connection is interrupted abnormally, will it automatically reconnect? The default is false) It is strongly recommended to match it. Ignoring this attribute may lead to caching reasons.
The latest DB is not read. configuration, resulting in the inability to try the utf8mb4 character set;

2. Solution from the application layer
After obtaining the data, encode it before going to the data inventory:

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

Decoded when retrieved from the database and ready for display,

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

When solving the problem from the application layer, it is recommended not to code directly in the object getter and setter methods, because the setter method encodes the nickname when the object is put in. When inserting into the database, it is equivalent to calling the getter method from the object to refer to you. Taking out this will re-decode the Nickname that was previously encoded by the setter, which means that the above problems will still occur. Please pay attention to PHP Chinese website

for articles!