Common Causes of mysql Chinese garbled characters

In mysql applications, the problem of mysql Chinese garbled characters is not unexpected, but the encoding problem is caused by the server encoding or latin1 or both database encoding and program page encoding is not well handled, you only need to solve these two mysql Chinese problems.

I have previously written an article about how to deal with mysql Chinese garbled characters. I found it is necessary to write this article again.

The reason is that for security purposes, the official environment on the connection line of the mysql management tool is not allowed. In this case, the mysql management tool cannot be used to convert the encoding to solve Chinese garbled characters.
In this case, you can remotely connect to the mysql server through putty or secureCRT, export the mysql database through the mysql command interface, and perform other encoding and conversion operations. The current environment is like this.

Now, to describe my data, I need to export a Chinese garbled data table account. user:

The Code is as follows:

Copy code

Mysql> show create database account; + ---- + -------------------------------- + | Database | Create Database | + ---- + -------------------------------- + | Account | create database 'account '/*! 40100 default character set utf8 COLLATE utf8_unicode_ci */| + ---- + -------------------------------- + 1 row in set (0.00 sec) Mysql> show create table user; + ------- + Response-+ | Table | Create Table | + ------- + Response-+ | User_agreement_info | create table 'user '( 'Id' int (11) unsigned not null AUTO_INCREMENT, 'Uid' bigint (21) unsigned not null, 'Realname' char (32) not null, 'Id _ type' smallint (11) unsigned not null, 'Id _ num' char (32) default null, 'Create _ time' int (10) unsigned default null, Primary key ('id '), KEY 'uid' ('uid ') ) ENGINE = MyISAM AUTO_INCREMENT = 129287 default charset = utf8 | + ------- + Response-+ 1 row in set (0.02 sec) # Query data with garbled characters: Mysql> select * from user limit 10; + -- + ---- + --------- + --- + ------- + ----- + | Id | uid | realname | id_type | id_num | create_time | + -- + ---- + --------- + --- + ------- + ----- + | 23 | 1000001229 | ⠁ é™California | 1 | 410101234567891234 | 1272619237 | | 2 | 1000001207 | ç æ-‡ é‰ | 1 | 320211198511261933 | 1272546559 | | 3 | 1000001208 |?®¶ É "counter | 1 | 513023198808294915 | 1272547009 | | 4 | 1000001209 | zhaojing | 1 | 320822198704286120 | 1272550654 | | 5 | 1000001210 | é Hei®°  ­ ¦ | 1 | 31020619840214283X | 1272562857 | | 6 | 1000001211 | é» '{%%%%%%%^| 1 | 412723798204103835 | 1272588671 | | 7 | 1000001212 |? | 1 | 1 | 330722198408168210 | 1272591799 | | 8 | 1000001213 | é ‚ µ˜ %****** | 3 | 0621316 | 1272592840 | | 9 | 1000001215 | ç ‹ ç» ç° California | 1 | 330382198611030393 | 1272592959 | | 10 | 1000001216 |?®California | 1 | 430721198309272802 | 1272595142 | + -- + ---- + --------- + --- + ------- + ----- + 10 rows in set (0.00 sec) # Database encoding settings: Mysql> show variables like '% char % '; + --------- + -------------- + | Variable_name | Value | + --------- + -------------- + | Character_set_client | utf8 | | Character_set_connection | utf8 | | Character_set_database | utf8 | | Character_set_filesystem | binary | | Character_set_results | utf8 | | Character_set_server | utf8 | | Character_set_system | utf8 | | Character_sets_dir |/usr/local/mysql/share/mysql/charsets/| + --------- + -------------- + 8 rows in set (0.02 sec)

What's the problem?

First, let's look at the data encoding:

The Code is as follows:

Copy code

