Modify the character_set_server and collation_server parameters - ApsaraDB RDS

When you migrate or merge the databases of an ApsaraDB RDS for MySQL instance, if the character sets and character collations of the databases are incompatible or the supporting applications have special requirements for character sets and character collations, you can modify the character_set_server and collation_server parameters of the RDS instance.

Background information

The character_set_server parameter specifies the default character set of an RDS instance. The collation_server parameter specifies the collation of the character set.

A character set supports various collations. For example, the UTF-8 character set supports the utf8_general_ci and utf8_bin collations. The Latin-1 character set supports the latin1_general_ci and latin1_bi collations. A character set collation specifies how characters in a character set are compared and sorted.

Collations are categorized into two types: binary collation and language-specific collation.

A binary collation, such as utf8_bin, compares the binary values of characters regardless of the language environments and case sensitivity of the characters. For example, the binary values of characters a and A are different. As a result, they are considered different characters and are sorted based on their binary values. A binary collation sorts characters at a high speed but may not comply with the language behavior.
A language-specific collation, such as utf8_general_ci, takes the language environments and case sensitivity of characters into consideration. For example, characters a and A are considered the same. As a result, a sorting result that complies with the language behavior can be obtained.

In MySQL, character_set_server and collation_server are associated because collations are character set-specific. Different character sets support different collations. Only character sets and collations that are compatible with each other can be used in combination.

Note

In MySQL, utf8 and utf8mb3 are aliases for each other and specify the same character set. utf8mb3 is the alias that distinguishes from utf8mb4. In MySQL 5.7 and later versions, we recommend that you use the utf8mb4 character set as the default character set to support more characters. In some scenarios, such as tests on compatibility with code or databases in earlier versions of MySQL, you still need to use the utf8 or utf8mb3 character set.
You can set the character_set_server and collation_server parameters only to utf8 instead of utf8mb3.

Notes

You can modify the character_set_server and collation_server parameters only on the primary RDS instance but not read-only instances.

Parameter setting rules

If you modify only the character_set_server parameter, the system automatically sets the collation_server parameter to the default value that matches the character_set_server parameter regardless of the current value of the collation_server parameter. For example, if you set the character_set_server parameter to utf8, the system automatically changes the value of the collation_server parameter to utf8_general_ci.
Note
utf8 and utf8mb3 are aliases for each other. As a result, the utf8_general_ci and utf8mb3_general_ci collations are the same. When you set the character_set_server parameter to utf8, the value of the collation_server parameter may be changed to utf8mb3_general_ci.
If you modify only the collation_server parameter, the system checks whether the new value of the parameter matches the value of the character_set_server parameter. If the values of the parameters do not match, you cannot modify the collation_server parameter. For more information, see Parameter values.
If you modify the character_set_server and collation_server parameters at the same time, the system checks whether the new values of the parameters match. If the new values do not match, you cannot modify the parameters. For more information, see Parameter values.
You can set the character_set_server and collation_server parameters only to the values that are listed in the tables in Parameter values.

Important

When you create or modify a custom parameter template, you must take note of the following items:

If you modify only the character_set_server parameter, the default value is used for the collation_server parameter.
If you modify the character_set_server and collation_server parameters at the same time, make sure that the new values of the parameters meet the parameter setting rules.

Parameter values

Default values

MySQL version	character_set_server	collation_server
MySQL 8.0, MySQL 5.7, and MySQL 5.6.	utf8	utf8_general_ci

Parameter matching

Important

The minor engine version of the RDS instance must meet one of the following requirements:

If your RDS instance runs MySQL 8.0, the minor engine version must be 20200331 or later.
If your RDS instance runs MySQL 5.7, the minor engine version must be 20181226 or later.
If your RDS instance runs MySQL 5.6, the minor engine version must be 20221130 or later.

character_set_server	collation_server	Default value of collation_server	Description
utf8	utf8_general_ci	Yes	The parameter matching is the same for RDS instances that run MySQL 8.0, MySQL 5.7, and MySQL 5.6.
	utf8_bin	No
	utf8_unicode_ci	No
	utf8_unicode_520_ci	No
	utf8_general_mysql500_ci	No
latin1	latin1_swedish_ci	Yes	The parameter matching is the same for RDS instances that run MySQL 8.0, MySQL 5.7, and MySQL 5.6.
	latin1_bin	No
	latin1_general_ci	No
	latin1_general_cs	No
gbk	gbk_chinese_ci	Yes	The parameter matching is the same for RDS instances that run MySQL 8.0, MySQL 5.7, and MySQL 5.6.
gbk	gbk_bin	No
gb18030	gb18030_chinese_ci	Yes	The parameter matching is the same for RDS instances that run MySQL 8.0 and MySQL 5.7. gb18030 is not supported for RDS instances that run MySQL 5.6.
	gb18030_bin	No
	gb18030_unicode_520_ci	No
utf8mb4	utf8mb4_0900_ai_ci	Yes	The collations are supported only for RDS instances that run MySQL 8.0 but are not supported for RDS instances that run MySQL 5.7 and MySQL 5.6.
	utf8mb4_0900_as_ci	No
	utf8mb4_0900_as_cs	No
	utf8mb4_0900_bin	No
	utf8mb4_general_ci	RDS instances that run MySQL 8.0: No. RDS instances that run MySQL 5.7 and MySQL 5.6: Yes.	None.
	utf8mb4_bin	No	The parameter matching is the same for RDS instances that run MySQL 8.0, MySQL 5.7, and MySQL 5.6.
	utf8mb4_unicode_520_ci	No
	utf8mb4_unicode_ci	No

References

Character Set Configuration