Deep Mysql Character Set setup graphic version _mysql

There is a character set translator between the MySQL client and the MySQL server.

Character_set_client =>GBK: The converter knows that the client sent the code in GBK format.

CHARACTER_SET_CONNECTION=>GBK: Converts data transferred from client to GBK format

Character_set_results =>GBK:

Note: The above three character sets can be set uniformly using the set names GBK

Example:

CREATE TABLE Test (

Name varchar () not NULL

) charset UTF8; #这里的utf8表示服务器端的字符编码

First, insert a piece of data into the datasheet test

Inert into test values (' test ');

, the data "test" is saved in the database in the "UTF8" format

Process:

First, through the MySQL client, the data sent to the MySQL server, after the character set converter, because the character_set_connection value is GBK, so will send the data from the client to GBK format, followed by, When the character set converter transmits data to the server, it discovers that the server is saving the data in UTF8, so that the data is automatically converted from GBK to UTF8 format inside it.

When will there be garbled?

The
client's data format does not match the declared character_set_client

By the header (' Content-type:text/html;charset=utf8 '), the client's data is converted into a UTF8 format, and as the data passes through the "character set converter", because of the CHARACTER_SET_CLIENT=GBK, Character_set_connection is also equal to GBK, so the data sent from the client (in fact, the UTF8 format) is not converted into a format.

However, the character set converter when the data sent to the server, found that the server to the format is UTF8, so the current data as a GBK format to be processed, so as to turn into UTF8 (but this step is actually wrong ... ）。

2. Result and the client page does not meet the time

The format of the returned result is set to UTF8, but the client-accepted format is GBK, so garbled

With show character set syntax, you can display all of the available character sets

Latin Character Set

Note: The MaxLen column shows the maximum number of bytes used to store one character.

UTF8 Character Set

GBK Character Set

When will you lose data?

In contrast to the above three images, it is possible to know that each character set has the largest number of bytes to store a single character, utf8 maximum and Latin minimum. Therefore, when the character set converter, if improper handling, will result in data loss, and can not be retrieved.

Like what:

When you change the value of Character_set_connection to Lantin

GBK data sent from the client is converted to LANTIN1 format because the data in the GBK format takes up more characters, resulting in data loss

Summarize:

Character_set_client and character_set_results are generally consistent, because one represents the data format sent by the client, and the other represents the data format accepted by the client in order to avoid data loss, you need to let character_set_ The connection character encoding is greater than the character_set_client character encoding