Problems with Chinese data in MySQL tutorial

This article mainly introduces issues related to mysql Chinese data. I hope it will be helpful to friends in need!

Recommended reference tutorial: "mysql tutorial"

Chinese data problemwith Chinese data in MySQL tutorial>

The essence of Chinese data problem is the problem of character set.

Since computers only recognize binary data, and humans are more inclined to recognize characters (symbols), a correspondence between binary and characters is needed, which is Character set.

When we insert Chinese data into the server through the client of the MySQL database, it may fail. The reason may be that the character set settings of the client and the server are different, for example:

• The character set of the client is gbk, then one Chinese character corresponds to two bytes;

• The character set of the server is utf8, one Chinese character corresponds to three bytes.

This will obviously cause problems during the encoding conversion process, resulting in failure to insert Chinese data.

Since some characteristics of all database servers are maintained through server-side variables, the system will first read its own variables to see the specific manifestations. In this case, we can use the following statement to check which character sets the server recognizes:

-- 查看服务器识别的全部字符集show character set;

Through the above query, we will find: The server is omnipotent, and its All character sets are supported.

Since the server supports so many character sets, there will always be a character set that is the server's default for dealing with clients. Therefore, we can check the server's default external processing character set through the following statement:

-- 查看服务器默认的对外处理的字符集show variables like &#Problems with Chinese data in MySQL tutorial9;character_set%&#Problems with Chinese data in MySQL tutorial9;;

• Note Problems with Chinese data in MySQL tutorial: Server's default client sent The data character set is utf8;

• . Note 2: The connection layer character set is utf8;

• Annotation Problems with Chinese data in MySQL tutorial: The character set of the current database is utf8;

• Annotation Problems with Chinese data in MySQL tutorial: The server’s default character set for external processing is utf8.

Through the above query, we will find: The server’s default external processing character set is utf8.

Then In turn, we are checking the character set supported by the client through the client's properties:

Obviously, we have found the source of the problem, which is indeed: Client The supported character set is gbk, while the default character set for external processing by the server is utf8, so there is a contradiction.

Now that the problem has been found, the solution is: Modify the default character set received by the server to gbk.

-- 修改服务器默认接收的字符集为 GBK（不区分大小写）set character_set_client = gbk;

This way If so, when we insert Chinese data again, the insertion will be successful! But, when we checked the data, we found another problem, that is, the Chinese data we inserted before was garbled! But this is normal, because when querying, the source of the data is the server (utf8), and the client parses the data, and the client only recognizes data in the gbk format and displays garbled characters. That’s as expected!

Therefore, the solution is: Modify the data character set provided by the server to the client to gbk.

-- 修改服务器给客户端的数据字符集为 GBK（不区分大小写）set character_set_results = gbk;

As shown in the picture above, the problem of inserting Chinese data into the server has been solved!

In addition, the SQL statement we used before:

-- 修改的只是会话级别，即当前客户端当次连接有效，关闭后失效set 变量 = 值;

In this case, every time we restart the client, we have to reset it in sequence, which is troublesome, so we can use the quick setting The method is:

set names 字符集;

For example,

/**
* 恒等于 set character_set_client = gbk;
* 恒等于 set character_set_results = gbk;
* 恒等于 set character_set_connection = gbk;
*/set names gbk;

means that the above statement will change the values ​​of three variables at the same time. Among them, connection is the connection layer, which is the middleman for character set conversion. If it is consistent with the character sets of client and results, it will be more efficient and inconsistent. It doesn't matter either.

The above is the detailed content of Problems with Chinese data in MySQL tutorial. For more information, please follow other related articles on the PHP Chinese website!