Mysql> set names latin1; Query OK, 0 rows affected (0.02 sec) Mysql> select * from user_agreement_info limit 10; + -- + ---- + ----- + --- + ------- + ----- + | Id | uid | realname | id_type | id_num | create_time | + -- + ---- + ----- + --- + ------- + ----- + | 23 | 1000001229 | Chen | 1 | 410101234567891234 | 1272619237 | | 2 | 1000001207 | Wang jiawen | 1 | 320211198511261933 | 1272546559 | | 3 | 1000001208 | wangjiafeng | 1 | 513023198808294915 | 1272547009 | | 4 | 1000001209 | zaojing | 1 | 320822198704286120 | 1272550654 | | 5 | 1000001210 | Kobayashi | 1 | 31020619840214283X | 1272562857 | | 6 | 1000001211 | night genie | 1 | 412723798204103835 | 1272588671 | | 7 | 1000001212 | fengwu | 1 | 330722198408168210 | 1272591799 | | 8 | 1000001213 | Yan mingfang | 3 | 0621316 | 1272592840 | | 9 | 1000001215 | Wang Weiji | 1 | 330382198611030393 | 1272592959 | | 10 | 1000001216 | tan Hong | 1 | 430721198309272802 | 1272595142 | + -- + ---- + ----- + --- + ------- + ----- + 10 rows in set (0.01 sec)

Now we can confirm that the data in this table is latin1 encoded.

We know that if you want mysql to display Chinese properly, you must keep the encoding consistent. We can see that after the set names latin1 is executed in the database, the Chinese will be displayed normally, in this way, we can export data.

The Code is as follows:	Copy code
[Root @ sh-db1 tmp] #/usr/local/mysql/bin/mysqldump-uroot-opt-default-character-set = latin1-p654321 account user>/tmp/user. SQL

Note that the export parameter-default-character-set = latin1 is equivalent to executing set names latin1 in the database before data export;
In this way, the data is stored on the server in the form of SQL scripts. It is still garbled when you use vim to view the file/tmp/user. SQL. Why? The reason is as follows:

The Code is as follows:	Copy code
[Root @ sh-db1 tmp] # locale LANG = en_US.UTF-8 LC_CTYPE = "en_US.UTF-8 ″ LC_NUMERIC = "en_US.UTF-8 ″ LC_TIME = "en_US.UTF-8 ″ LC_COLLATE = "en_US.UTF-8 ″ LC_MONETARY = "en_US.UTF-8 ″ LC_MESSAGES = "en_US.UTF-8 ″ LC_PAPER = "en_US.UTF-8 ″ LC_NAME = "en_US.UTF-8 ″ LC_ADDRESS = "en_US.UTF-8 ″ LC_TELEPHONE = "en_US.UTF-8 ″ LC_MEASUREMENT = "en_US.UTF-8 ″ LC_IDENTIFICATION = "en_US.UTF-8 ″ LC_ALL =

It also has something to do with the encoding of your connection tool. Check the encoding used for Your putty or secureCRT connection. The different encoding causes you to use vim to view Chinese characters with garbled characters.

It doesn't matter. We use the sz command to download/tmp/user. SQL to your local host, that is, your windows host.

You can use emedtor, uedtor, notepad ++, and vim to enable it and find that the Chinese language can be displayed normally. If it is not displayed properly, check whether something is wrong and modify the user. set names latin1 in SQL; change it to set names utf8; then save it as the UTF-8 encoding format.

Use rz to upload data to the mysql server and use vim again.

The Code is as follows:	Copy code
[Root @ sh-db1 tmp] #/usr/local/mysql/bin/mysql-uroot-S/tmp/mysql3306.sock-p654321 account <user. SQL

Log on to mysql and check that Chinese characters are displayed normally.

At last, I summarized some Chinese garbled characters.

1. Problems with server settings, such as staying at latin1
2. table language setup problems (including character and collation)
3. Connection language settings for client programs (such as php)
We strongly recommend that you use utf8 !!!!
Utf8 is compatible with all characters in the world !!!!
4. Add a "set names utf8" or "set names gbk" command in the webpage code to tell MySQL to use the link content
Utf8 or gbk