A summary of the solution for MySQL Chinese garbled problem

The reason is that many times, in order to secure, do not allow MySQL management tools to connect the formal environment on the line, in this case, you can not rely on the MySQL management tools to transform the code to solve the problem of Chinese garbled.
In this case, you can only remotely connect to MySQL server via putty or SECURECRT, and then export the MySQL database via the MySQL command interface, and do other coding conversion operations. That's the kind of environment I'm facing right now.

Now, to describe my data, I need to export the Chinese garbled datasheet 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; + ——————— + —————————————————————————————————————————————————————————————————————————————————————————————————————— ————————————————————————— –+ | Table | Create Table | + ——————— + —————————————————————————————————————————————————————————————————————————————————————————————————————— ————————————————————————— –+ | User_agreement_info | CREATE TABLE ' user ' ( ' id ' int (one) unsigned not NULL auto_increment, ' UID ' bigint (unsigned) not NULL, ' Realname ' char (not NULL), ' Id_type ' smallint (one) unsigned not NULL, ' Id_num ' char (#) DEFAULT NULL, ' Create_time ' int (a) unsigned DEFAULT NULL, PRIMARY KEY (' id '), KEY ' uid ' (' uid ') ) Engine=myisam auto_increment=129287 DEFAULT Charset=utf8 | + ——————— + —————————————————————————————————————————————————————————————————————————————————————————————————————— ————————————————————————— –+ 1 row in Set (0.02 sec) #查询数据乱码, look below: Mysql> SELECT * from user limit 10; +--+ ———— + ————————— + ——— + —————— –+ ————-+ | id | uid | realname | id_type | id_num | create_time | +--+ ———— + ————— ———— + ——— + —————— –+ ————-+ | 23 | 1000001229 |è€é™ˆ| 1 | 410101234567891234 | 1272619237 | | 2 | 1000001207 |瞋文鉴| 1 | 320211198511261933 | 1272546559 | | 3 | 1000001208 |è ' ‹å®¶é "‹| 1 | 513023198808294915 | 1272547009 | | 4 | 1000001209 | zhaojing | 1 | 320822198704286120 | 1272550654 | | 5 | 1000001210 |阮å°æ¦| 1 | 31020619840214283X | 1272562857 | | 6 | 1000001211 |é» ' 夜精çµ| 1 | 412723798204103835 | 1272588671 | | 7 | 1000001212 |谢勇| 1 | 330722198408168210 | 1272591799 | | 8 | 1000001213 |é ' µæ˜žèš³| 3 | 0621316 | 1272592840 | | 9 | 1000001215 |瞋维纪| 1 | 330382198611030393 | 1272592959 | | 10 | 1000001216 |è°ˆå®| 1 | 430721198309272802 | 1272595142 | +--+ ———— + ————————— + ——— + —————— –+ ————-+ rows in Set (0.00 sec) #数据库编码设置情况: 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 is the problem?

First, let's look at what the data is encoded in:

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 | Network Home Front | 1 | 513023198808294915 | 1272547009 | | 4 | 1000001209 | zaojing | 1 | 320822198704286120 | 1272550654 | | 5 | 1000001210 | Xiao Lin | 1 | 31020619840214283X | 1272562857 | | 6 | 1000001211 | Night Elves | 1 | 412723798204103835 | 1272588671 | | 9 2 1000001212 | Phoenix Dance | 1 | 330722198408168210 | 1272591799 | | 8 | 1000001213 | Shaomingfang | 3 | 0621316 | 1272592840 | | 9 | 1000001215 | Wang Weiji | 1 | 330382198611030393 | 1272592959 | | 10 | 1000001216 | Talk about Macros | 1 | 430721198309272802 | 1272595142 | +--+ ———— + ———— –+ ——— + —————— –+ ————-+ Rows in Set (0.01 sec)

It is now possible to determine that the data in this table is latin1 encoded.

We know that if you want to MySQL, the Chinese normal display, you must keep the code consistent, we see in the database after the implementation of set names Latin1, the Chinese can be normal display, then we will follow this way to 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, which is equivalent to executing set names latin1 in the database before the data is exported;
This data is in the form of SQL script on the server, using VIM to see the file/tmp/user.sql found or garbled, this is why? Here's why:

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=

And the coding of your connection tool, check out what code your putty or SECURECRT connection uses, and these different encodings are the reason you use VIM to see the Chinese display garbled.

No, we use the SZ command to download the/tmp/user.sql to the local, which is your Windows host.

Then use Emedtor or Uedtor,notepad++,vim can, open, found that Chinese can be normal display, if not normal display, then look forward to see what is wrong, modify the contents of the User.sql, set names latin1; Modify to set names UTF8, and then save as a UTF8 encoded form.

Then use RZ to upload to the MySQL server, again using VIM to open, found Chinese normal display.

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

Login MySQL view, Chinese display normal.

and then I came up with a simpler way

Login to MySQL--->

Enter the appropriate database--->

code is as follows

copy code

Enter command: Show variables like '%char% '; Get: +--------------------------+--------------------------+ | 　　　　 variable_name | Value    | +--------------------------+--------------------------+ | character_set_client   UTF8 | cha Racter_set_connection   UTF8 | character_set_database   latin1 | Character_set_filesystem bin ary | character_set_results UTF8 | character_set_server UTF8 | character_set_system   u Tf8 | character_sets_dir d:mysqlsharecharsets +--------------------------+--------------------------+ /td>

If this happens, you need to change the database encoding: two methods, the first one is a fix, the second is only valid for the current connection, disconnect, and revert to the state before the change

Methods 1:alter database name CharSet UTF8;
Methods 2:set character_set_database = UTF8;

Re-use the command:

code is as follows

copy code

Show variables like '%char% '; +--------------------------+--------------------------+ | 　　　　 variable_name | Value    | +--------------------------+--------------------------+ | character_set_client   UTF8 | cha Racter_set_connection   UTF8 | character_set_database   UTF8 | Character_set_filesystem binar Y | character_set_results utf8 | character_set_server UTF8 | character_set_system   UTF 8 | Character_sets_dir d:mysqlsharecharsets +--------------------------+--------------------------+

The encoding is already consistent, but inserting Chinese characters into the tables in the database will still report: ERROR 1366 (HY000)

The code is as follows	Copy Code
---> Set character_set_client = GBK; Sets the encoding to be received at the time of insertion as GBK, so that you can insert Chinese

At this point, the Chinese characters can be inserted, but the query result is still garbled, and then change a place can:

The code is as follows	Copy Code
---> Set character_set_results = GBK; The encoding of the output result is GBK

Note: This setting is only related to the current connection, disconnect, these settings restore the database default settings state, so if necessary, each connection must be set!

In addition: Error 1366 (HY000) errors occur mainly when the console (cmd/Black window) executes SQL statements to insert Chinese.
Insert with Mysql-front software does not report character problems, Java through JDBC Connection database execution executeupdate ("INSERT statement") Chinese can also successfully insert data and no error!

PHP to solve the garbled problem only in the connection after joining Mysql_query ("Set names ' GBK '") on the ok!

Such as:

code is as follows	copy code
$conn = Mysql_ Connect ("localhost", "root", ""); mysql_query ("Set names ' GBK '");