Article From:

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94' for colum n 'name' at row 1 at com.mysql.jdbc.SQLError.createSQLException( at com.mysql.jdbc.MysqlIO.checkErrorPacket( at com.mysql.jdbc.MysqlIO.checkErrorPacket( at com.mysql.jdbc.MysqlIO.sendCommand( at com.mysql.jdbc.MysqlIO.sqlQueryDirect( at com.mysql.jdbc.ConnectionImpl.execSQL( at com.mysql.jdbc.StatementImpl.executeUpdate( at com.mysql.jdbc.StatementImpl.executeUpdate(

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)

default-character-set = utf8mb4    
default-character-set = utf8mb4    
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 *