RDS for MySQL character set related instructions

Https://help.aliyun.com/knowledge_detail/41706.html?spm=5176.7841698.2.9.F5YjI5

• Word Fu She naming rules
• Character Set related MySQL commands
• How the console modifies character set parameters (Character_set_server)
• Ways to modify the database character set using SQL statements
• How to ensure the correct setting of database character set encoding

1. Word Fu She naming rules

Begins with the character set name corresponding to character Fu She, ending with _ci (case insensitive), _cs (case sensitive), _bin (comparison by encoded value, case sensitive).

For example: When the collation_connction of a session is set to the word Fu She Utf8_general_ci, the character a and character a are equivalent;

And when it is set to Utf8_bin, the character a and character a are not equivalent.

Please refer to the following example:

2. Character Set related MySQL command

ShowGlobalVariablesLike'%char% '; #查看RDS实例字符集相关参数设置ShowGlobalVariablesLike' coll% '; #查看当前会话字符序相关参数设置ShowCharacterSet #查看实例支持的字符集ShowCollation #查看实例支持的字符序ShowCreateTable table_name \g #查看表字符集设置ShowCreateDatabase database_name \g #查看数据库字符集设置show create procedure procedure_ Name \g #查看存储过程字符集设置 show procedure status \g #查看存储过程字符集设置 alter  Database db_name default charset UTF8; #修改数据库的字符集 create database db_name character set UTF8; #创建数据库时指定字符集 alter table Tab_name default charset UTF8 collate utf8_general_ci ; #修改表字符集和字符序                

Example:

3. The console modifies the character set parameter (Character_set_server) method:

In the RDS instance console parameter settings, click on the "pen" icon,:

"OK", "Submit Parameters":

Note: After this parameter is modified, only the database that opened the high-privileged account is subsequently created. is not valid for the current database.

4. Ways to modify the database character set using SQL statements:

The syntax is as follows:

To modify a library:Alter DATABASELibrary nameCHARACTER SETCharacter Set nameCOLLATESort rule name; Modify Table:Alter TABLETable nameCONVERT To CHARACTER SET Character Set name COLLATE Sort Rule Name; Modify a column:alter  table table name modify column Name field type  character Span class= "Hljs-keyword" >set   Character set name   collate  Collation name             

Example: The following three SQL changes the library DBSDQ, table tt2, table TT2 in the C2 column to the UTF8MB4 character set, the code is as follows:  

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

:

Attention:

• When a column is modified, all rows in the current column are immediately converted to the new character set;
• ALTER TABLE will lock the CAD data (metadata lock), as described in:
  Https://help.aliyun.com/knowledge_detail/6697124.html

5. How to ensure that the database character set encoding is correct:

The character set in the database is a point that needs to be considered in the database design process, and the user needs to be considered in terms of your business scenario, user data, and so on.

• View parameters for setting character sets in the database
  
  Code:

• show variables like ‘%character%‘;

  Results

The following parameters must ensure that all parameters except Character_set_filesystem are uniform in order to ensure that the character encoding does not appear garbled.

Character_set_client, Character_set_connection, and character_set_results are all settings for the client

Character_set_system, Character_set_server, and Character_set_database refer to server-side settings.

The priority for these three server-side parameters is:
Column-level character set > table-level Character Set > Character_set_database > Character_set_server > Character_set_system
The character encoding at the column level is the highest priority on the server side.

• How can we ensure that these fields are consistent?

Client Character set: The corresponding character_set_client, character_set_connection, and character_set_results parameters can be defined in the following manner. For example:

set names utf8;

Server-side character set: Because of the different levels of the settings, the changes are not provided for Character_set_system at first, but because of their lowest priority it has little impact.

See item 3rd above for character_set_server changes. The description is no longer repeated here.

For Character_set_database, it is specified at the time the database was created,

做到上述的设置之后基本上可以保证字符编码不会出现乱码，对于在代码中设置客户端的字符编码的时候建议也可以通过 set names XXX; 来修改客户端的设置，然后再进行相关的操作。

If the problem is not resolved, please contact after-Sales technical support.

RDS for MySQL character set related instructions