Solution to the error message "java.sql.SQLException: Incorrect string value:'\xF0\x9F\x92\xA9\x0D\x0A...'" when storing emoticons in MySQL

Preface

This article mainly introduces the solution to the MySQL storage table error: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\xA9\x0D\x0A...', and shares it for your reference. Let's take a look at the detailed introduction:

This project is based on Spring MVC + MySQL + Druid DataSource. An error message appears when inserting Emoji characters into the database:

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

Ordinary characters take up to 3 bytes, which is enough to use utf8. 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 and the connecting client.

MySQL Server

I have previously introduced a tutorial on how to set up MySQL to store emoji characters. If you want to learn more, you can take a look at it first.

On the server side, change the fields that need to store emoji characters to use 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 setup is as follows:

set names utf8mb4;

The open source druid datasource from Alibaba is used. It has a property called connectionInitSqls, which is used to set the SQL executed when the physical connection is initialized. So we can use connectionInitSqls to initialize the connection.

Spring is configured as follows:

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
 <!-- Basic attributes url, user, password -->
 <property name="url" value="jdbc:mysql://localhost:3888/majing?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull" />
 <property name="username" value="dbusername"/>
 <property name="password" value="dbpasswod"/>
 <property name="connectionInitSqls" value="set names utf8mb4;" />
 <!-- Other configurations -->
</bean>

Other database connection pools such as dbcp2 and HikariCP have the connectionInitSqls attribute.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.