mysql字符集設定

MYSQL伺服器中有六個關鍵位置使用了字符集的概念，分別是：client 、connection、database、results、server、system. MYSQL有兩個和字符集有關的概念。一個是字符集本身，一個是字符集校驗規則。字符集影響資料在傳輸、儲存過程中的處理方式，自己校驗則影響order by ,group by這些排序方式。
和儲存有關的
伺服器字符集 character_set_server
庫字符集 character_set_database
表字符集
欄位字符集
character_set_server：伺服器安裝時指定的預設字符集設定
character_set_database：資料庫伺服器中某個庫使用的字符集設定，如果建庫時沒有明確指定，將使用伺服器安裝時指定的字符集設定。
character_set_system：資料庫系統使用的字符集設定
和傳輸有個的
character_set_connection：連線資料庫的字符集設定型別，如果沒有明確指定連線資料庫使用的字符集型別就安裝伺服器端預設的字元設定
character_set_results：資料庫給客戶端返回時使用的字符集設定，如果沒有指明，使用伺服器預設的字符集。
character_set_client：客戶端使用的字符集
字符集的校對規則
字符集的校對規則設定分別有character_set_connection，character_set_database，character_set_server決定
collation_connection:連線字符集的校對規則
collation_database:預設資料庫使用的校對規則
collation_server:伺服器的預設校對規則
character_set_connection，character_set_client，character_set_results受客戶端預設字符集影響，當預設字符集不是utf8時，設定my.cnf
[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci
default-character-set只能改變儲存層（server,database,table,column,system）的設定,對客戶端和服務端的通訊層則沒有任何影響。
解決字符集通訊層設定不匹配的方法：
在mysql服務端的配置檔案my.cnf的mysqld下設定
[mysqld]
...
skip-character-set-client-handshake
...
忽略客戶端字符集，所有字符集和服務端保持一致。
沒加skip-character-set-client-handshake引數時，登入檢視字符集為：
[root@agent1 src]# mysql -h 192.168.151.141 -uxxx -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show variables like '%character%';
+--------------------------+----------------------------------------+
| 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                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
加了skip-character-set-client-handshake引數時，登入檢視字符集為：
[root@agent1 src]# mysql -h 192.168.151.141 -uxxx -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show variables like '%character%';
+--------------------------+----------------------------------------+
| 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       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.01 sec)