Kill MySql garbled characters in the command line window and mysql in the window

Kill MySql garbled characters in the command line window and mysql in the window

In the evening, I encountered a huge pain problem when operating mysql in the dos window ------> Chinese verification code --> _ -->, so I found the information and found out how to solve the garbled problem ,, note.

Create a table

create table student(    id int,    name varchar(20),    chinese float,    english float,    math float);

Insert data to a table (including Chinese characters)

Insert into student (id, name, chinese, english, math) values (1, 'Lee dog', 88, 93.5, 78 );

The result is as follows:

A Data too long for column 'name' at row 1 error is reported. Why? My field is 20 characters long. The word "Li" must be enough. Why is it too long !!!! In fact, in this case, even if you only have one Chinese character, this error will be reported .... Why? -----> Chinese Garbled text !!!

The reason for garbled characters in the servlet is the same. The encoding method is different between submission and acceptance ....

We know that the default doscommand line encoding method in the Chinese version of win7 is gbk, and the default encoding method of the mysql server we installed is utf8. In this way ,, the two encoding methods are different... The dos window is used to submit Chinese data to the mysql server. One is committed in gbk encoding and the other is received in utf8 decoding. No garbled code is generated.

Believe it or not, let's look at the default mysql encoding-input in the dos window

show variables like 'character%';

Check the encoding status of the mysql server, as follows:

However, these encoding formats are only the encoding modes that mysql considers as your system, but they are not the case. It is manifested in: client encoding --> mysql thinks utf8, but in fact your doscommand line is under my tianchao win7, then you are the gbk encoding format, not utf8; others are the default utf8. Here we can see that the client is gbk, the connection method is utf8, the server storage method is utf8, And the return method is utf8 ,, the reason for garbled characters is as follows:> the two encoding methods are different. The two encoding methods use gbk to encode the codes, and the results are encoded with u8. Then, the codes are sent with utf8 and received with utf8. So how can this problem be solved?

There are two solutions to Garbled text: temporary and thorough.

Temporary method: Enter "set names gkk;" in the dos line and check the encoding method as follows:

Clearly tell the client that my dos line is typed in by gbk, and you must encode it by gbk; tell the Connection Bridge that my data is by gbk, and you must transmit it to me by gbk; tell the server that my dos line is gbk. You need to use gbk to return the data I saved to me. Now we can insert the line of data with Chinese characters,

As you can see, the garbled problem no longer exists .... But at the beginning, I said that the method in this section is temporary. Why? To open another dos window and view all the encoding methods:

At this time, you can insert data with Chinese characters to ensure that the error is still the same... Why? Because "set names gbk;" only works for the current client (that is, the dos window .. Opening another window will not work, so this method will not cure the problem. If this happens every time, you cannot get tired !! (Don't tell me that you are using Visual mysql operating software. Our current context is that you don't know Visualization at all.) How can we solve this problem once and for all?

Complete Solution: Modify the mysql configuration file ---> my. ini

Find the my. ini file under your mysql installation directory, open it in notepad, find ----- default-character-set = utf8, and change utf8 to gbk

Restart mysql. Oh YEAH !!

 

Let's talk about why garbled characters are fixed after gbk is changed .. This involves three data conversion processes: encoding, decoding, and transcoding.

Encoding is the process of compiling characters into binary data;

Decoding is the process of parsing binary data into characters;

Transcoding is the process of converting a character from binary data represented by character set a to binary data represented by Character Set B.

When the client sends gbk data to the server through the Connection Bridge, the two-party shard set is actually different. The database receives gbk, and the server needs to store it in utf8, in this process, if you do not do anything, garbled Code cannot be solved .. In fact, in this process, the server itself performs a transcoding operation: Convert the received gbk data into utf8 and store it in the database, then, when you query the data, it automatically converts the utf8 data to gbk and returns it to you. So garbled characters are missing.