In-depth MySQL Character Set settings

Source: Internet
Author: User

In MySQL Client and MySQL There is a character set converter between servers.   Character_set_client => GBK: the converter knows that the client sends the GBK encoding character_set_connection => GBK: converts the data sent from the client to the GBK character_set_results => GBK: Note: The preceding three character sets can be set using set names GBK. Example: Create Table Test (name varchar (64) not null) charset utf8; # utf8 indicates the character encoding on the server. First, insert a data inert into test values ('test') to the data table test. Then, the data "test" is stored in the database in the "utf8" format:

 

First, the data is sent to the MySQL server through the mysql client. When the character_set_connection value is GBK, the data sent from the client is converted to GBK format, when the character set converter transmits the data to the server, it finds that the server saves the data by utf8. Therefore, the data is automatically converted from GBK to utf8 within the server.   When Will garbled characters appear?
    1. Client data format and declaredCharacter_set_client does not match
Use the header ('content-type: text/html; charset = utf8'); to convert the client data to utf8 format. When the data passes through the "Character Set converter, because character_set_client = GBK and character_set_connection is equal to GBK, the data transmitted from the client (in fact, in utf8 format) will not be converted. However, when the character set converter sends data to the server, it finds that the server requires utf8 format, so the current data will be processed as GBK format, and then converted to utf8 (however, this step is actually wrong ...). 2. When the result does not match the client page

Set the format of the returned result to utf8, but the format accepted by the client is GBK, so garbled characters will appear.   Show Character Set syntax can be used to display all available character sets Latin character sets

Note: The maxlen column displays the maximum number of bytes used to store a single character. Utf8 Character Set

GBK character set

  When Will data be lost? Compared with the above three images, we can know that in each character set, the maximum number of bytes used to store a character is different, utf8 is the maximum, and Latin is the smallest. Therefore, when a character set converter is used, improper processing may cause data loss and cannot be recovered. For example: Set When the character_set_connection value is changed to Lantin

The GBK data sent from the client is converted to the lantin1 format, because the GBK format occupies a large number of characters, resulting in data loss.

Summary:
    1. Character_set_client and character_set_results must be consistent in general, because one represents the data format sent by the client, and the other represents the data format accepted by the client.
    2. To avoid data loss, the character_set_connection character encoding must be greater than the character_set_client character encoding.
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.