MySQL Character set summary

Author:skate
time:2013/04/09

MySQL Character set summary

Today colleague Jie brother found inside a MySQL test server garbled, previously also recorded about the character set, and today to add

Modifying the MySQL character set and default storage engine
http://blog.csdn.net/wyzxg/article/details/7581415

To view the library's existing character set:
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 and character set-related variables
Character_set_client: Character set for client request data
Character_set_connection: A character set that receives data from a client and then transmits it
Character_set_database: The character set of the default database, regardless of the default database, is this character set, and if there is no default database, the character set specified by Character_set_server is used.

This variable is recommended to be managed by the system itself, not artificially defined.
Character_set_filesystem: Convert the file name of the OS to this character set, that is, the character_set_client conversion character_set_filesystem, the default binary is not to do any conversion
Character_set_results: Character set of the result set
Character_set_server: Default character set for the database server
Character_set_system: This value is always UTF8, does not need to be set, is the character set for storage system metadata

A complete user-requested character set conversion process is
1) When MySQL server receives the request, it converts the request data from Character_set_client to Character_set_connection
2) to convert the request data from character_set_connection to the internal operation character set before doing internal operations, proceed as follows
A. Use the character set value for each data field;
B. If the above value does not exist, use the character set of the corresponding data table to set the value
C. If the above value does not exist, use the corresponding database character set value;
D. If the above value does not exist, use Character_set_server to set the value.
3) Finally convert the operation result from the internal operation character set to Character_set_results

If we want to set the character set is UTF8, the steps are as follows:

1) Modify the configuration file
Add the following parameters under [client], affecting system variables character_set_client and Character_set_connection and Character_set_results
Default-character-set=utf8

Under [Mysqld], add the following parameters, affecting system variables Character_set_server and Character_set_database
Default-character-set=utf8

After setting up, start MySQL, we just need to see if 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, do not affect garbled
Character_set_filesystem
Character_set_system
Character_sets_dir

To modify the 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 over here? We have not adjusted the word Fu She (collation), the word Fu She determines the comparison rule between characters within 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 config file to control this parameter
Collation_server: Config file adjustment [Mysqld]collation_server=utf8_general_ci, be sure to use the same character set as [Mysqld]default-character-set]
Collation_database: adjust [mysqld]default-character-set] in config file to control this parameter

In general, if you want to adjust the character set to UTF8, you need to 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-sentence command, when the following three variables are inconsistent, or garbled, you can use this command

mysql> SET character_set_client = UTF8;
mysql> SET character_set_results = UTF8;
mysql> SET character_set_connection = UTF8;

View the installed Word Fu She
Mysql> Show collation;
To view the installed character set
Mysql> show Character set;

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

From: http://blog.csdn.net/wyzxg/article/details/8779682

MySQL Character set summary