Character sets for ApsaraDB RDS for MySQL-ApsaraDB RDS(RDS)-阿里云帮助中心

Collation naming conventions

A collation name starts with the name of its corresponding character set and ends with a suffix: _ci for case-insensitive, _cs for case-sensitive, or _bin for a binary comparison based on character code values.

For example, if the collation_connection for a session is set to utf8_general_ci, the characters 'a' and 'A' are treated as equivalent. However, if it is set to utf8_bin, 'a' and 'A' are not equivalent.

(xxx.mysql.rds.aliyuncs.com) [xxx]> show variables like 'coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
(xxx.mysql.rds.aliyuncs.com) [xxx]> select count(*)  from t where 'a' = 'A';
+----------+
| count(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)
(xxx.mysql.rds.aliyuncs.com) [xxx]> set collation_connection='utf8_bin';
Query OK, 0 rows affected (0.00 sec)
(xxx.mysql.rds.aliyuncs.com) [xxx]> show variables like 'coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_bin           |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
(xxx.mysql.rds.aliyuncs.com) [xxx]> select count(*)  from t where 'a' = 'A';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

MySQL commands for character sets

show global variables like '%char%';    # View character set parameters for the RDS instance.
show global variables like 'coll%';     # View collation parameters for the current session.
show character set;                     # View character sets supported by the instance.
show collation;                         # View collations supported by the instance.
show create table table_name \G         # View the character set settings for a table.
show create database database_name \G   # View the character set settings for a database.
show create procedure procedure_name \G # View the character set settings for a stored procedure.
show procedure status \G                # View the character set settings for a stored procedure.
alter database db_name default charset utf8;  # Modify the character set of a database.
create database db_name character set utf8;   # Specify the character set when you create a database.
alter table tab_name default charset utf8 collate utf8_general_ci;   # Modify the character set and collation for a table.

For example, the show create database command's output includes DEFAULT CHARACTER SET utf8, which indicates the database's default character set is utf8.

mysql> show create database xxx;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| xxx      | CREATE DATABASE xxx /*!40100 DEFAULT CHARACTER SET utf8 */     |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

Modify character_set_server in the console

Important

Modifying the character_set_server parameter requires an instance restart. Perform this operation during off-peak hours to minimize impact.

Procedure

Log on to the ApsaraDB RDS console.
In the top-left corner of the page, select the region where the instance is located.
Find the target instance and click its ID.
In the left-side navigation pane, click Parameters.
On the Editable Parameters tab, locate the character_set_server parameter. Click the edit icon next to it, set the value to your target character set (for example, utf8), and then click OK.
In the top-right corner, click Apply Changes. In the confirmation dialog box that appears, click OK. The instance will then restart.

Note
For instances with a privileged account, this change applies only to newly created databases and does not affect existing ones.

Modify character sets using SQL statements

Syntax:

# To modify a database
ALTER DATABASE <database_name> CHARACTER SET <character_set_name> COLLATE <collation_name>;
# To modify a table
ALTER TABLE <table_name> CONVERT TO CHARACTER SET <character_set_name> COLLATE <collation_name>;
# To modify a column
ALTER TABLE <table_name> MODIFY <column_name> <data_type> CHARACTER SET <character_set_name> COLLATE <collation_name>;

Example: The following SQL statements demonstrate how to modify the character set to utf8mb4 for a database, a table, and a column:

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;

Note

When you modify a column, all existing rows in that column are immediately converted to the new character set.
The ALTER TABLE statement applies a metadata lock to the table.
To use a character set other than the default, you must explicitly define it for an object, such as a database, table, or column. MySQL applies character set settings at four levels: server, database, table, and column. Each level inherits the default from the level above it unless specified otherwise. To verify the character set of columns in a table, run the SHOW CREATE TABLE tableName; or SHOW FULL FIELDS FROM tableName; command.