The best solution for Mysql Chinese garbled problem _mysql

In general, the cause of MySQL appears in Chinese garbled factors mainly have the following points:

1.server itself set up problems with the character set, for example still stay in Latin1
2.table language setting problem (including character and collation)
3. Client program (for example, PHP) of the connection language set up problems

In this respect, it is strongly recommended to use UTF8 encoding! Because UTF8 can be compatible with all the characters in the world!

First, to avoid the creation of databases and tables appear in Chinese garbled and view coding methods

1, the time to create the database:

CREATE DATABASE ' test '
CHARACTER SET ' UTF8 '
COLLATE ' utf8_general_ci ';

2, the time to build the table

CREATE TABLE ' database_user ' (
' ID ' varchar ' () NOT null default ',
' UserID ' varchar ' (+) NOT null default ', 
   
     Engine=innodb DEFAULT Charset=utf8;

   

These 3 settings are OK, the basic will not be a problem, that is, the same encoding format is used to build the library and build the table.
But if you have 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 is not NULL,
' BRC ' varchar (6) is not N ull,
' Teller ' int (6) Not NULL,
' telname ' varchar (a) NOT NULL,
' date ' int (a) not NULL,
' count ' int (6) NO T null,
' back ' int (a) 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 |

Second, to avoid importing data with Chinese garbled problem

1. Save the data encoding format as Utf-8
Set default encoding to UTF8:

Set names UTF8;

Set database db_name defaults 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;
(2) DOS does not support the display of UTF8;

Third, solve the problem of garbled Web pages

set the site code to Utf-8 so that all the characters in the world are compatible .
If the site has been operating for a long time, there are many old data, can not change the setting of Simplified Chinese, then it is recommended that the encoding of the page set to GBK, GBK and GB2312 is the difference between: GBK can display more characters than GB2312, to show the traditional character of simplified code, 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" instructions to the Web page code to tell MySQL that the connection content is to be used
UTF8 or GBK;