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

In this issue, the editor will bring you a discussion on how to generate mysql garbled. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Experiment one

one. First of all, in the following cases:

Mysql > show variables like 'character_set_%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | latin1 |

| | character_set_connection | latin1 |

| | character_set_database | latin1 |

| | character_set_filesystem | binary |

| | character_set_results | latin1 |

| | character_set_server | latin1 |

| | character_set_system | utf8 |

| | character_sets_dir | D:\ Programs\ mysql5045\ share\ charsets\ | |

+-+

Build the table and add 3 records: big, ah, love

two. Set character_set_results=utf8

Then: (in the cmd window, cmd window code page 936)

Big-> Lu Trade

A-> captive ridge

Love-> captive Lu

Analyze the code:

Big URV 5927J GBKR B4F3

Piedmont URL 9E93 GBKRU C2B4

Trade URO 8D38JI GBKR C3B3

A U: 963F ·GBKKV B0A2

Captive Uril 63B3 GBKR C2B0

Ridge UGV 5784GBKKRV C2A2

Love URO 7231 GBKR B0AE

Captive Uril 63B3 GBKR C2B0

Lu Ulux 5E90 GBKRU C2AE

three. Change it to set character_set_results=gb2312

It's also garbled.

four. Conclusion:

Garbled code is caused by the expansion of single byte to multi-byte. If B0A2 is stored as a single byte (although it represents 1 Chinese character, but because it is a latin1 single byte, it is considered that B0A2 is two unrelated characters), then if you change character_set_results into utf8 multibyte, then the database mysql will try to expand each single byte into an approximate (I don't know the specific algorithm) double bytes. So the garbled code

On the contrary, when multi-byte to single-byte conversion, there will be no change, only the original 2 bytes represent a character 'B0A2' to represent two characters. -this statement has proved to be wrong.

The content stored in the database (on disk, in memory) will not be affected by character_set_, but will be affected by character set conversion in the process of submission and query.

Experiment two

one.

Create table y (id int, name char (4)) default charset gb2312

two. If you insert a Chinese character without changing that the default character_set_ is latin1, the garbled code will be displayed.

three. Change it to set names gb2312 and display it fine (in the cmd window, cmd window code page 936)

four. I thought that, as in conclusion 2 of the above experiment 1, "there will be no change when multi-byte is converted to single-byte." So at first I thought that after set names gb2312, changing character_set_results to latin1 would show that there would be no problem. Result,

One Chinese character shows a question mark; two Chinese characters show the garbled code of two question marks (it is estimated that one question mark represents one character). In other words, after changing to character_set_results = latin1, the multi-byte data storage, when converting to a single-byte representation, mysql "shrunk" the proposed information, converting two bytes into one byte.

five. How to prevent mysql from shrinking? I thought of the result of character_set_results = binary;, and it sure enough showed normal.

PS

Developed applications that use mysql correspond to character sets that independently use their own character_set_client

The cmd window logs in to mysql as an independent application with its own character_set_client variables

Similarly, different cmd windows all have their own character_set_client variables

Experiment 3 0716 Universe 2010

one. Create a table of the default character set utf8 (in navicat, code page 65001 under the interface of utf8) and insert utf8-encoded Chinese characters; University

two. Switch to mysql console (code page 936)

three. Set names gbk; then displays the table you just created, can it be correct and realistic? -Yes! Of course, only the character_set_results into gbk, can also be displayed normally

Experiment 4

one. Mysql console (code page 936) creates a table x3 (name char (32)), the default character set default charset gbk

two. Default environment variabl

| | character_set_client | latin1 |

| | character_set_connection | latin1 |

| | character_set_database | latin1 |

| | character_set_filesystem | binary |

| | character_set_results | latin1 |

| | character_set_server | latin1 |

| | character_set_system | utf8 / / I do not know whether it will affect the following procedures and analysis |

If character_set_client character_set_connection character_set_results is latin1, insert data: insert x3 values ('big')

Display: ERROR 1406 (22001): Data too long for column 'name' at row 1

three. Set character_set_client=gbk; and then insert x3 values ('big'); there is no problem with insertion, but it is obvious that the data has been damaged by (character_set_connection=latin1) conversion

four. No matter whether character_set_results is set to gbk or not, the result cannot be displayed normally.

five. Set names gbk; can be plugged into reality. And at this point, there is no problem with the display of a table of the uf8 character set (experiment 3). And it is no problem to make a connection query.

six. Of course, set names utf8, if you are on a utf8 software interface, it is OK to display the output (verified by navicat)

seven. If set to set names binary. In the display interface of the 936 code page, you can see that x3 can still be normal reality, but the table built like experiment 3 can not be displayed properly.

-

Point 2 of analysis: Data too long for column 'name' at row 1

My char is long enough and the insert data is short enough, so it's not that the data is too long. That means the hint is wrong.

I know that if the default character set of table x3 is latin1, there is no problem with insertion (it has always been played this way); this is because, although the input mysql console code page is 936, but because the three main environment variables character_set_c% are all latin1, mysql thinks that insert x3 values ('big') enters 2 characters (of course, if you enter from the utf8 interface, you may think of it as entering 3 characters). Naturally, the storage is also 2 characters. When it is displayed, it also displays 2 characters, but the 936 code page naturally combines these two characters and displays them as Chinese characters (a common phenomenon in the early environment).

What happens when the default character set becomes gbk? I don't know.

Experiment 5

A very shit problem arises: 936 console

Environmental variables such as experiment 1. 1.

Mysql > set names latin1

Query OK, 0 rows affected (0.00 sec)

Mysql > create table x4 (

-> name char (32) primary key)

Query OK, 0 rows affected (0.09 sec)

Mysql > drop table x4

Query OK, 0 rows affected (0.06 sec)

Mysql > create table x4 (

-> name char (32) primary key) default charset utf8

Query OK, 0 rows affected (0.10 sec)

Mysql > insert x4 values ('na')

Query OK, 1 row affected (0.04 sec)

Mysql > create table x5 (

-> name char (32) primary key) default charset gbk

Query OK, 0 rows affected (0.09 sec)

Mysql > insert x5 values ('Nai')

ERROR 1406 (22001): Data too long for column 'name' at row 1

Mysql >

In conclusion, I really draw a conclusion on the third point of the analysis in experiment 4. Character_set_system utf8 related ~ ~

The above is the discussion of how to generate mysql garbled code shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.