About MySQL Chinese garbled problem how to solve (garbled problem perfect solution)

This article to you about the MySQL Chinese garbled problem how to solve (garbled problem perfect solution) of the relevant information, but also for you to collect some information about MySQL will appear in Chinese garbled reasons common points, small partners come and see it

The last two days to do the project is always garbled problems, this is not just the MySQL Chinese garbled problem solved, the following small series of my solution to share to everyone, for your reference, but also convenient for you to check later.

First of all:

Use show variables like "%colla%"; show varables "%char%"; these two commands view the character set settings of the database and the server side

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

Here's how to modify it:

Open the My.ini file in the MySQL database installation directory with Notepad or Uitraedit, and then ctrl+f search Default-character-set, change the character set to UTF8, notice 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%"; query the character set with two statements.

The configuration is complete.

Attention:

If you have a database that has not been deleted before, use the show database name, and the show create table name; see if the character set for the database and table is UTF8, because modifying the My.ini file does not modify the character set of the original database. Below the command line, you can use

ALTER DATABASE database name character Set "character set"; command to modify DB character set

There is a point to note that after the change to UTF8, the command line below the Chinese is garbled, only output to the page or console is normal, this problem I also check the Internet, seemingly command line below does not support UTF8, I am not very clear.

When modified, below the command line if you want to insert Chinese, you can execute before inserting the statement, set names gbk2312; You can insert Chinese, but you cannot insert traditional and some special symbols.

The above is the results of the past few days to solve garbled. I hope you have a lot of shrimp advice.

Let's take some time to tidy up some of the reasons for MySQL will appear in Chinese garbled is the following points.

The 1.server itself sets the problem, for example still staying in Latin1
2.table language setting problem (including character and collation)
3. The connection language setting problem for the client program (e.g. PHP)

It is highly recommended to use UTF8!!!!

UTF8 can be compatible with all characters in the world!!!!

First, avoid creating database and table in Chinese garbled and view encoding method

1, when creating the database:

1 CREATE DATABASE  2CHARACTERSET'UTF8'3' Utf8_general_ci ';

2, the time to build the table

1 CREATE TABLE' Database_user ' (2' ID 'varchar( +) not NULL default "', 3' UserID 'varchar( +) not NULL default "', 4) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

With these 3 settings, there is no problem at all, that is, the same encoding format is used when building the library and building the table.

But if you have built libraries and tables, you can query them in the following ways.

1. View the default encoding format:

1Mysql>Show variables like"%Char%"; 2 +--------------------------+---------------+ 3 |Variable_name|Value| 4 +--------------------------+---------------+ 5 |Character_set_client|Gbk| 6 |Character_set_connection|Gbk| 7 |Character_set_database|Utf8| 8 |Character_set_filesystem| binary | 9 |Character_set_results|Gbk| Ten |Character_set_server|Utf8|  One |Character_set_system|Utf8|  A +--------------------------+-------------+

Note: Before 2 to determine, you can set the default encoding format using the set names Utf8,set names GBK;

The effect of performing set NAMES UTF8 is equivalent to setting the following:

1 set character_set_client='UTF8'2set Character_set_connection='UTF8'3set Character_set_results='UTF8';

2. View the encoding format of the test database:

1Mysql>ShowCreate Databasetest;2 +------------+------------------------------------------------------------------------------------------------ + 3 | Database | Create Database | 4 +------------+------------------------------------------------------------------------------------------------ + 5 |Test| CREATE DATABASE' Test '/*!40100 DEFAULT CHARACTER SET GBK*/ | 6 +------------+------------------------------------------------------------------------------------------------ +

3. View the encoding format of the YJDB data sheet:

1Mysql>ShowCreate Tableyjdb;2 |Yjdb| CREATE TABLE' Yjdb ' (3' SN 'int(5) not NULLAuto_increment,4' Type 'varchar(Ten) not NULL, 5' BRC 'varchar(6) not NULL, 6' Teller 'int(6) not NULL, 7' Telname 'varchar(Ten) not NULL, 8' Date 'int(Ten) not NULL, 9`Count`int(6) not NULL, Ten' Back 'int(Ten) not NULL,  One PRIMARY KEY(' sn '), A UNIQUE KEY' sn ' (' sn '), - UNIQUE KEY' sn_2 ' (' sn ') -) ENGINE=MyISAM auto_increment=1826 DEFAULTCHARSET=GBK Row_format=DYNAMIC|

Second, to avoid the import of data has the problem of Chinese garbled

1: Save the Data encoding format as Utf-8

Set the default encoding to UTF8:

Set names UTF8;

Setting the database db_name default to UTF8:

1 ALTER DATABASE 'db_nameDEFAULTCHARACTERSET UTF8 COLLATE utf8_ General_ci;

Set table tb_name default encoding is UTF8:

1 ALTER TABLE DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

Import:

LOAD DATA LOCAL INFILE ' c:\\utf8.txt ' into TABLE yjdb;

2: Save data Encoding format to ANSI (i.e. GBK or GB2312)

Set the default encoding to GBK:

Set names GBK;

Set the database db_name default encoding to GBK:

1 ALTER DATABASE 'db_nameDEFAULTCHARACTERSET GBK COLLATE gbk_chinese_ Ci

Set table Tb_name default encoding is GBK:

1 ALTER TABLE DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci;

Import:

LOAD DATA LOCAL INFILE ' c:\\gbk.txt ' into TABLE yjdb;

Note: 1. UTF8 do not import GBK,GBK do not import UTF8;

The display of UTF8 is not supported under 2.dos;

Third, solve the problem of garbled pages

Set the site encoding to Utf-8, which is compatible with all characters in the world.

If the site has been working for a long time, there are many old data, can not change the setting of Simplified Chinese, then it is recommended to set the page encoding GBK, GBK and GB2312 is the difference between: GBK can show more characters than GB2312, to show the simplified code of traditional characters, you can only use GBK.

1. Edit/etc/my.cnf, add Default_character_set=utf8 in [MySQL] section;

2. When writing the connection URL, add the useunicode=true&characterencoding=utf-8 parameter;

3. Add a "Set names UTF8" or "Set names GBK" instruction in the Web page code to tell the MySQL connection to use UTF8 or GBK;

Reprint Original Address: http://www.jb51.net/article/74769.htm

About MySQL Chinese garbled problem how to solve (garbled problem perfect solution) (GO)