PHP access MySQL data garbled hack

The following article is mainly about PHP (PHP training PHP tutorial) access to MySQL (MySQL certified MySQL training) data garbled practical solution we all know that MySQL data garbled is a very headache, then how to solve it? The following articles will be answered.

The study of PHP and MySQL is not in-depth, the following are some of my personal experience, said may not be very clear, but it is really useful things.

My discuz version is UTF-8, but open phpmyadmin display is the sorting column: Gbk_chinese_ci, and view data display completely normal, no garbled, this means that the actual discuz in the Access database is GBK code, But the page display does UTF-8 encoding ah, how do not MySQL data garbled it? Did discuz do the code conversion.

Recently developed a Web site in the company, due to the use of cakephp framework, the default code is UTF-8, and my computer is Ubuntu, usually used are UTF-8 encoding, the program page is written UTF-8 encoded files, naturally think of the UTF-8 encoded database, This problem can make me very bitter.

According to the general online statement is the creation of the database table with the DEFAULT CHARSET UTF8, the table created by the collation column in the phpMyAdmin display is utf8_general_ci, in the execution of the SQL statement is added "set Names UTF8", This will be normal, the inserted data in the page display is completely normal, my page is set UTF-8 encoding, <meta http-equiv= "Content-type" content= "text/html; Charset=utf-8 "/>.

Supposedly there should be no problem, but in the phpMyAdmin inside the display is really a MySQL data garbled, and if I change a phpmyadmin inside a data, in the phpMyAdmin inside the display is normal, but to the page display is garbled, So I would like to refer to the practice of discuz in the phpMyAdmin Force the table and all the Char,varchar and text fields changed to Gbk_chinese_ci, modified, phpmyadmin inside normal, but to the page display is actually MySQL Data garbled, the company's original database is Ms-sql Server 2000 now to import into the MySQL5, the original two characters in the import to char (10) When the error is that the character is too long, how possible?

One character according to UTF16 also 4 bytes up to 8 bytes Ah, what's going on? Online Some people say that is due to improper coding may be UTF8 encoding after two conversion into a Chinese character 6 bytes storage, specifically what is going on I do not know, but then after many experiments finally understand the original MySQL access code and query code is not consistent, need to manually specify, you can also in MySQL In the configuration file. The solution for someone on the Internet is:

PHP source files are using UTF-8 encoded MySQL storage with GBK encoding

1. Set character_set_client = UTF8;

Specifies that the SQL statement that the PHP program sends to the database uses UTF8 encoding, such as insert;

1. Set character_set_connection = GBK;

Indicates that the database should be transcoded from character_set_client after it receives the SQL statement as

UTF8 format, such as insert. (In the absence of this sentence, the inserted data becomes a question mark)

1. Set character_set_results = UTF8;

Indicates what encoding should be returned to the caller, such as SELECT, after the database query is complete.

Now the ultimate solution, PHP file for UTF-8: Execute the following three commands before all the mysql_query functions are executed to do the database insert Delete query:

1. mysql_query (' Set character_set_client = UTF8 ');
2. mysql_query (' Set character_set_connection = GBK ');
3. mysql_query (' Set character_set_results = utf8 ');

Instead of the previous set names UTF8 command to create the database and create the table with the specified encoding of GBK, the specified collation is:

1. Gbk_chinese_ci;
2. CREATE DATABASE ' test ' DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

This way your site will never have MySQL data garbled problem and if there is a field is username char (20), so that you can insert 20 characters, instead of 20/2 or 20/3 or the like if you insert 20 Kanji, select Length ( Username) View will return 40, that is, MySQL is actually stored in 40 characters but we do not have to take care of his actual storage, you want to limit how much directly is specified char () How much, Chinese characters and English are treated similarly.

PHP access MySQL data garbled decoding