How to solve the problem of reading MySQL Chinese garbled characters

In general, there are two possible causes for Garbled text. The first reason is the incorrect encoding (charset) setting, which leads to the incorrect encoding resolution by the browser, resulting in the "tianshu", which is full of screens ", secondly, the file is opened with an error code, and then saved, for example, a text file originally gb2312 encoding, but opened with UTF-8 encoding and then saved. To solve the above garbled code problem, you must first know which stages of development involve encoding:

1. file encoding: indicates the encoding of the page file (.html,. php, etc. Notepad and Dreamweaver automatically recognize the file encoding when opening the page, so there is no problem. Zendstudio does not automatically recognize the encoding. It only opens the file with a certain encoding according to the preference configuration. If you do not pay attention to it during work, use the error code to open the file, after the modification is made, the Garbled text will appear as soon as it is saved.

2. Page declarative encoding: in the HTML code head, you can use <meta http-equiv = "Content-Type" content = "text/html; charset = "XXX"/> (this sentence must be written before <title> XXX </title>. Otherwise, the page will be blank (ie + PhP only )) to tell the browser web page using what encoding, the current Chinese website development is mainly used in gb2312 and UTF-8 two types of encoding.

3. Database Connection encoding: it refers to the encoding used to transmit data with the database during database operations. Note that it should not be confused with the database encoding, for example, MySQL uses Latin1 encoding by default. That is to say, MySQL uses Latin1 encoding to store data. Data transmitted to MySQL using other encoding will be converted to Latin1 encoding.

When we know where encoding is involved in web development, we also know the cause of garbled code: the three encoding settings are inconsistent, because most of the encodings are compatible with ASCII, so the English symbols will not appear, and Chinese characters will be unlucky. The following are some common errors and solutions:

1. The database uses utf8 encoding, while the page declarative encoding is gb2312, which is the most common cause of garbled code. In this case, the Select data in the PHP script is garbled and needs to be used before query:

Mysql_query ("set names GBK"); or mysql_query ("set names gb2312"); To set the MySQL connection encoding, ensure that the page declarative encoding is consistent with the connection encoding set here (GBK is an extension of gb2312 ). If the page is UTF-8 encoded, you can use:

Mysql_query ("set names utf8"); note that utf8 is not a commonly used UTF-8. If the encoding stated on the page is consistent with the internal encoding of the database, no connection encoding can be set.
Note: In fact, MySQL data input and output are more complex than described above. MySQL configuration file my. INI defines two default encodings, they are default-character-set in [client] and default-character-set in [mysqld] to set the encoding used for client connection and database respectively by default. The encoding we specified above is actually the command line parameter character_set_client when the mysql client connects to the server to tell the MySQL server what encoding the client data is received, rather than the default encoding.

 

2. The page declarative encoding is inconsistent with the file encoding. this rarely happens, because if the encoding is inconsistent, the attacker will see garbled code in the browser when making the page. More often, it is caused by modifying some minor bugs after the release, opening the page with error code, and saving it. Or you can use some FTP software to directly modify files online, such as CuteFTP. The conversion error is caused by incorrect software encoding.

3. Some friends who rent a VM clearly confirm that the above three codes are correctly set and there are still garbled characters. For example, the web page is gb2312 encoding, ie and other browsers open but always recognized as a UTF-8, the web page head has been declared is gb2312, manually modify the browser code to gb2312 after the page shows normal. The cause is that the server Apache sets the server's global default encoding, added the adddefaultcharset UTF-8 in httpd. conf. At this time, the server will first send an HTTP header to the browser, which has a higher priority than the declarative encoding in the page, and the natural browser will recognize the error. There are two solutions. The administrator needs to add adddefacharcharset gb2312 to the virtual machine in the configuration file to overwrite the global configuration, or configure it in the. htaccess directory.

Garbled Solution

To solve the garbled problem, you must first find out the encoding used by your database. If not specified, the default value is Latin1.
The three character sets gb2312, GBK, and utf8 are used most.
So how do we specify the character set of the database? The following GBK is also used as an example.
[Create a database in MySQL command line client]
Mysql> Create Table 'mysqlcode '(
-> 'Id' tinyint (255) unsigned not null auto_increment primary key,
-> 'Content' varchar (255) not null
->) Type = MyISAM Character Set GBK collate gbk_chinese_ci;
Query OK, 0 rows affected, 1 warning (0.03 Sec)
Mysql> DESC mysqlcode;
+ --------- + --------------------- + ------ + ----- + --------- + ------------------ +
| FIELD | type | null | key | default | extra |
+ --------- + --------------------- + ------ + ----- + --------- + ------------------ +
| ID | tinyint (255) unsigned | no | pri | auto_increment |
| Content | varchar (255) | no |
+ --------- + --------------------- + ------ + ----- + --------- + ------------------ +
2 rows in SET (0.02 Sec)

Type = MyISAM Character Set GBK collate gbk_chinese_ci;
Is to specify the character set of the database, collate (), so that MySQL supports multiple encoding databases at the same time.
Of course, we can also use the following command to modify the character set of the database:
Alter database da_name default Character Set 'charset '.
The client sends data in GBK format. The following configurations can be used:
Set character_set_client = 'gbk'
Set character_set_connection = 'gbk'
Set character_set_results = 'gbk'
This configuration is equivalent to set names 'gbk '.
Perform operations on the database you just created
Mysql> use test;
Database changed
Mysql> insert into mysqlcode values (null, 'php hobby ');
Error 1406 (22001): Data too long for column 'content' at Row 1
If the character set is not specified as GBK, an error occurs during insertion.
Mysql> set names 'gbk ';
Query OK, 0 rows affected (0.02 Sec)
The specified character set is GBK.
Mysql> insert into mysqlcode values (null, 'php hobby ');
Query OK, 1 row affected (0.00 Sec)

Inserted successfully

Mysql> select * From mysqlcode;
+ ---- + ----------- +
| ID | content |
+ ---- + ----------- +
| 1 | PHP hobbies |
+ ---- + ----------- +
1 row in SET (0.00 Sec)

Garbled characters are also displayed when the character set GBK is not specified.

Mysql> select * From mysqlcode;
+ ---- + --------- +
| ID | content |
+ ---- + --------- +
| 1 | PHP ??? |
+ ---- + --------- +
1 row in SET (0.00 Sec)

[Create a database in phpMyAdmin and specify the character set]

 

Select the table type as needed. Here, select MyISAM (full-text search is supported );
Sort and select gbk_chinese_ci, that is, the GBK character set.
Gbk_bin Simplified Chinese, binary. Gbk_chinese_ci (Simplified Chinese), case-insensitive.

Insert a database into the database you just created

 

Garbled characters are found during browsing.

Why? Because the database is a GBK character set, we did not specify it as a GBK
Back to database Homepage

You can see the default latin1_bin for MySQL connection verification. Change it to gbk_chinese_ci.

Insert a data record. Check that this article is normal.

 

[Solve PHP to read database garbled characters]

Take database mysqlcode as an Example
<? PHP
$ Conn = mysql_connect ("localhost", "root", "89973645 ");
Mysql_query ("set names 'gbk'"); // This specifies the database character set. It is usually placed after the database is connected.
Mysql_select_db ("test ");
 
$ SQL = "select * From mysqlcode ";
$ Result = mysql_query ($ SQL, $ conn );
 
?>
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = gb2312"/>
<Title> MySQL character encoding </title>
</Head>
 
<Body>
<Table width = "300" Height = "32" border = "1" align = "center" cellpadding = "0" cellspacing = "0">
<Tr>
<TD width = "71" align = "center"> id </TD>
<TD width = "229" align = "center"> content </TD>
</Tr>
<? PHP while ($ ROW = mysql_fetch_assoc ($ result )){
Echo"
<Tr>
<TD align =/"center/"> ". $ row ['id']." </TD>
<TD> ". $ row ['content']." </TD>
</Tr> ";
}?>
</Table>
</Body>
</Html>
<? PHP mysql_free_result ($ result);?>
If we comment out mysql_query ("set names 'gbk'");, it must be garbled.

The sentence is normal again.

One sentence
What encoding does your database use? Set names 'your encoding' before you operate on the database ';