MySQL Character Set Summary

Author: skate
Time: 2013/04/09

MySQL Character Set Summary

 

Today, my colleague, brother a, found a MySQL test server with garbled characters. He has recorded character sets before. I will try again today.

 

Modify the character set and default storage engine of MySQL
Http://blog.csdn.net/wyzxg/article/details/7581415

 

View the existing character sets of the Library:
Mysql> show variables like '% char % ';
+ -------------------------- + -------------------------------- +
| Variable_name | value |
+ -------------------------- + -------------------------------- +
| Character_set_client | Latin1 |
| Character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir |/MySQL/share/MySQL/charsets/|

MySQL variables related to character sets
Character_set_client: Character Set of the client request data
Character_set_connection: the character set that the client receives and then transmits
Character_set_database: the character set of the default database. No matter how the default database changes, it is the character set. If no default database exists, use the character set specified by character_set_server,

We recommend that you manage this variable by the system rather than manually defining it.
Character_set_filesystem: converts the OS file name to this character set, that is, character_set_client is converted to character_set_filesystem. By default, binary is not converted.
Character_set_results: Character Set of the result set
Character_set_server: Default Character Set of the Database Server
Character_set_system: this value is always utf8. It is a character set that stores system metadata.

 

A complete character set conversion process for user requests is
1) MySQL Server converts the request data from character_set_client to character_set_connection when receiving the request
2) Before performing internal operations, convert the request data from character_set_connection to the internal operation character set. The steps are as follows:
A. Set the character set value for each data field;
B. If the preceding value does not exist, use the character set of the corresponding data table to set the value.
C. If the preceding value does not exist, use the character set value of the corresponding database;
D. If the preceding value does not exist, use character_set_server to set the value.
3) Finally, convert the operation result from the internal character set to character_set_results.

To set the character set to utf8, follow these steps:

1) modify the configuration file
Add the following parameters under [client] to affect the system variables character_set_client and character_set_connection and character_set_results.
Default-character-set = utf8

Add the following parameters under [mysqld] to affect the system variables character_set_server and character_set_database.
Default-character-set = utf8

After setting, start mysql. We only need to check whether the system variables meet our requirements.
Character_set_client
Character_set_connection
Character_set_database
Character_set_results
Character_set_server

The following three system variables do not need to be concerned and will not affect garbled characters:
Character_set_filesystem
Character_set_system
Character_sets_dir

 

Modification result:
Mysql> show variables like '% char % ';
+ -------------------------- + -------------------------------- +
| Variable_name | value |
+ -------------------------- + -------------------------------- +
| Character_set_client | utf8 |
| Character_set_connection | utf8 |
| Character_set_database | utf8 |
| Character_set_filesystem | binary |
| Character_set_results | utf8 |
| Character_set_server | utf8 |
| Character_set_system | utf8 |
| Character_sets_dir |/MySQL/share/MySQL/charsets/|

 

Is it all done here? We have not adjusted the collation. The collation determines the comparison rules between characters in the same character set.

 

Mysql> show variables like '% colla % ';
+ ---------------------- + ------------------- +
| Variable_name | value |
+ ---------------------- + ------------------- +
| Collation_connection | utf8_general_ci |
| Collation_database | utf8_general_ci |
| Collation_server | utf8_general_ci |
+ ---------------------- + ------------------- +

Collation_connection: Adjust [client] default-character-set in the configuration file to control this parameter.
Collation_server: Adjust [mysqld] collation_server = utf8_general_ci in the configuration file, which must use the same character set as [mysqld] default-character-Set
Collation_database: Adjust [mysqld] default-character-set in the configuration file to control this parameter.

To adjust the character set to utf8, add the following items in the configuration file:

[Client]
Default-character-set = utf8

[Mysqld]
Default-character-set = utf8
Collation_server = utf8_general_ci

Command description:
Mysql> set names 'utf8'; it is equivalent to the following three commands. You can use this command when the following three variables are inconsistent or garbled.

Mysql> set character_set_client = utf8;
Mysql> set character_set_results = utf8;
Mysql> set character_set_connection = utf8;

View the installation collation
Mysql> show collation;
View installed character sets
Mysql> show character set;

 

Refer:
Http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html