MySQL Storage emoji Times java.sql.SQLException: Incorrect string value:'\ xF0\ x9F\ x92\ xA9\ x0D\ x0A..' What about mistakes?

This article will explain in detail about MySQL storage emoji Times java.sql.SQLException: Incorrect string value:'\ xF0\ x9F\ x92\ xA9\ x0D\ x0A..' How to make a mistake? the editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

This project is based on Spring MVC + MySQL + Druid DataSource. Error inserting emoji Emoji characters into the database:

Java.sql.SQLException: Incorrect string value:'\ xF0\ x9F\ x92\ xA9\ x0D\ x0A..'

Normal characters take up to 3 bytes at most, so using utf8 is sufficient. Emoji emoji characters take up 4 bytes and need to be stored using the utf8mb4 character set. The solution requires changes in two places: the MySQL server side and the connected client side.

MySQL server side

I have previously introduced you to the setup tutorial that enables MySQL to store emoji emoji characters. Friends who want to know more can take a look at it first.

On the server side, change the fields that need to store emoji emoji characters to the utf8mb4 character set:

ALTER TABLE mytable MODIFY COLUMN myfiled varchar (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Client

The client needs to set the character set of the connection to utf8mb4.

The settings are as follows:

Set names utf8mb4

Using Ali's open source druid datasource, it has an attribute connectionInitSqls, which is used to set the sql that is executed when the physical connection is initialized. So we can use connectionInitSqls to initialize the connection.

The Spring configuration is as follows:

Other database connection pools, such as dbcp2,HikariCP, have connectionInitSqls attributes.

This is the end of the article about "MySQL Storage emoji Times java.sql.SQLException: Incorrect string value:'\ xF0\ X9F\ X92\ xA9\ x0D\ x0A." I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.