How to solve the Mysql Chinese garbled problem (the perfect solution to the garbled problem) _ MySQL

This article describes how to solve the Mysql Chinese garbled problem (the perfect solution to the garbled problem) and some common causes of Chinese garbled characters in MySQL, let's take a look at the problem of garbled code that has plagued projects in the past two days. this is not just a solution to the mysql Chinese garbled code problem. The following small series will share my solution with you for your reference, it is also convenient for you to check later.

First:

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

If you see it is gbk2312, or gbk, then only supports Simplified Chinese, traditional and some special symbols can not be inserted, we only modify the character set to UTF-8,

The modification method is as follows:

Use Notepad or UitraEdit to open my. open the INI file and press Ctrl + F to search for default-character-set and change the character set to UTF8. Note that you need to modify the character set in two places: client and server.

Save and restart the mysql service. go in and continue using show variables like "% colla %"; show varables like "% char %"; query the character set with two statements.

Now the configuration is complete.

Note:

If the database has not been deleted before, use the show database name; and the show create table name. check whether the character set of the database and table is UTF8 because you have modified my. ini file, which cannot modify the character set of the original database. You can use

Alter database name character set "character set"; command to modify the database character set

Note that after being changed to UTF8, Chinese characters in the command line are garbled and output only to the page or console is normal. I also checked this problem online, it seems that the command line does not support UTF8, which is not clear to me.

After modification, if you want to insert Chinese characters under the command line, you can execute the command before the insert statement and set names gbk2312; then you can insert Chinese characters, but you cannot insert traditional Chinese characters or some special characters.

The above is the result of solving garbled text in the past few days. I hope you can give me more advice.

The following is an example of why MySQL may contain Chinese garbled characters.

1. problems with server settings, such as staying at latin1
2. table language setup problems (including character and collation)
3. connection language settings for client programs (such as php)

We strongly recommend that you use utf8 !!!!

Utf8 is compatible with all characters in the world !!!!

1. avoid Chinese garbled characters and check encoding methods when creating databases and tables

1. when creating a database:

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

2. when creating a table

CREATE TABLE `database_user` ( `ID` varchar(40) NOT NULL default '', `UserID` varchar(40) NOT NULL default '', ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

After these three settings are complete, there will basically be no problem, that is, the same encoding format is used for both database creation and table creation.

However, if you have created a database and a table, you can query the table by using the following methods.

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: You can use set names utf8 and set names gbk to set the default encoding format;

The execution of set names utf8 is equivalent to 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 data table:

mysql> show create table yjdb; | yjdb | CREATE TABLE `yjdb` ( `sn` int(5) NOT NULL AUTO_INCREMENT, `type` varchar(10) NOT NULL, `brc` varchar(6) NOT NULL, `teller` int(6) NOT NULL, `telname` varchar(10) NOT NULL, `date` int(10) NOT NULL, `count` int(6) NOT NULL, `back` int(10) 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 | 

II. avoid Chinese garbled characters in the imported data

1: save the data encoding format as UTF-8

Set the default encoding to utf8:

Set names utf8;

Set Database db_name to utf8 by default:

ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

Set the default table tb_name encoding to 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 (GBK or GB2312)

Set the default encoding to gbk:

Set names gbk;

Set the default database db_name encoding to gbk:

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

Set the default table tb_name encoding to 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. do not import gbk for UTF8, or do not import UTF8 for gbk;

2. UTF8 display is not supported in dos;

3. solve the problem of garbled code on the webpage

Set the website encoding to UTF-8 to be compatible with all characters in the world.

If the website has been running for a long time and has a lot of old data, you cannot change the settings of simplified Chinese, we recommend that you set the page encoding to GBK. The difference between GBK and GB2312 is: GBK can display more characters than GB2312. to display simplified Chinese characters, you can only use GBK.

1. edit/etc/my. cnf and add default_character_set = utf8 in the [mysql] segment;

2. when writing a Connection URL, add? UseUnicode = true & characterEncoding = UTF-8 parameter;

3. add a "set names utf8" or "set names gbk" command in the webpage code to instruct MySQL to use utf8 or gbk for connection content;