MySQL Chinese garbled problems in various solutions

Modify MySQL configuration to solve garbled

First of all:

With show variables like "%colla%"; "Varables" and "%char%"; these two commands view the database and server-side character set settings

If the view is gbk2312, or GBK, then you can only support Simplified Chinese, traditional and some special symbols can not be inserted, we only modify the character set for UTF-8,

The modification method is as follows:

With Notepad or uitraedit open the MySQL database installation directory under the My.ini file Open, and then ctrl+f search Default-character-set, the following character set modified to UTF8, note to modify two places, one thing client, One is the service side.

Then save, restart the MySQL service, go in and continue with show variables like "%colla%"; show varables like "%char%"; two statements to query the character set

Encoding settings for data tables and connection sections

Set up database and datasheet encoding

To solve the garbled problem, we must first understand the database and data table with what code. If not indicated, it will be the default latin1.
The most used should be the 3 character set Gb2312,gbk,utf8.
How do I specify the character set of the database and datasheet? The following also GBK as an example:
"Create a database in MySQL Command line client"

The code is as follows	Copy Code
mysql> CREATE TABLE ' Mysqlcode ' ( -> ' id ' TINYINT (255) UNSIGNED not NULL auto_increment KEY, -> ' content ' VARCHAR (255) Not NULL ->) TYPE = MYISAM CHARACTER SET gbk COLLATE gbk_chinese_ci; Query OK, 0 rows affected, 1 warning (0.03 sec)

The one behind the

The code is as follows	Copy Code
TYPE = MYISAM CHARACTER SET gbk COLLATE gbk_chinese_ci;

is to specify the character set of the database, COLLATE (collation), so that MySQL supports a variety of encoded databases.
You can, of course, modify the character set of the database datasheet by following these instructions:
　　　

The code is as follows	Copy Code
ALTER DATABASE Mysqlcode default character set ' GBK '.

Already set up the server, database and data table encoding, then the code in the database is GBK, Chinese can be stored in.
However, if you want to perform an insert or select operation, there will still be a Chinese garbled problem, because you have not set
The "Connection (connection)" section is encoded, while database operations such as INSERT, select contain connection actions to the database. If you don't believe
, you can now perform the following SQL try this:

The code is as follows	Copy Code
mysql> INSERT INTO mysqlcode values (null, ' Java enthusiasts ');

Press ENTER, and the results are as follows:

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

　　
Set Connection Encoding

The encoding of the server, database, and data table parts is set, and the connection code must be set. The connection encoding settings are as follows:

The code is as follows	Copy Code
mysql> SET character_set_client= ' GBK '; mysql> SET character_set_connection= ' GBK ' mysql> SET character_set_results= ' GBK '

If you set up your connection code, you can successfully insert Chinese in the following:

The code is as follows	Copy Code
mysql> INSERT INTO mysqlcode values (null, ' Java enthusiasts '); Query OK, 0 rows affected (0.02 sec)

　　
In fact, the above three commands to set the connection code can be reduced to one:
　　

The code is as follows	Copy Code
mysql> set names ' GBK ';

When the connection code is set, the Chinese can be displayed correctly when the select query is made:

The code is as follows	Copy Code
Mysql> select * from Mysqlcode; +----+-----------+ | ID | Content | +----+-----------+ | 1 | Java Hobby | +----+-----------+ 1 row in Set (0.00 sec)

query MySQL Chinese record garbled problem

Here we take gb2312 code garbled as an example to introduce

If you want to use the gb2312 encoding, it is recommended that you use Latin1 as the default character set for the datasheet, so that you can insert the data directly in the command line tool in Chinese and display it directly. Instead of using character sets such as gb2312 or GBK, and if you're worried about sorting queries, You can use the Binary property constraint, for example:

The code is as follows	Copy Code
CREATE TABLE my_table (name varchar () binary NOT NULL default ') Type=myisam default CharSet latin1;

Attachment: Old data upgrade method
Take the original character set for Latin1 as an example, upgrade to become the UTF8 character set.

Original table: old_table (Default charset=latin1), new table: New_table (Defaultcharset=utf8).

First step: Export old data

The code is as follows	Copy Code
mysqldump--default-character-set=latin1-hlocalhost-uroot-bmy_db--tables old_table > Old.sql

Step Two: Convert code

The code is as follows	Copy Code
Iconv-t Utf-8-F gb2312-c old.sql > New.sql

In this case, assume that the original data is gb2312 encoded by default.

Step Three: Import

Modify Old.sql, add an SQL statement: "SET NAMES UTF8;", save.

The code is as follows	Copy Code
Mysql-hlocalhost-uroot my_db < New.sql

Done!!

MySQL Import data garbled solution

The garbled statement appears: Mysql-hlocalhost-uroot-proot test<d:/test.sql

The correct statement: Mysql-hlocalhost-uroot-proot test<d:/test.sql--default-character-set=utf8

Solve MySQL import and export data garbled problem

The first thing to do is to determine the encoding format of the data you are exporting, and you need to add--default-character-set=utf8 when using mysqldump, such as the following code:

The code is as follows	Copy Code
Mysqldump-uroot-p--default-character-set=utf8 dbname tablename > Bak.sql

Then you should also use--default-character-set=utf8 when importing data:

The code is as follows	Copy Code
Mysql-uroot-p--default-character-set=utf8 dbname < Bak.sql

This unified coding solves the problem of the garbled data migration in MySQL.