Article From:https://www.cnblogs.com/lhat/p/9967751.html

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)

The original Emoji expression takes up 4 bytes, but UTF-8 characters only take up 3 bytes in MySQL, which makes it impossible to save Emoji expression in MySQL database.

1. Modify Character Set of Database, Table and Column

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(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Modify MySQL configuration file my. CNF (my. ini under windows)

[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'    

Be careful: If the background code uses mysql-connector-x.x.x.jar, the version of mysql-connector must be higher than 5.1.13, otherwise utf8mb4 is not supported.


Refer to this blog Mysql to save Emoji expression (Weichat developer nickname.)

Leave a Reply

Your email address will not be published. Required fields are marked *