MYSQL Chinese garbled Troubleshooting solution

Read Catalogue

• 1. Avoid creating database and table garbled and view encoding method
• 2. Avoid the problem of importing data with Chinese characters
• 3. Solve the problem of garbled pages

MySQL will appear in Chinese garbled reason is the following points:

• The server itself sets a problem, such as still staying in Latin1
• Table language setting problem (including character and collation)
• The connection language setting problem for client programs (e.g. PHP)

Back to top 1. Avoid creating databases and tables with garbled characters and view encoding methods

A. When creating a database

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

B. When the table is built

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

The same encoding format is used when building and building tables, and there is basically no problem.

When the program connects to the configuration data link:

If this is not the case above, try this:

C. If you have already built libraries and tables, you can query them in the following ways:
To view the default encoding format:

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: 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:

SET character_set_client= ' UTF8 '; SET character_set_connection= ' UTF8 '; SET character_set_results= ' UTF8 ';

To view the encoding format of the test database:

Show create DATABASE test;

+------------+------------------------------------------------------------------------------------------------+
| Database | Create Database |
+------------+------------------------------------------------------------------------------------------------+
| Test | CREATE DATABASE ' test '/*!40100 DEFAULT CHARACTER SET GBK */| |
+------------+------------------------------------------------------------------------------------------------+

To view the encoding format for the YJDB database:

Show CREATE TABLE yjdb;| Yjdb | CREATE TABLE ' yjdb ' (' sn ' int (5) NOT NULL auto_increment, ' type ' varchar (TEN) NOT NULL, ' BRC ' varchar (6) ' is not null, ' Teller ' in T (6) NOT NULL, ' telname ' varchar (TEN) NOT NULL, ' date ' int (ten) not null, ' count ' int (6) is not null, ' back ' int (ten) not Null,prima RY key (' SN '), unique key ' SN ' (' sn '), unique key ' sn_2 ' (' sn ')) Engine=myisam auto_increment=1826 DEFAULT CHARSET=GBK row_f ormat=dynamic |

Back to top 2. Avoid the problem of importing data with Chinese garbled characters

A. 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:

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;

B. Saving the data encoding format to ANSI (that is, GBK or GB2312)
Set the default encoding to GBK:

Set names GBK;

Set the database db_name default encoding to 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: UTF8 do not import GBK,GBK do not import Utf8;dos does not support the display of UTF8;

Back to top 3. 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.
A. Edit/etc/my.cnf, add in [MySQL] section

Default_character_set=utf8;

B. When writing the connection URL, add

? useunicode=true&characterencoding=utf-8

C. 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;

MYSQL Chinese garbled Troubleshooting solution