PHP MySQL garbled

Recently, many posts have been posted about garbled Chinese characters, so I also summarized them as follows:

For more information, see Yang taotao's summary of various gibberish issues.
Http://topic.csdn.net/u/20071124/08/3b7eae69-ed1d-4a77-8895-9930bf3601af.html

Principles of the MySQL character set. Extracted from the official document. Http://dev.mysql.com/doc/refman/5.1/zh/charset.html

Different encoding formats may lead to the same character, and the encoding in different character sets will be different. The characters of the same Code in different character sets are also different. When the encoding format (character set) of the string returned by your MySQL is associated with your client tool Program (MySQL, PHP, query browser ,...) if the character set used is different, garbled characters are generated. For example, if a British friend tells you long, when a Chinese elementary school student sees it, he will tell you "dragon" instead of "long"

It is recommended that you take a moment to take a look at the detailed introduction and examples of character sets.
Http://dev.mysql.com/doc/refman/5.1/zh/charset.html (Chapter 1: character set support ).

Here is a summary.

In MySQL, the default character set is set to four levels: Server-level, database-level, and table-level. The final result is field-level Character Set settings. Note that the first three are default settings, and your fields will use this character set setting instead of code. Therefore, we recommend that you use show create table table; or show full fields from tablename; to check the character set settings of fields in the current table.

MySQL sets the character set of the connection environment with the client, connection, and results parameters. Through these parameters, MySQL will know what character set your client tool uses and what character set the result set should be. In this way, MySQL will perform necessary translation. Once these parameters are incorrect, the conversion errors of strings during the conversion process will naturally occur. Basically, 99% of garbled characters are caused by some.

Information to be checked after garbled characters. (If you need help from friends on the Forum, we suggest you provide the following information)

1. Character Set settings for fields in the database table. Show create table tablename or show full columns from tablename

mysql> show create table t1;+-------+------------------------------------| Table | Create Table                      +-------+------------------------------------| t1    | CREATE TABLE `t1` (  `id` int(11) NOT NULL,  `c1` varchar(30) DEFAULT NULL,  PRIMARY KEY (`id`)   ) ENGINE=InnoDB DEFAULT CHARSET=gbk |+-------+------------------------------------1 row in set (0.00 sec)                       mysql> show full columns from t1;+-------+-------------+----------------+------+-----+-| Field | Type        | Collation      | Null | Key |+-------+-------------+----------------+------+-----+-| id    | int(11)     | NULL           | NO   | PRI || c1    | varchar(30) | gbk_chinese_ci | YES  |     |+-------+-------------+----------------+------+-----+-2 rows in set (0.00 sec)mysql>

2. The currently connected system parameter show variables like 'Char %'

mysql> show variables like 'char%';+--------------------------+----------------| Variable_name            | Value+--------------------------+----------------| character_set_client     | gbk| character_set_connection | gbk| character_set_database   | latin1| character_set_filesystem | binary| character_set_results    | gbk| character_set_server     | latin1| character_set_system     | utf8| character_sets_dir       | C:/Program File+--------------------------+----------------8 rows in set (0.00 sec)mysql>

1. Chinese. Make sure that the character set of this field in the table is Chinese compatible:
 

big5     | Big5 Traditional Chinese gb2312   | GB2312 Simplified Chinese gbk      | GBK Simplified Chinese utf8     | UTF-8 Unicode

2. Make sure that the join parameter is consistent with the character set of this field. You can use set name 'charsetname ';
For example, Set Name 'gbk ';
This command modifies both character_set_client, character_set_connection, character_set_results
(If you use Chinese Characters in MySQL, you can add or modify this parameter in my. ini or my. CNF. After modifying the parameter file, you must restart the MySQL service)
[MySQL]
Default-character-set = GBK

3. php garbled characters, also mysql_query ("Set Name 'gbk'"); other APIs are similar.

4. garbled characters in phpMyAdmin
Does $ cfg ['defaultcharset'] = 'utf-8' set in config. Inc. php of phpMyAdmin ';

5. In Windows, run the command line ("dos" window.
Left click on the title bar in the upper left corner of your DOS window, properties,
In the font, select "" to confirm
Set names 'gbk' in MySQL ';

6. In ADO. NET and ADO, charset = utf8 can be added to the connection string. Similar commands are used to describe the character set of connection.
Server = myserveraddress; database = mydatabase; uid = myusername; Pwd = mypassword; charset = utf8;

7. SQL manager for MySQL

Use EMS to create a database,

Character Set to UTF-8

Client charset set UTF-8

Font charset is set to gb2312_charset

8. jdbcodbc bridging http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/bridge.html

   // Load the JDBC-ODBC bridge driver       Class.forName(sun.jdbc.odbc.JdbcOdbcDriver) ;       // setup the properties       java.util.Properties prop = new java.util.Properties();       prop.put( " charSet " , " Big5 " );       prop.put( " user " , username);       prop.put( " password " , password);       // Connect to the database       con = DriverManager.getConnection(url, prop);

9. php 5.2 and later versions solve the garbled problem (provided by ljf_ljf [Mark Liang)

 $conn = mysql_connect ( " 192.168.1.133 " , " root " , " 123456 " ) or       die ( " Could not connect: " . mysql_error ());    $program_char = " utf8 " ;    $conn . mysql_select_db ( " test " );    // $conn.mysql_query('SET @@character_set_results = "'.$program_char.'"');       mysql_set_charset( $program_char , $conn );    $charset = mysql_client_encoding ( $conn );    printf ( " current character set is %s" , $charset );    $result = mysql_query ( " SELECT id, task_no,pack_path FROM tb_workplan where id = 1 " , $conn );    while ( $row = mysql_fetch_array ( $result , MYSQL_BOTH)) {        printf ( " ID: %stask_no: %s pack_path :%s <BR> " , $row [ " id " ] , $row [ 1 ] , $row [ " pack_path " ]);     }    $conn . mysql_free_result ( $result );    $conn . mysql_close ();

9. garbled stored procedure parameters

Create procedure T (AA char (10) charset 'gbk ')

It took a whole morning to see PHP garbled characters.

1. When MySQL creates databases and tables with fields, it is normalized to UTF-8 for MySQL to convert itself.
When reading data from MySQL, add mysql_query ("set names gb2312"); convert to gb2312 encoding, and add header ("Content-Type: text/html; charset = gb2312 "); the output is Chinese and no garbled characters are displayed. If you want to save data to MySQL, convert the encoding to UTF-8.

2. Set all MySQL databases, tables, and fields to GBK encoding. Set mysql_query ("set names GBK") when writing data to the database. when outputting data, set the header encoding to GBK. If the output format is XML, the xml header is also encoded as GBK. If the request link contains Chinese parameters, you can determine the browser first and set the encoding according to the browser.

3. When querying the MySQL database, the results of the corresponding encoding format are output based on the encoding format of the queried fields.