Completely solve the MySQL Chinese garbled method

MySQL will appear in Chinese garbled reasons are the following:
1.server itself set the problem, for example still stay in Latin1
2.table language setting problem (including character and collation)
3. Client program (for example, PHP) of the line-language setting problem
It is highly recommended to use UTF8!!!!
UTF8 can be compatible with all the characters in the world!!!!

Linux under MySQL insert Chinese display garbled solution

Mysql-uroot-p return Enter password

Enter the MySQL view status as follows:

MySQL cannot be inserted in Chinese

The default is the client and server are used latin1, so will be garbled.

Solution:

Mysql>user MyDB;

Mysql>alter database mydb Character Set utf8;!

The other way is to directly modify the default MySQL configuration file

In a Debian environment, completely solve MySQL cannot insert Chinese and Chinese garbled method

The above mentioned the use of temporary methods to change the database's character set settings, display Chinese, but later found in some systems can not be successful, such as I used Debian 7.0, looking for a long time to find a solution for the Debian system, Debian 7.0 tested successfully, Other methods, such as modifying client and MySQL to join Default-character-set=utf8, only apply to systems prior to 5.5!

Ultimate solution, once and for all:

Debian under the MySQL string modified for UTF8 (reference: Rainysia's Column)

First, enter MySQL (mysql-uroot-p), view the current database character set (status;)

Second, view the current system version (LSB_RELEASE-A)

MySQL cannot be inserted in Chinese

Third, #vim/etc/mysql/my.cnf. (5.5 prior system ) join Default-character-set=utf8 under "client"

Join Default-character-set=utf8 under "mysqld"

Notice: Note If you can not start the error after modification try to change the Default-character-set=utf8 to Character_set_server=utf8, just add to the mysqld below. Client doesn't need to add.

Four, #vim/etc/mysql/my.cnf. (after 5.5 system) modify as follows:

[Client]
Default-character-set=utf8

[Mysqld]
Default-storage-engine=innodb
Character-set-server=utf8
Collation-server=utf8_general_ci

Five, restart MySQL (/etc/init.d/mysql stop/etc/init.d/mysql start)

Six, build success, enter MySQL view character set (Mysql>show variables like ' character_set_% ';)

MySQL cannot insert Chinese

first, avoid creating a database and tables appear in Chinese garbled and view coding methods


1: Create database ' Test '


CHARACTER SET ' UTF8 '


COLLATE ' utf8_general_ci ';


2, CREATE table ' Database_user ' (


' ID ' varchar not NULL default ',


' UserID ' varchar () not NULL default ',


) Engine=innodb DEFAULT Charset=utf8;





These 3 settings, the basic will not be a problem, that is, build the library and the table with the same encoding format.


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


1. View the default encoding format:


mysql> Show variables like "%char%";


+--------------------------+---------------+


| variable_name | Value |


+--------------------------+---------------+


| character_set_client | GBK |


| character_set_connection | GBK |


| Character_set_database | UTF8 |


| Character_set_filesystem | binary |


| Character_set_results | GBK |


| Character_set_server | UTF8 |


| Character_set_system | UTF8 |


+--------------------------+-------------+


Note: The previous 2 to determine that you can use the set names Utf8,set names GBK to set the default encoding format;





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


SET character_set_client= ' UTF8 ';


SET character_set_connection= ' UTF8 ';


SET character_set_results= ' UTF8 ';





2. View the encoding format of the test database:


mysql> Show CREATE DATABASE test;


+------------+--------------------------------------------------------------------------------------------- ---+


| Database | Create Database |


+------------+------------------------------------------------------------------------------------------------+


| Test | CREATE DATABASE ' test '/*!40100 DEFAULT CHARACTER SET GBK * |


+------------+--------------------------------------------------------------------------------------------- ---+





3. View the encoding format of the YJDB database:


mysql> Show create TABLE yjdb;


| Yjdb | CREATE TABLE ' yjdb ' (


' sn ' int (5) Not NULL auto_increment,


' type ' varchar not NULL,


' BRC ' varchar (6) Not NULL,


' Teller ' int (6) not NULL,


' telname ' varchar (%) not NULL,


' Date ' int (a) not NULL,


' count ' int (6) not NULL,


' Back ' int (ten) not NULL,


PRIMARY KEY (' sn '),


UNIQUE KEY ' sn ' (' sn '),


UNIQUE KEY ' sn_2 ' (' sn ')


) Engine=myisam auto_increment=1826 DEFAULT CHARSET=GBK row_format=dynamic |





Two, avoid importing the data have Chinese garbled problem


1: Save the Data encoding format as Utf-8


set default encoding to UTF8:


set names UTF8;


Set the database db_name default to UTF8:


ALTER DATABASE ' db_name ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;


Set Table tb_name default encoding is UTF8:


ALTER TABLE ' tb_name ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;


Import:


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


2: Save the Data encoding format as ANSI (that is, GBK or GB2312)


set default encoding to GBK:


set names GBK;


Set the database db_name default encoding is GBK:


ALTER DATABASE ' db_name ' DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;


Set Table Tb_name default encoding is GBK:


ALTER TABLE ' tb_name ' 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;


three, solve the problem of garbled page





sets the site code to Utf-8, which can be compatible with all the characters in the world.


if the site has been in operation for a long time, there are a lot of old data, can not change the setting of Simplified Chinese, then it is recommended that the encoding of the page to GBK, GBK and GB2312 is the difference between: GBK can display more characters than GB2312, to show the traditional character of simplified code, 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 to the Web page code to tell MySQL that the connection content is to be used


UTF8 or GBK;