SoFunction
Updated on 2025-03-03

A thorough understanding of UTF-8 and UTF-8MB symbol sets in MySQL

Preface

In today's globalization, data storage and processing need to support multiple languages ​​and character sets. Character set selection is particularly important for web applications and database systems, especially in systems that deal with characters containing multiple languages ​​(such as Chinese, Arabic, emoji, etc.). As a commonly used database management system, MySQL provides a variety of character sets to support data storage and operations in different languages.

This article will dive into two common character sets in MySQL: UTF-8 and UTF-8MB4, analyzing their differences, usage scenarios, storage differences, and how to choose the right character set to ensure the scalability and compatibility of the application system.

1. What are UTF-8 and UTF-8MB4?

1.1 UTF-8

UTF-8 is a variable-length character encoding, which is an implementation of Unicode. In UTF-8 encoding, each character can be represented by 1 to 4 bytes. The biggest feature of UTF-8 encoding is backward compatibility with ASCII, i.e. all standard ASCII characters (U+0000 to U+007F) are still represented by 1 byte.

UTF-8 is able to represent almost all language characters, and it has become the most widely used character encoding standard on the web.

  • 1 byte: ASCII characters (0x00 to 0x7F)
  • 2 bytes: More common characters, such as Latin letters, Chinese characters, etc. (0x80 to 0x7FF)
  • 3 bytes: Extended characters, such as some Chinese characters and other characters with medium frequency usage (0x800 to 0xFFFF)
  • 4 bytes: Rare characters, such as emojis, characters of some ethnic minorities, etc. (0x10000 to 0x10FFFF)

1.2 UTF-8MB4

UTF-8MB4 is an enhanced version of UTF-8 that supports a complete Unicode character set and supports up to 4 bytes of characters. It can store any Unicode characters, including some special characters, such as emojis, ancient texts, etc.

  • 4 bytes: UTF-8MB4 introduces support for more than 3 byte characters (such as emojis and some minority characters) that are beyond UTF-8's support range and therefore require 4 bytes to be stored.

In MySQL,UTF-8In fact, it does not fully follow the Unicode standard and supports up to 3 bytes of characters, andUTF-8MB4Solved this problem and provided complete Unicode support.

2. The difference between UTF-8 and UTF-8MB4

2.1 Character Set Range

  • UTF-8: UTF-8 in MySQL is actually an incomplete implementation, which can only support up to 3 bytes of characters, so it cannot store some Unicode characters, especially high-digit characters in the Unicode range (such as emojis and some rare Chinese characters).
  • UTF-8MB4:UTF-8MB4 fully supports the Unicode standard, with a maximum support of 4 byte characters, which means it is able to store all Unicode characters, including emojis and other rare characters.

2.2 Storage space

Due to the different character sets supported by UTF-8 and UTF-8MB4, their storage requirements are also different.

  • UTF-8: In MySQL, UTF-8 uses 1 to 3 bytes to store each character. For common characters (such as English and common Latin letters), only 1 byte is required, while for some complex characters (such as Chinese and other extended characters), 2 or 3 bytes are required.
  • UTF-8MB4: UTF-8MB4 uses 1 to 4 bytes to store characters. For common characters, it is still 1 byte, but for emojis and some special characters, UTF-8MB4 uses 4 bytes for storage.

Therefore, when storing the same characters, UTF-8MB4 takes up more storage space than UTF-8, especially when you need to store a large number of emojis, etc. 4 byte characters.

2.3 Backward compatibility

  • UTF-8: Since UTF-8's character set does not fully support all Unicode characters, it is sufficient for handling common languages ​​(such as English, Chinese, Japanese, etc.), but cannot store certain special symbols, emojis and other characters.
  • UTF-8MB4: UTF-8MB4 is a complete implementation of the Unicode standard, supporting all characters, so it is more general and powerful, suitable for applications with multilingual and multi-character requirements.

3. Use UTF-8 and UTF-8MB4 in MySQL

3.1 Why use UTF-8MB4?

Although the character set of UTF-8 is enough for many applications, UTF-8 no longer meets all needs as applications and websites gradually support emojis and more Unicode characters (such as ancient texts, special symbols).

UTF-8MB4 fully supports Unicode standards, especially for modern web applications, and the demand for emojis and special symbols is increasing. For example, social platforms, chat apps, user comments, etc. all need to be able to handle emojis and other special characters.

Therefore, if your application contains text entered by the user (such as social networks, instant messaging systems, etc.), useUTF-8MB4It is a more recommended choice.

3.2 Character Set Selection in MySQL

In MySQL, you can select a character set to define the character encoding of a database, table, or column. Choosing the right character set is essential for storing text data. If your database tables need to support multiple languages ​​and contain emojis or special symbols, UTF-8MB4 is the best choice.

When creating a database, table, or column, you can specify a character set:

  • Specify the character set when creating a database：
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • Specify the character set when creating a table：
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • Modify the character set of an existing table：

If your table has been usedutf8character set and want to convert it toutf8mb4, you can modify it through the following command:

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

In this way, you can ensure that the database can store all types of characters, especially emojis and other high-ranking Unicode characters.

3.3 Things to note

  • Increased storage space: Since UTF-8MB4 uses up to 4 bytes to store characters, the amount of data in the table may increase compared to using UTF-8, especially when you store a large number of special characters (such as emojis), the table size will increase.

  • MySQL version support: Ensure the MySQL version used supportsutf8mb4Character set. MySQL is officially supported since version 5.5.3utf8mb4, so if you are using an older version of MySQL, you may need to upgrade.

  • Application Compatibility: Make sure your application also supports UTF-8MB4. Many modern web applications (such as PHP, Python, Java, etc.) support UTF-8MB4, but older versions of the programs may not be fully compatible.

3.4 Performance Impact

In practical applications, UTF-8MB4 consumes more storage space and memory than UTF-8, especially when tables contain a large number of emojis or other characters that require 4 bytes. Therefore, if your application does not need to process these characters, using UTF-8 may be a more space-saving option.

However, with the increasing use of emojis and other Unicode characters, more and more applications are beginning to choose to use UTF-8MB4 to ensure compatibility and future scalability.

4. Summary

The utf8 and utf8mb4 character sets provided by MySQL provide us with flexible options to store multilingual text data. When selecting a character set, it is important to consider the needs of the application, the diversity of data, and the requirements of storage space. UTF-8 is a widely used character set that works in most languages, but it does not support all Unicode characters, especially emojis and some rare characters. UTF-8MB4 is a complete Unicode implementation that supports all Unicode characters, suitable for applications that need to support multiple languages ​​and symbols.

If your application needs to support emojis, special symbols, or other Unicode characters, it is recommended to use `UTF-8MB4`. At the same time, it is important to note that when selecting a character set, you should weigh the storage space, application compatibility, and future expansion requirements.

The above is a detailed article about deeply understanding the UTF-8 and UTF-8MB4 character sets in MySQL. For more information about MySQL UTF-8 and UTF-8MB4 character sets, please pay attention to my other related articles!