Store emoji ( Unicode characters ) in MySQL DB using Spring boot

by microideation · December 12, 2022

This blog post will show us how to persist and retrieve emoji ( or any Unicode ) characters to/from MySQL using a spring boot application.

If we try to save an emoji ( May be copied and pasted to the text field or API field using postman ), you may get an error like

Incorrect string value: ‘\xF0\x9F\x98\x83\xF0\x9F…’ for column from the MySQL
The saved emoji will be replaced with “?” in the database.

There are some preparations and configuration changes that are needed on both the MySQL side and the spring boot JPA connection to handle emojis. We will see them in detail below.

MySQL configuration

There are no changes you need to do to the configuration files of MySQL. All that we need to do is configure the right character-set and collation to the respective Schema, Table and Column where we want to store the emoji.

The basic requirement of MySQL for storing emoji ( or Unicode characters ) is to ensure that we are having the character-set as utf8mb4 and the collation as utf8mb4_unicode_ci

Let’s assume that we want to store emojis in a COMMENT column of table USER_COMMENTS under the BLOGS schema.

Commands for changing character-set and collation

Execute the below commands on your DB for changing the character set and collation for Schema, Table & Column. Ensure to replace the names with your respective details.

-- Alter the schema
ALTER SCHEMA `BLOGS`  DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4_unicode_ci ;

-- Alter the table
ALTER TABLE `BLOGS`.`USER_COMMENTS` CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_unicode_ci ;

-- Alter the column
ALTER TABLE `BLOGS`.`USER_COMMENTS` 
CHANGE COLUMN `COMMENT` `COMMENT` LONGTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NULL DEFAULT NULL ;

At this point, you should be able to copy and paste an emoji to the above text column and save it from MySQL workbench ( or similar DB management tools ).

Spring boot configuration

Now that our DB is set up to store emojis, we need to prepare the spring boot application for handling the Unicode characters.

Configure the JPA connection URL

We need to configure the JDBC connection URL to have Unicode support enabled with specific character encoding.

Add the following parameters to the JDBC URL

&useUnicode=yes&characterEncoding=UTF-8

The final JDBC URL will look something like this:

jdbc:mysql://localhost:3306/BLOGS?zeroDateTimeBehavior=convertToNull&useUnicode=yes&characterEncoding=UTF-8&useSSL=false

Upgrade mysql-connector dependency

In some cases, you may still get some errors related to the characters being saved. Ensure that the above steps are completed properly and if the issue persists, you may need to upgrade the mysql-connector dependency. If the connector is 5.7.x version, upgrade to 8.0.x version.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

Note: This does not mean that you need to use the MySQL DB version also as 8.x. The mysql-connector 8.0.x should work fine with MySQL DB version 5.7.x as well.

Reload the dependencies ( Reimport in the case of Maven ) and then try again.