[Go] about the navicat and MySQL character set is not uniform occurrence of Chinese garbled problem

Original link: about Navicat and MySQL character set is not uniform occurrence of Chinese garbled problem

Recently encountered a string of MySQL Chinese garbled problem, the background is this:

Before this, the server installed a good MySQL immediately after re-configuration of the character set as UTF8, after the data import with Navicat, found that Chinese characters are garbled after import, and then check the forum is

Solve the problem in the following way: When establishing a connection, select UTF-8, and remove the tick before using the MySQL character set.

The properties of the database are set to:

The Chinese characters in the Ok,navicat are normal. So, happy to carry out data import, all feel perfect ~

Next I use the simplest mysqldump method in the command line to backup, backup success, because it is the test data, I open the backup good file a look, is garbled! The command line then directly into the database query,

Sure enough query results are garbled! I used the command line to build a test table, insert Chinese data, look up the table, the display is correct; navicat check table, garbled. What's the situation ~ clearly both sides are UTF-8

Encoding method.

10,000 of the inner camel ran over ...

OK Let's step through the problem now:

First, open the/etc/my.cnf file to see if the character set is properly configured and sure enough, the error is written under [mysqld] Default-character-set=utf8

The correct way to configure:

Back up the my.cnf file first, and then vim/etc/my.cnf insert the following lines

[Client]

Default-character-set=utf8

[Mysqld]

Character-set-server=utf8 #注意这行不要写成 Default-character-set=utf8

[MySQL]

No-auto-rehash

After saving the service mysqld Restart started successfully, before the data test, garbled problem still exists. Can the character set on both sides really be different?

Looked up some information to know: As long as the text is involved in the place, there will be a character set and encoding method. For the MySQL database system, the user typed an SQL statement from the MySQL client side and passed it through TCP/IP to

The MySQL server process, to the file that is eventually deposited on the server side, involves character storage for each link. Where character storage is concerned, character set encoding is involved.

We'll use show variables like ' char% '; and Showvariables ' collation_% '; check it out:

Running commands in MySQL, navicat, respectively;

Some character sets and proofreading system variables are different on both sides.

So far, I have found the key to the problem.

Finally, let's solve the problem, I have two ways:

① we use MySQL configuration as a standard. Run the following command in Navicat:

Setcharacter_set_client= UTF8;

Setcharacter_set_connection =utf8;

Setcharacter_set_results=utf8;

Then look at the character set, which is the same on both sides. To test, garbled problem solved.

② ensure that the MySQL character set is properly configured, use the MySQL character set in the Navicat connection properties.

Finally found himself in the pit.

In any case, the final problem is solved and the work can be enjoyable again.

Here are some of the articles I refer to:

Http://www.douban.com/note/268110263/?type=like

Http://www.linuxidc.com/Linux/2015-04/116550.htm

[Go] about the navicat and MySQL character set is not uniform occurrence of Chinese garbled problem