RDS for MySQL character set related instructions

Source: Internet
Author: User
Tags mysql commands aliyun

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.