:Description of RDS MySQL Character Set

Overview

This article mainly introduces information about the RDS MySQL character set.

Detail

Alibaba Cloud reminds you that:

• Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

This article mainly introduces the character set from the following aspects:

• collation naming rules
• character encoding related parameters
• Character set-related SQL statements
• Change character sets
  • How the console modifies character set parameters (character_set_server)
  • Method for modifying a database character set by using SQL statements
  • Method for Modifying Database Character Set by Using Navicat

collation naming rules

The collation starts with the corresponding character set name, ends with_ci (case-insensitive),_cs (case-sensitive),_bin (case-sensitive), etc. The function of the collation is to use different methods for comparison of each different character set. For example, when the collation_connction of the session is set to utf8_general_ci collation, the character A and the character A are equivalent, while when it is set to utf8_bin, the character A and the character A are not equivalent. For specific information, see the following example.

character encoding related parameters

1. Log on to the RDS instance. For more information, see Connect to a MySQL instance.
2. Execute the following SQL statement in the target library to view the character set of the corresponding database.

   show variables like '%character%';

   The following output is returned.
   
   

   Note:
   

   • The above parameters must ensure that the values except the character_set_filesystem are the same, so that there will be no garbled code.
   • character_set_client, character_set_connection, and character_set_results are client settings.
   • character_set_system, character_set_server, and character_set_database are server-side settings.
   • The server-side parameter priority is character_set_database > character_set_server > character_set_system.
   • character_set_system parameter does not provide changes on the console for the time being, but because it has the lowest priority, it has little impact. After modifying the client character set, character_set_database, and character_set_server, it can basically ensure that there will be no garbled codes. When setting the client character encoding in the code, it is recommended to modify the client settings through set names [$Character_Set]. [$Character_Set] is the character set that needs to be set.

Character set-related SQL statements

show global variables like '%char%;                 # View parameter settings related to RDS instance character set
show global variables like 'coll%;                  # View parameter settings collation the current session
show character set;                                 # View character sets supported by the instance
show collation;                                     # View the collation supported by the instance
show create table [$Table_Name];                    # View table character set settings
show create database [$Database_Name];              # View database character set settings
show procedure status;                              # Displays the basic information of all stored procedure in the database
show create procedure [$Procedure_Name];            # Displays the details of a stored procedure
alter database db_name default charset utf8;        # Modify the character set of the database
create database db_name character set utf8;         # Specify the character set when creating the database
alter table tab_name default charset utf8 collate utf8_general_ci; # Modify table character set and collation

Note:

• [$Table_Name] is the required table name.
• [$Database_Name] is the library name.
• [$Procedure_Name] is the name of the stored procedure, which usually matches the show procedure status; Use.

The following figure shows an example command output.

Change character sets

Note: Changing the utf8 to utf8mb4 character set will not affect the subsequent Data Quality, and the existing data will not be affected, but the data storage space will increase.

Modify character set parameters on the console

For how to modify character_set_server parameters, see Set instance parameters.

Use SQL statements to modify the database character set

When you modify a column, all rows in the current column are immediately converted to the new character set. The alter table locks Canadian dollar data in the table. For more information, see Metadata lock generation and processing on RDS MySQL tables.

• Modify Library: ALTER DATABASE [$Database] CHARACTER SET [$Character_Set] COLLATE [$Collation_Name];.

  Note:
  

  • [$Database] is the library name of the database.
  • [$Character_Set] is the character set name.
  • [$Collation_Name] is the collation name, that is, the collation.

• Modify Table: ALTER TABLE [$Table] CONVERT TO CHARACTER SET [$Character_Set] COLLATE [$Collation_Name];.

  Note :[$Table] is the name of the table.

• Modify Column: ALTER TABLE [$Table] MODIFY [$Column_Name] [$Field_Type] CHARACTER SET [$Character_Set] COLLATE [$Collation_Name];.

  Note:
  

  • [$Column_Name] is the field name.
  • [$Field_Type] is the field type.

As shown in the following SQL statement, modify the c2 column in the dbsdq library, tt2 table, and tt2 table to the utf8mb4 character set.

alter database dbsdq character set utf8mb4 collate utf8mb4_unicode_ci;
use dbsdq;
alter table tt2 convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table tt2 modify c2  varchar(10) character set utf8mb4 collate utf8mb4_unicode_ci;

Use Navicat to modify the database character set

• Modify library:
  Use Navicat to connect to the database, select the name of the connection, right-click the name of the database, click Edit Database, select the desired character set on the right side of the character set, and click OK.
• Modify a table:
  Right-click the table, click Design Table, and then click Options. Select the desired character set at the right of the character set, and then click Save.
• Modify a field:
  Click Field, select the corresponding field, select the desired character set, and click Save.

Documentation

• RDS MySQL uses utf8mb4 character set to store emoji expressions
• MySQL official website character set introduction

Applicability

• ApsaraDB RDS for MySQL