Analysis of character sets and PHP + Mysql4.1 garbled characters

Always float @ CU original. for reprint, please note that Mysql has added support for character sets since Mysql 4.1. I used to know Mysql better. When I first came into contact with Mysql 4.1, I felt that Mysql was a bit redundant. However, I later thought that the support for character sets could be troublesome for developers, but it is undeniable that, is an improvement. The support for character sets not only supports multiple languages, but is always @ CU original.
Mysql has added support for character sets since 4.1. I used to know Mysql better. When I first came into contact with Mysql 4.1, I felt that Mysql was a bit redundant. However, I later thought that the support for character sets could be troublesome for developers, but it is undeniable that, is an improvement. The support for character sets not only supports multiple languages, but also facilitates porting.
When you first started using Mysql4.1, you may feel a little uncomfortable. Next, I will briefly explain my understanding of the Mysql4.1 character set and how PHP can adapt to this change in Mysql, I hope you will gain some benefits after reading this article.
If you are not familiar with basic computer knowledge, read the "conclusion"
I. Principles
Mysql Character set has two concepts: "Character set" and "Collations ".
1. Collations
Collations is translated into Chinese as "verification". in the process of web development, this word is only used in Mysql. it mainly serves to guide Mysql in character comparison, for example, in the ASCII character set, collations specifies whether a is less than B, a is equal to a, and a is equal to. Generally, you can ignore the existence of Collations because each character set has a default Collations. Generally, you can use the default Collations.
2. Character set
In contrast, character sets are a broader concept, and even common text files in Windows are vulnerable to character sets. Different character sets define different character encoding methods. A character set is a set of symbols and encoding, such as the ASCII character set, including digits, uppercase/lowercase letters, semicolons, line breaks, and so on, the encoding method is to use A 7bit to represent A character (A is 65, B is 98 ). ASCII only specifies the English letter encoding. non-English languages cannot be represented by ASCII encoding. Therefore, different countries are encoded for their own languages. for example, in our country, it has GB2312 encoding. However, there are also some cross-platform problems in the coding of different countries. For this reason, some international standards organizations have developed some universal international coding, the most commonly used is UTF8. ASCII is only used to encode English characters and English letters, GB2312 is used to encode English characters, English letters, and Chinese characters, and UTF8 is used to encode all languages and texts in the world, the characters in GB1212 contain ASCII characters, and UTF8 contains GB2312 characters. It can be seen that UTF8 is the most widely used character set. Therefore, UTF8 is generally used in some multilingual WEB systems (PHPMyAdmin uses UTF8 encoding ).
The storage of any text involves the concept of character sets. Including databases and common text files.
Main terms:
Characters: Chinese characters, English letters, punctuation marks, and Latin.
Encoding: converts A character to A computer-stored format. for example, A is expressed as 65.
Character Set: A group of characters and their encoding methods.
A. Mysql character set
Mysql currently supports multiple character sets and supports conversion between different character sets (easy to transplant and multi-language support ).
Mysql can set server-level character sets, database-level character sets, Data Table-level character sets, and column character sets. In fact, the character sets are stored in columns, for example, you set the col1 column in Table 1 to be of the character type, and col1 only uses the character set. if the col2 column in Table 1 is of the int type, col2 does not use the character set concept.
Server-level character sets, database-level character sets, and Data Table-level character sets are default options for column character sets.
Mysql must have a character set, which can be specified by adding parameters at startup, during compilation, or in the configuration file. The Mysql server character set is used as the default value at the database level. When creating a database, you can specify a character set. if it is not specified, the server character set is used. Similarly, when creating a table, you can specify the character set at the table level. if not, use the character set of the database as the character set of the table. When creating a column, you can specify the character set of a column. if not specified, you can use the character set of the table.
Generally, you only need to set server-level character sets, other database-level, table-level, and column-level character sets.
Because UTF8 is the most extensive character set, we usually set the Mysql server-level character set to UTF8!
B. character set of common text
Character sets exist in the storage of any text, and common text files are no exception.
In Windows +, open notepad and "Save ..." In the dialog box, you can select the encoding method for storing text.
Generally, all Windows + systems use the default encoding. Therefore, character set problems are not encountered.
In Windows, you can select the encoding method when saving a text file. However, when opening a text file, the encoding method is automatically determined. There is a joke about mobile and Unicom games on the Internet using Notepad of Windows +. you can search for it, because the encoding error caused by Windows when opening a text file.
Because automatic encoding is sometimes incorrect, some text files define how to identify the encoding they use. The HTML file is such an example.
HTML is a text file. When you store an HTML file, you need to use an encoding. in the HTML file, you also use the HTML syntax to specify the encoding used by the file (for example ). If no encoding is specified for an HTML file, the browser automatically identifies the file encoding. If encoding is specified in HTML, the browser uses the encoding specified in HTML.
Generally, the charset specified by the HTML file is the same as the encoding of the HTML file, but there are also inconsistencies. Otherwise, the webpage may be garbled (garbled here, only related to text files and databases .) Using specialized webpage editing tools (such as Dreamwave), files are automatically encoded based on the charset value in the webpage.
C. php + mysql character set problems
PHP generates a text file, but it needs to take the text in the database or store the text in the database.
Mysql supports multiple character sets. by default, Mysql does not know what encoding characters PHP sends to him. Therefore, Mysql requires the client (PHP) to tell him what character set is accessed.
PHP sets character_set_client to tell Mysql what encoding method PHP stores in the database.
PHP sets character_set_results to tell Mysql what encoding data PHP needs.
PHP sets character_set_connection to tell Mysql what encoding is used for text in PHP queries.
MYSQL uses the configured encoding method to store text.
Assume that Mysql uses setserver to store text. the character_set_client of PHP is setclient, and the character_set_results of PHP is setresult. Mysql converts the text sent by PHP from the setclient encoding method to the setserver encoding method and then stores it in the database. If PHP retrieves the text, Mysql converts the text from setserver to setresult, and then send it to PHP.
The PHP file (the final HTML file) itself has an encoding. if the encoding passed by Mysql is different from the encoding of the PHP file itself, the entire web page will inevitably be garbled. Therefore, PHP generally tells Mysql about its encoding method.
To ensure that there are no garbled characters, three codes must be unified: one is the encoding of the webpage, and the other is the encoding specified in HTML, the third is the encoding that PHP tells Mysql (including character_set_client and character_set_results ).
The first and second codes are usually the same if you use an editor such as DW, but the webpages written in Notepad may be inconsistent.
The third encoding must be manually notified to Mysql. In this step, you can use mysql_query ("set names characterX") in PHP.
D. Character Set Conversion
If a small character set is converted to a large character set, data will not be lost. However, if a large word set is converted to a small character set, data may be lost.
For example, some characters in UTF8 may not exist in GB2312. Therefore, conversion from UTF8 to GB2312 may lose some characters.
However, in some cases, convert from GB2312 to UTF8 and then from UTF8 to GB2312. in this case, data will not be lost, because the converted text is all characters in GB2312, therefore, the entire process is GB2312 characters in conversion and will not be lost.
Because UTF8 can accommodate all the characters in the world, the database generally uses UTF8 encoding. This allows any character to be stored in a UTF8-encoded database.
E. PHPMyAdmin garbled
PHPMyAdmin supports multiple languages, which requires UTF8 encoding for HTML pages.
The HTML page uses UTF8 encoding, which requires that character_set_client and character_set_results use UTF8 encoding when PHPMyAdmin is connected to Mysql.
Currently, the PHP connection to Mysql can only use set names (or several other statements) to notify Mysql of the encoding method. If no explicit declaration encoding method is available, latin1 encoding is used. Generally, the character_set_client variable is not explicitly declared. Therefore, the gb2312 text is stored in the database in latin1 encoding mode, and PHPMyAdmin is read in utf8 format. it must be garbled.
If the PHP program is stored in the database according to the correct encoding, it is certainly no problem. Therefore, it is not PHPMyAdmin that needs to be modified. (although PHPMyAdmin can solve the garbled problem sometimes, it is not the root of the problem)
II. Summary
The above is a bit messy. here is a summary:
1. use utf8 for database storage whenever possible (modify/etc/my. cnf and add default-character-set = utf8 in the [mysqld] segment)
(Convert existing databases to UTF8 format first)
2. before the PHP program queries the database, execute mysql_query ("set names xxxx"). xxxx indicates the code of your webpage (charset = xxxx). if charset = utf8 in the webpage, then xxxx = utf8. if the charset in the webpage is gb2312, then xxxx = gb2312. if the charset in the webpage is ipaddr, xxxx = ipaddr (joke, no such code)
Almost all WEB programs have a piece of public code to connect to the database. put it in a file and add mysql_query ("set names") to the file.
3. PHPMyAdmin does not need to be modified.
4. Note that to ensure the actual encoding of the webpage (encoding in the Windows Save dialog box) and its declared encoding (charset = ?) Yes. use tools such as DW for web pages.
It was a little hasty. I hope you can correct and supplement it.