Summary of garbled characters in MySQL: System variables of four character sets in MySQL 5

Many times of changing the MySQL host provider, MySQL data may be garbled, because the website development history has gone through multiple MySQL versions, and I did it a little bit when I was learning PHP, because the underlying things are messy, I didn't know how to handle the string at the time. Today I decided to study it well. Mysql5 provides the following system variables for setting character sets: character_set_client Character Set character_set_connection character set used for client-to-server connection character_set_results select to query the character set of returned data character_set_database the character set garbled problem is generally caused by incorrect settings of the preceding variables, so as long as you understand these variables, you can get rid of the garbled characters. To use the preceding variables, we need to understand this core idea: character_set_client and character_set_connection must be consistent with character_set_database encoding, while character_set_results must be consistent with the result returned by the SELECT statement.Program. We can use set names in the program to set the three system variables character_set_client, character_set_connection, and character_set_results at the same time.
For example, set names 'utf8' is equivalent:

Set @ character_set_client = 'utf8'

Set @ character_set_connection = 'utf8'

Set @ character_set_results = 'utf8' generally, when the character set of the database and database tables is utf8, we can set the set names 'utf8' command in the program, this ensures that no garbled characters exist. However, note the value of the character_set_results variable. The character_set_results character value is used to display the encoding returned to the user.
For example, if your database (character_set_database) uses the utf8 character set, you must ensure that character_set_client and character_set_connection are also the utf8 character set. However, your program may not use utf8. For example, if your program uses GBK, you may encounter garbled characters if you set character_set_results to utf8. In this case, set character_set_results to GBK. This ensures that the database returns the same result as the encoding of your program.

The following is a program section from the network:
<? PHP
// Assume that our program uses the utf8 character set.

$ Program_char = 'utf8 ';

// Check the mysql version number first. if the version number is greater than 4, you can set these system variables (mysql4 does not have these system variables)

$ Version = Current ($ db-> fetch_one ('select version ()'));

If (substr ($ version, 0, 1)> 4 ){

// Retrieve the character set of the current database

$ SQL = 'select @ character_set_database ';

$ Char = Current ($ db-> fetch_one ($ SQL ));

// Set the client character set (character_set_client) and connection character set (character_set_connection) to be consistent with the database character set (character_set_database)

$ Db-> query ('set @ character_set_client = "'. $ Char .'"');

$ Db-> query ('set @ character_set_connection = "'. $ Char .'"');

// Set the character set of the data returned by the SELECT query to be consistent with the character set of the current program

$ Db-> query ('set @ character_set_results = "'. $ program_char .'"');

}

?>

1. Ensure that the data stored in the database is consistent with the database encoding, that is, the data encoding is consistent with character_set_database;

2. Ensure that the character sets for communication are consistent with those for databases, that is, character_set_client and character_set_connection are consistent with character_set_database;

3. Ensure that the returned results of select are encoded in the same way as those of the program, that is, character_set_results is encoded in the same way as the program;

4. Ensure that the program encoding is consistent with the browser encoding, that is, the program encoding is consistent with <meta http-equiv = "Content-Type" content = "text/html; charset =? "/> Consistent.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/piperzero/archive/2009/03/07/3964554.aspx