Summary of Character Set meanings and usage in MySQL (1)

If it is reprinted, please indicate the source. Thank you.
1. DISPLAY variable values related to character sets
Mysql> show variables like 'chara % ';
+ -------------------------- + ------------------------------------------- +
| Variable_name | value |
+ -------------------------- + ------------------------------------------- +
| Character_set_client | Latin1 |
| Character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir | D:/mysql-5.0.9-beta-win32/share/charsets/|
+ -------------------------- + ------------------------------------------- +
7 rows in SET (0.02 Sec)

Mysql> show variables like 'collation % ';
+ ---------------------- + ------------------- +
| Variable_name | value |
+ ---------------------- + ------------------- +
| Collation_connection | latin1_swedish_ci |
| Collation_database | latin1_swedish_ci |
| Collation_server | latin1_swedish_ci |
+ ---------------------- + ------------------- +
3 rows in SET (0.00 Sec)

It must be noted that Collation is a sort-related character set variable. The preceding variable value is the default Character Set variable in MySQL.

Ii. Significance of various variables related to character sets
Character_set_client: client character set (character set used by the application client), which is actually related to the current session.
Character_set_connection: Connection character set. It refers to the character set used in MySQL network transmission and is also related to sessions.
Character_set_database: character set used for physical storage in database tables.
Character_set_results: character set used to return results after SQL query processing.
Character_set_server: character set used by the MySQL database server.
Character_set_system: character set used to store metadata in the database.

Collation_connection: Meaning and so on

3. Character Set Modification
We can modify a single variable, as shown in figure
Set character_set_client = GBK
You can also use set names to modify several variables at the same time, as shown in figure
Set names 'gbk' allows you to modify character sets all at once
It is equivalent:
Set character_set_client = X;
Set character_set_results = X;
Set character_set_connection = X;

It is worth noting that the collation_connection variable is associated with character_set_connection by default, that is
Collation_connection depends on the value of character_set_connection.
For example:
Mysql> set names 'gbk ';
Query OK, 0 rows affected (0.08 Sec)

Mysql> show variables like 'chara % ';
+ -------------------------- + ------------------------------------------- +
| Variable_name | value |
+ -------------------------- + ------------------------------------------- +
| Character_set_client | GBK |
| Character_set_connection | GBK |
| Character_set_database | Latin1 |
| Character_set_results | GBK |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir | D:/mysql-5.0.9-beta-win32/share/charsets/|
+ -------------------------- + ------------------------------------------- +
7 rows in SET (0.00 Sec)

Mysql> show variables like 'collation % ';
+ ---------------------- + ------------------- +
| Variable_name | value |
+ ---------------------- + ------------------- +
| Collation_connection | gbk_chinese_ci |
| Collation_database | latin1_swedish_ci |
| Collation_server | latin1_swedish_ci |
+ ---------------------- + ------------------- +
3 rows in SET (0.00 Sec)
We can see that set names 'gbk' changes the value of character_set_client, character_set_connection, character_set_results, and collation_connection.
To specify collation_name separately, you can use
Set names 'charset _ name' collate 'collation _ name'

Another command to modify multiple variables is:
Set character set X is equivalent:
Set character_set_client = X;
Set character_set_results = X;
Set collation_connection =collation_database;
You can experience the changes to the character set variable value caused by this command.

How can I modify the value of character_set_server and character_set_database? Direct modification in the command line does not work.
The best way is to directly modify it in the configuration file my. ini, and add the following under the [mysqld] item:
Default_character_set = GBK
Of course, if you want to try it, it is not recommended to use mysqld-NT -- default_character_set = GBK to start the database.

The search order of my. ini files is:
Before Version 4.1.5, it must be under C:/My. CNF or C:/Windows/My. in. This is to be tested.
In subsequent versions, my. INI is under the MySQL installation directory by default.

Note:
MySQL -- default-character-set = GBK-u Root
Or
MySQL -- defaults-file = C:/My. ini-u Root
Add the following content to my. ini:
[MySQL] or [client]
Default-character-set = GBK
Or
Mysql-u Root
Then run set names 'gbk'
These three functions are identical.

4. Generation of garbled characters
Garbled characters are mainly caused by inconsistent character set variables.
The operating system has a locale variable to be used when displaying characters. You can see it using the locale command in Linux.
Using chcp in windows, we can see that the code page corresponding to GBK should be 936.
If the OS has a conversion table for the current Character Set and the target character set installed, the characters in the target character set are correctly displayed. Otherwise, garbled characters are displayed.
Latin1 corresponds to the iso8859_1 character set. By default, it can be converted to GBK, at least at the operating system level.

ZZ: http://blog.csdn.net/iihero/archive/2006/09/19/1246191.aspx