Why not use UTF-8 encoding in MySQL?

MySQL UTF-8 encoding

MySQL has supported UTF-8 since version 4.1, which was in 2003, but the currently popular UTF-8 standard (RFC 3629) was specified after that. Because of this, the UTF-8 in MySQL is inconsistent with the UTF-8 in our daily development, which leads to some problems. MySQL's UTF-8 only supports up to three bytes per character, while real UTF-8 supports up to four bytes per character.

Problem reproduction

The database table is as follows: utf8 encoding

Add a record to the database:

@Test
public void testInsert() {
 User user = new User();
 user.setUsername("\uD83D\uDE00 ");
 user.setPassword("123456");
 userRepo.save(user);
}

Here is just part of the code, it doesn’t matter if you don’t understand it. Here is to insert a record into the user table. Where username is \uD83D\uDE00.

In fact, \uD83D\uDE00 is an emoji.

Because the utf8 character set in MySQL only supports the Unicode range of three-byte UTF-8 encoding, and emoji characters belong to the four-byte encoding part, the program will report an error when running as expected. Run this code:

As expected, an error was reported.

Solving the problem

Although MySQL's UTF-8 has defects, MySQL (including mariadb) officials did not fix this bug. Instead, they supported real UTF-8 through the "utf8mb4" re-released in 2010. Therefore, if you want to solve this problem, you can only set the MySQL database to utf8mb4 character set.

Summarize

This problem was discovered because an emoji expression was saved when saving data. In fact, when I first started using MySQL, I discovered utf8mb4, but I didn’t understand the difference between UTF8 and UTF8MB4. After learning this lesson, I will set the character set to utf8mb4 when using MySQL in the future.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.