Store emoji ( Unicode characters ) in MySQL DB using Spring boot
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.
There are no changes you need to do to the configuration files of MySQL. All that we need to do is configure the right
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
utf8mb4 and the
Let’s assume that we want to store emojis in a
COMMENT column of table
USER_COMMENTS under the
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
The final JDBC URL will look something like this:
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
<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.
This should help you with not just the emojis, but the other Unicode characters ( different languages and dialects ) as well.