How Do I Ensure that the Character Set of an RDS MySQL Database Is Correct?
UTF-8 supports 4 byte characters, but RDS for MySQL utf8 supports only 3 byte characters. Emojis, uncommon Chinese characters, and newly added Unicode characters cannot be stored using the MySQL utf8 character set. MySQL released the utf8mb4 character set in 2010 and added the utf8mb4 code after 5.5.3 to be compatible with the 4-byte unicode. You only need to change utf8 to utf8mb4. No other conversion is required.
Data Admin Service (DAS) is a professional database management tool. You can view the database and system character sets through the DAS console.
Procedure
- Log in to the management console.
- Click in the upper left corner and select a region and a project.
- Click in the upper left corner of the page and choose Databases > Relational Database Service.
- On the Instances page, locate the target DB instance and click Log In in the Operation column.
Alternatively, click the target DB instance on the Instances page. On the displayed Basic Information page, click Log In in the upper right corner of the page.
- On the displayed login page, enter the correct username and password and click Log In.
- On the top menu bar, choose SQL Operation > SQL Window.
- Run the following SQL statement in the SQL window to view the database character set:
show variables like '%character%';
Figure 1 SQL execution result
- Run the following SQL statement in the SQL window to view the database coding:
show variables like 'collation%';
Figure 2 SQL execution result
- Change the character set to utf8mb4.
- Run the following SQL statement to change the database character sets.
ALTER DATABASE DATABASE_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
- Run the following SQL statement to change the table character sets.
- Run the following SQL statement to change all the field character sets in tables:
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
- character_set_client, character_set_connection, and character_set_results are the settings of the client.
- character_set_system, character_set_server, and character_set_database are the settings of the server.
- The priorities of the parameters on the server are as follows: character_set_database > character_set_server > character_set_system.
- Run the following SQL statement to change the database character sets.
Database Parameter Modification FAQs
- What Inappropriate Parameter Settings Cause Unavailability of the RDS for PostgreSQL Database?
- How Can I Change the Time Zone?
- How Do I Configure a Password Expiration Policy for RDS for MySQL DB Instances?
- How Do I Change the RDS Transaction Isolation Level?
- How Do I Ensure that the Character Set of an RDS MySQL Database Is Correct?
- Does RDS for PostgreSQL Support the test_decoding Plugin?
- How Do I Use the utf8mb4 Character Set to Store Emojis in an RDS for MySQL DB Instance?
- Where Should I Store NDF Files for RDS for SQL Server?
- Can I Use SQL Commands to Modify Global Parameters?
- How Do I Modify the Collation of RDS for SQL Server?
- How Do I Set Case Sensitivity for RDS for MySQL Table Names?
- Can I Enable Query Caching for My RDS for MySQL Instance?
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbotmore