MySQL Insert emoji expression error

caused By:java.sql.SQLException:IncorrectstringValue'\xf0\x9f\x98\x84'  forColumn' nickname'At row1At 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.executeUpdate (Preparedstatement.java:2458)

The UTF-8 encoding can be two, three, and four bytes. The emoji expression is 4 bytes, while MySQL UTF8 encodes up to 3 bytes, so data cannot be plugged in.

Workaround:

1. Modify the MySQL configuration file My.ini

Locating the configuration file is key, you can find the MySQL service in the window service, right-click Property View,

Open the configuration file and append the following to the node in the response:

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

2, modify the database and table structure, the command line is as follows:

ALTER database ' database ' CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE ' tablename ' CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ' tablename ' modify ' columnName ' text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3, modify the Java program in the database connection string, do not set characterencoding, set Autoreconnect=true, connection string as follows:

Jdbc:mysql://127.0.0.1:3306/dbname?useunicode=true&autoreconnect=true&allowmultiqueries= True

4, the last step, restart the MySQL database, restart the Web server.

OK, get it done!

MySQL Insert emoji expression error