A brief explanation of MySQL character set generation garbled characters

MySQL 4.1 Character Set Support includes two Character sets (Character set) and Collation ). // Title: MySQL character set (my summary)

// By dx_andy

// Time: 2007.8.27 evening

// Test environment: win32 MySQL 5.0.45

// Cause: Garbled MySQL

Since the launch of MySQL 4.1, the garbled code problem for Chinese people has also aroused the risk of the entire internet. The main reason is that different character sets are encoded differently.

Which character sets are available in MySQl configurations:

MySQL 4.1 Character Set Support includes two Character sets (Character set) and Collation ). The support for character sets is refined to four levels: server, database, table, and connection ).

Run the following command to check the current MySQL character set:

Show variables like "character_set _ % ";

Show variables like "collation _ % ";

MySQL or later versions of the system preset encoding is UTF-8, and our Chinese encoding: GBK, GB2312, BIG5 ...... So when we convert Chinese characters into character sets of UTF-8 as other character sets, and then store them in the database, some text code will be lost and garbled. This is what we get from the database query and the data is displayed on the webpage as "???". Or the cause of garbled characters.

Solution? Of course, it is also very simple. You can search for it on any web page.

Solution (1): set the MySQL character set before inserting data, that is, mysql_query ("set names gbk") or change gbk to gb2312 or another Chinese character set. Then perform the insert operation.

"Special" "Don" "sound" "Ming"

The above method is successfully tested on my computer.

However, I feel a little uncomfortable. Before each query, I need to write mysql_query ('set name gbk ');. I'm not satisfied with this. So I started searching again, and I found the configuration method to be discussed below.

Solution (2): configure the my. ini file. Open my. ini and find [mysql]. Find default-character-set. If your mysql is installed by default, it should be utf8. Change it to gbk or gb2312. (That is, do not add mysql_query ("set names gbk") before query "))

Again, output, question, and question

I have changed the number of times according to the above method. The change from gbk to gb2312 to utf8 has never been successful. I thought about what it looks like at a life prompt, since the webpage cannot pass the value successfully.

So I opened MySQL at a command prompt. The test showed that MySQL was operating normally at the command prompt. I have a big head here.

I re-configured the PHP environment and downloaded the latest AppServ 2.5.9. Check the MySQL character set. The default character set is UTF8.

MySQL command: (show variables like "character_set _ % ";)

Then, when I do not execute (mysql_query ("set names gbk"), I insert data to the database and perform query operations. Everything on the webpage is displayed normally. Oh, it's really strange!

Why can text be inserted into the database without conversion?

Why can I get the correct data from the database without converting the text in it?

With these two questions, I opened the command prompt again. When the query operation is executed, all the results are garbled (at that time, I thought: The text should be GBK encoded ). So I executed: set names gbk; and changed this database character set to the GBK character set. The query results show question marks. I really don't want to study it any more.

I commented out the default-character-set in the [mysql] column of my. ini file. After I restarted MySQL, I performed another operation. The result was unexpectedly displayed normally. I'm so excited. As a result, I checked the character set of mysql again: haha it was latin1. Isn't it here? Then I use PHP to insert data and query the database (without executing mysql_query ("set names gbk"). The results will still display normally. Haha, the problem is finally found. It turns out to be a strange problem !!!

I want to see that everyone should understand it. Let's make a small summary.

The greatest, the post, the general, and the knot (only personal opinions, do not assume any legal responsibility !)

(1) In the mysql5.0.45 environment, the database treats the data transmitted by PHP as the latin1 (ISO-8859-1) Character Set by default. That is, latin1 is converted to a UTF-8, and then inserted.

(2) Before PHP inserts data into the MySQL database, MySQL runs (mysql_query ("set names gbk") to use the character set specified in the PHP query (gbk) insert after converting to UTF-8.

(3) Operating MySQL in CMD mode and using PHP for character set processing are two concepts. In CMD, MySQL converts the data under CMD to the default Character Set of MySQL to the UTF-8 for post-processing. For processing PHP Data, MySQL will process latin1 data by default.

---- My final configuration ----:

Because I only perform a test locally, to test the problem, write less mysql_query ("set names gbk "). So I changed the default-characte_set in the [mysql] Bar of my. ini to latin1. In this way, it is convenient to operate MySQL locally or in CMD. However, if you use PHPMyAdmin, garbled characters may be displayed. The solution is to install PHPMyAdmin that supports the character set latin1. If both the character set and the character set are checked for latin1.

Customs, Yu, number, Data, database, backup, and copies

Because the Chinese character set used before mysql4.1 is gbk or gb2312. We recommend that you run the set name gbk command before backing up and restoring the database to solve the garbled problem.