Java.sql.SQLException:Incorrect string value: ' \xf0\x9f\x98\x87<b

Actual development encountered in the

Scene:

Solution: Copy Someone else's blog, no Tests,

Incorrect string value: ' \xf0\x9f ... ' for column ' XXX ' at row 1

The problem, because the UTF-8 encoding could be two, three, four bytes. Emoji emoticons or some special characters are 4 bytes, and MySQL UTF8 encode up to 3 bytes, so data is not plugged in.

That's my solution.

1. Locate the My.ini in the MySQL installation directory and make the following changes:

[Mysqld]

Character-set-server=utf8mb4

[MySQL]

Default-character-set=utf8mb4

Restart MySQL after modification

2. Convert the already built tables to UTF8MB4

Command:

Change database encoding: Alter DB caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;

ALTER TABLE table_name convert to Character set UTF8MB4 collate utf8mb4_bin; (Replace table_name with your table name)

And then it's OK. A version of the online spread adds a step that will character_set_client,character_set_connection,character_set_database,character_ the MySQL environment variable Set_results,character_set_server are modified to UTF8MB4, but I did not do this step, but also normal, so it may be that this step is superfluous, if you need to change, follow the instructions below to make changes.

3 Modifying the MySQL configuration file my.cnf (Windows My.ini)

MY.CNF generally in the etc/mysql/my.cnf position. When you find it, add the following three sections:
[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 '

Java.sql.SQLException:Incorrect String value: ' \xf0\x9f\x98\x87<b