MySQL Character Set setting-MySQL database garbled

MySQL (Versions later than 4.1) servers use character sets in six key locations: client, connection, database, results, server, and system. MySQL has two character set concepts: character set itself and character set verification rules. Character sets affect how data is processed during transmission and storage, while character set verification affects order by and group by sorting methods.

1. Storage-related
Server character set (@ character_set_server)
Library character set (@ character_set_database)
Table Character Set
Field Character Set
Character_set_server: the default character set specified during Server installation.
Character_set_database: character set used by a database on the database server. If it is not specified during database creation, the character set specified during Server installation will be used.
Character_system: character set used by the database system.
When creating a table, each field will have a character set as long as it is not binary. If this parameter is not specified, it will not be displayed during show create table.
When creating a table, the field character set is selected as follows:
* Character set specified by the if field
* Character set specified in the else if table
* Else if @ character_set_database
* Else @ character_set_server (if not set, this value is latin1)

2. Transfer-related
@ Character_set_connection
@ Character_set_results
@ Character_set_client
Character_set_connection: Specifies the character set setting type for connecting to the database. If www.bkjia.com does not specify the character set type used to connect to the database, it is set according to the default character set on the server.
Character_set_results: character set used by the database to return data to the client. If not specified, use the default Character Set of the server.
Character_set_client: character set used by the client, which is equivalent to the character set setting in the web page.

3. Collation rules
The character_set_connection, character_set_database, and character_set_server determine the collation settings of character sets.
Collation_connection: checking rules for connected character sets
Collation_database: The checking rules used by the default database. When the default database changes, the server sets this variable. If no default database exists, the variable value is the same as collation_server.
Collation_server: Default Server verification rules
In the above content, character_set_client, character_set_connection, and character_set_results are affected by the default Character Set of the client. The default character set when php compiles the mysql module is also affected by the mysql dynamic library linked to it, thus affecting php character, character_set_client settings. When the default character set is not utf8, set my. cnf

[Mysqld]
Default-character-set = utf8
Default-collation = utf8_general_ci
Default-character-set can only change the settings of the storage layer (server, database, table, column, system), and has no impact on the communication layer between the client and the server.
Mysql> show variables like 'character _ set _ % ';
+ --------- + ------------------------- +
| Variable_name | Value |
+ --------- + ------------------------- +
| Character_set_client | latin1 |
| Character_set_connection | latin1 |
| Character_set_database | utf8 |
| Character_set_filesystem | binary |
| Character_set_results | latin1 |
| Character_set_server | utf8 |
| Character_set_system | utf8
To resolve the mismatch of Character Set communication settings:
(Modify the default character_set_client, character_set_connection, character_set_result)
1. recompile mysql and php, and add the compilation parameter-default-character-set = utf8 to mysql.
2. Before the PHP program queries the database, execute mysql_query ("set names utf8 ;");
3. modify my. cnf and add init-connect = "set names utf8" to [mysqld] (this option is invalid for Super User connection)
4. Start mysqld and add the parameter-skip-character-set-client-handshake to ignore the client character set.

1 2 Next Page