JIYIK CN >

utf8mb4

Fortunately, MySQL 5.5.3 (released in early 2010) introduced a utf8mb4new encoding called which maps to proper UTF-8 and thus fully supports Unicode, including astral symbols.

Step 1: Create a backup

Create a backup of all databases on the server you are upgrading. Safety first!

Step 2: Upgrade MySQL Server

Upgrade your MySQL server to v5.5.3+, or ask your server administrator to do it for you.

Step 3: Modify database, table and column

Change the character set and collation properties of the database, tables, and columns to use utf8mb4instead of utf8.

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

Since is fully backwards compatible utf8mb4with utf8, no mojibake or other forms of data loss should occur. (But you have backups, right?)

Step 4: Check the maximum length of column and index keys

This is probably the most tedious part of the entire upgrade process.

When utf8converting from utf8mb4, the maximum length of a column or index key in bytes is unchanged. Therefore, it is smaller in terms of characters because the maximum length of a character is now four bytes instead of three.

For example, a TINYTEXTcolumn can hold up to 255 bytes, which corresponds to 85 three-byte characters or 63 four-byte characters. Suppose we have a column that uses utf8TINYTEXT but must be able to contain more than 63 characters. Given this requirement, we cannot convert this column to utf8mb4 unless we also change the data type to a longer type, such as TEXT_t — because if we try to fill it with four-byte characters, we will only be able to enter 63 characters, but no more.

The same is true for index keys. InnoDBThe maximum index length for a storage engine is 767 bytes, so we can index up to 255 or 191 characters for a utf8 or utf8mb4 column, respectively. If our current index on a utf8 column is longer than 191 characters, we will need to index fewer characters when using utf8mb4VARCHAR(255) . (I had to change some indexes on the columns to VARCHAR(191).)

Step 5: Modify the connection, client and server character sets

In our application code, set the connection character set to utf8mb4 . This can be done by simply SET NAMES utf8replacing any variation of with SET NAMES utf8mb4. If our old SET NAMESstatement specified a collation, make sure to change that as well, e.g. SET NAMES utf8 COLLATE utf8_unicode_cito SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci.

Make sure to set both the client and server character sets. I have the following in my MySQL configuration file ( /etc/my.cnf ):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

We can easily confirm that these settings are working correctly:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

As we can see, all relevant options are set to utf8mb4 , except that should be binary unless the file system we are using supports multi-byte UTF-8 encoded characters character_set_filesystemin filenames , while is always utf8 and cannot be overridden.character_set_system

注意: The default character set and collation can also be configured at some other levels.

Step 6: Repair and optimize all tables

After upgrading your MySQL server and making the necessary changes above, make sure to repair and optimize all databases and tables. I did not do this immediately after the upgrade (I did not think it was necessary since everything seemed to be working fine at first glance) and ran into some strange errors where UPDATEstatements had no effect, even though no errors were thrown.

We can run the following MySQL query for each table to be repaired and optimized:

# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

Fortunately, this can be mysqlcheckeasily done in one go using the command line utility:

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

This will prompt for the root user's password, after which all tables in all databases will be repaired and optimized.

Summarize

Never use utf8 in MySQL - always use utf8mb4 . It may take some time to update your database and your code, but it is definitely worth the effort. Why arbitrarily limit the set of symbols that can be used in a database? Why lose data every time a user enters an astral symbol as part of a comment or message or whatever we store in the database? There is no reason not to strive for full Unicodesupport everywhere. Do the right thing and use utf8mb4 . 🍻