Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the reason of garbled code in mysql character conversion". In daily operation, I believe that many people have doubts about the cause of garbled code in mysql character conversion. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubt of "the reason of garbled code in mysql character conversion". Next, please follow the editor to study!

The transcoding process that occurs when data is stored in mysql:

1. Input in the terminal (Terminal, which can be a bash window, or a client tool such as navicat), and the input is encoded by Terminal according to its own characters.

2. The binary stream encoded by Terminal is transmitted to mysql server. Mysql server (mysql engine) decodes the binary stream according to the character setting of the parameter character_set_client.

3. After decoding, mysql server again determines whether character encoding conversion is needed according to the destination table, that is, the character set of table. If the character setting of character_set_client is the same as the character setting when table is defined, no character encoding conversion is required. Otherwise, the conversion is performed and the converted binary stream is stored in a data file (file).

Summary: client-> server (engine)-> file needs to be encoded three times and transformed twice.

The transcoding process that occurs when data is fetched in mysql:

1. Read out the binary data stream from the data file (file) and decode the data stream according to the character setting defined by table.

2. After decoding the secondary data stream with table character, in mysql engine (mysql server), the decoded database stream needs to be encoded again according to the character set setting of the parameter character_set_client, and the encoded secondary database stream is transmitted to the client.

3. The client, that is, the terminal (Terminal), displays the query results according to its own character set code.

Summary: file-> server (engine)-> client needs to be encoded three times and transformed twice.

There may be some doubt that in the above analysis, the data flows between nodes in a binary stream. So why do you need to code for conversion?

1. The conversion between client and server (engine), or codec is for syntax and lexical parsing of the incoming binary stream, otherwise you won't know whether it is insert or update.

2. The conversion between file and server (engine) is to perform character-level operations within the storage engine after reading data from a data file.

After the above analysis, it should be quickly found that the causes of garbled codes are as follows:

1. The coding of the data is inconsistent when it is saved and when it is taken out. For example, the utf8 is used when saving and the GBK is used when taking out.

2. Transcoding is not caused by lossless transcoding. For example, clien is the character_set_client in utf8,mysql server set to gbk, and the character set of the table structure is set to utf8. There will be two code conversions. When client is converted to server, utf8 is converted to gbk, and then server to file, gbk is converted to utf8. Because gbk to utf8 is a lossy code conversion, resulting in garbled code.

Lossless transcoding: if we want to convert the character X represented by code An into the representation of code B, and there is no character X in the glyph set of code B, then we call the conversion lossy.

However, the conversion between any two character set encodings is not lossy, and whether the conversion is lossy depends on the following points:

-whether the converted characters are in two character sets at the same time

-whether the character set can retain its original expression for unsupported characters. (for example, when latin1 encounters a character that it cannot represent, it retains the encoded data of the original character set and skips ignoring the character to process the later data.)

So as long as the client side, MySQL Server character-set-client,table charset's three character sets are exactly the same can guarantee that there will not be garbled.

At this point, the study of "the reasons for garbled mysql character conversion" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.