How do I update the character set to utf8mb4 on Amazon RDS for MySQL?

Short description

In MySQL and MySQL compatible engines, the utf8mb4 character set processes and stores all characters from the Unicode character set, such as emojis and supplementary characters. The utf8 character set processes characters only from the Basic Multilingual Plane and doesn't process emojis or supplementary characters.

For all new applications, use the utf8mb4 character set instead of utf8.

Resolution

Before you update your character set on your production databases, it's a best practice to restore a snapshot to a test instance. Then, test your character set updates on the test instance. Also, make sure that you convert all triggers, events, and database objects to the utf8mb4 character set. To use a new character set on your applications and clients, you might need to upgrade your clients' versions.

Note: In the following procedures, replace each example-name with the names of your resources.

By default, the collation on MySQL is utf8mb4_0900_ai_ci. To view a list of collations that you can use, run the following command:

SHOW COLLATION WHERE Charset = 'utf8mb4';

To update your character set to utf8mb4, complete the following steps:

1. Update your RDS parameter group.

2. Run the following ALTER DATABASE command to update the collation_server parameter to utf8mb4_0900_ai_ci and the following character sets to utf8mb4:
   character_set_server
   character_set_client
   character_set _connection
   character_set_results

   ALTER DATABASE
       example-database-name
       CHARACTER SET = utf8mb4
       COLLATE = utf8mb4_0900_ai_ci;
   

   Note: For more information, see ALTER DATABASE statement and Database character set and collation on the MySQL website.

3. Update the table and column character set and collation:

   show create table example-table-name

4. To modify the default table and column definitions, run the ALTER TABLE command:

   ALTER TABLE example-table-name
     DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
     MODIFY example-column-name CHAR(10)
       CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
     MODIFY example-column-name-2 varchar(32)
       CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

   Note: When you run ALTER TABLE to change your character set, the tables are locked. When you alter the column, use the column's data type. The maximum length of columns and index keys is different when you use utf8mb4 instead of utf8. For more information, see ALTER TABLE statement on the MySQL website.

To automatically convert the table and columns to utf8mb4, run the CONVERT TO command:

ALTER TABLE example-table-name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Note: MySQL automatically converts the columns' data types to make sure that the columns store the same number of characters as the original character set. To keep the data types the same, run the MODIFY command.

Related information

Converting between 3-byte and 4-byte Unicode character sets on the MySQL website

MySQL 8.0 collations: migrating from older collations on the MySQL website