Change the character set of an ApsaraDB RDS for MySQL instance to utf8mb4

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for MySQL instance

• A connection to the instance via a client or the command-line interface (CLI). For details, see Use a client or the CLI to connect to an ApsaraDB RDS for MySQL instance

How it works

Character sets in MySQL follow a cascading hierarchy: database -> table -> column. A column without an explicit character set inherits from its table, and a table inherits from its database. To fully migrate to utf8mb4, apply the change at the level you need—or apply it at each level from database down to column.

Change the character set

Run the following statements in the SQL window. Replace the placeholder values with your actual database, table, column names, and data type.

ALTER DATABASE <Database name> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE <Table name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE <Table name> CHANGE <Column name> <Column name> <Data type> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

FAQ

What do I do if I get the error "Unsupported collation on string indexed column XXX. Consider change to other collation"?

This error means an indexed string column is using a collation that is not compatible with the current operation. Query the column's current collation, then update it.

1. Check the column's collation:

   SHOW FULL COLUMNS FROM <Table name>;

2. Change the collation:

   ALTER TABLE <Table name> CHANGE <Column name> <Column name> <Data type> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

What's next

For additional guidance on managing character encoding on the client side, see Guarantee the availability of database character encoding for an ApsaraDB RDS for MySQL instance.