ApsaraDB RDS:Character sets for RDS for MySQL

Collation naming conventions

A collation name starts with the name of its associated character set and ends with one of the following suffixes:

Example: With collation_connection set to utf8_general_ci, the values a and A are treated as equal. With utf8_bin, they are not.

Character set inheritance

Character sets cascade down through levels: server > database > table > column. A column with no explicit character set inherits from its table; the table inherits from its database; and the database inherits from the server.

This means changing a table's character set does not affect columns that already have an explicit character set—those must be updated separately.

Inspect character set settings

Use the following SQL statements to inspect current character set and collation settings on your RDS instance:

-- Inspect current settings
SHOW GLOBAL VARIABLES LIKE '%char%';         -- Character set parameters of the RDS instance
SHOW GLOBAL VARIABLES LIKE 'coll%';          -- Collation parameters of the current session
SHOW CHARACTER SET;                           -- Character sets supported by the RDS instance
SHOW COLLATION;                               -- Collations supported by the RDS instance
SHOW CREATE TABLE table_name \G              -- Character set of a specific table
SHOW CREATE DATABASE database_name \G        -- Character set of a specific database
SHOW CREATE PROCEDURE procedure_name \G      -- Character set of a stored procedure
SHOW PROCEDURE STATUS \G                     -- Character set settings of all stored procedures
SHOW FULL FIELDS FROM table_name;            -- Character set of each column in a table
ALTER DATABASE db_name DEFAULT CHARSET utf8;                            -- Changes the character set of a database
CREATE DATABASE db_name CHARACTER SET utf8;                             -- Specifies a character set for a database during creation
ALTER TABLE tab_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;     -- Changes the character set and collation of a table

Modify the character_set_server parameter

The character_set_server parameter sets the server-level default character set. Changing it affects all databases created after the change takes effect.

1. Log on to the ApsaraDB RDS console.

2. In the top navigation bar, select the region where the RDS instance resides.

   

3. Find the RDS instance and click the instance ID.

4. In the left-side navigation pane, click Parameters.

5. On the Editable Parameters tab, find character_set_server and click the icon in the Running Parameter Value column. Enter the new value in the dialog box and click OK.

6. Click Apply Changes. In the dialog box, click OK and wait for the RDS instance to restart.

Change character sets using SQL

-- Change the character set of a database
ALTER DATABASE <database_name> CHARACTER SET <charset_name> COLLATE <collation_name>;

-- Change the character set of a table (converts all columns that have no explicit character set)
ALTER TABLE <table_name> CONVERT TO CHARACTER SET <charset_name> COLLATE <collation_name>;

-- Change the character set of a specific column
ALTER TABLE <table_name> MODIFY <column_name> <data_type> CHARACTER SET <charset_name> COLLATE <collation_name>;

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;