MySQL客戶端顯示漢字亂碼的解決
MySQL 服務端不加任何參數啟動時，客戶端可以默認正常顯示漢字，如下面所示
代碼:
D:mysql5>.binmysqld-max-nt.exe --console
061106 20:35:21 InnoDB: Started; log sequence number 0 43655
061106 20:35:22 [Note] .binmysqld-max-nt.exe: ready for connections.
Version: '5.0.18-nt-max' socket: '' port: 3306 MySQL Community Edition (GPL)
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:Documents and SettingsAdministrator>cd d:mysql5bin
C:Documents and SettingsAdministrator>d:
D:mysql5bin>mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.0.18-nt-max
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> use test
Database changed
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------------------------+
| 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:mysql5sharecharsets |
+--------------------------+---------------------------+
7 rows in set (0.00 sec)
mysql> create table ad(name varchar(32));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into ad values('漢字');
Query OK, 1 row affected (0.00 sec)
mysql> select * from ad;
+------+
| name |
+------+
| 漢字 |
+------+
1 row in set (0.00 sec)
mysql>
此時，插入、顯示字段的漢字值都正確。
但是如果服務器啟動的時候指定了字符集參數，或者配置文件里面包含字符集參數
情況就不同了
my.ini
[mysqld]
default-character-set=gbk
D:mysql5>.binmysqld-max-nt.exe --defaults-file=my.ini --ansi --console
InnoDB: Error: log file .ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 8388608 bytes!
061106 21:31:06 [Note] .binmysqld-max-nt.exe: ready for connections.
Version: '5.0.18-nt-max' socket: '' port: 3306 MySQL Community Edition (GPL)
061106 21:37:46 [Note] .binmysqld-max-nt.exe: Normal shutdown
061106 21:37:48 [Note] .binmysqld-max-nt.exe: Shutdown complete
D:mysql5>binmysqld-max-nt.exe --character_set_server=gbk --console
InnoDB: Error: log file .ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 8388608 bytes!
061106 21:39:55 [Note] binmysqld-max-nt.exe: ready for connections.
Version: '5.0.18-nt-max' socket: '' port: 3306 MySQL Community Edition (GPL)
此時，顯示過去未指定字符集時建立的表并插入漢字的字段，顯示是正常的
D:mysql5bin>mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.0.18-nt-max
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> use test
Database changed
mysql> select * from ad;
+------+
| name |
+------+
| 漢字 |
+------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------------------------+
| Variable_name | Value |
+--------------------------+---------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | gbk |
| character_set_results | latin1 |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | D:mysql5sharecharsets |
+--------------------------+---------------------------+
7 rows in set (0.00 sec)
這個時候建立的表并插入漢字的字段，不能正確顯示
mysql> create table ad2(name varchar(32));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into ad2 values('漢字');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from ad2;
+------+
| name |
+------+
| ??? |
+------+
1 row in set (0.01 sec)
如果只在客戶端設定character_set_results參數，那么不管服務器端是什么字符集時建立、插入漢字的表都顯示不正確
mysql> set character_set_results='gbk';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------------------------+
| Variable_name | Value |
+--------------------------+---------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | gbk |
| character_set_results | gbk |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | D:mysql5sharecharsets |
+--------------------------+---------------------------+
7 rows in set (0.00 sec)
mysql> select * from ad2;
+-------+
| name |
+-------+
| ??×? |
+-------+
1 row in set (0.00 sec)
mysql> select * from ad;
+-------+
| name |
+-------+
| ??×? |
+-------+
1 row in set (0.00 sec)
如果再設定character_set_client，效果和只設定character_set_results沒有什么差別
mysql> set character_set_client='gbk';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ad;
+-------+
| name |
+-------+
| ??×? |
+-------+
1 row in set (0.00 sec)
mysql> select * from ad2;
+-------+
| name |
+-------+
| ??×? |
+-------+
1 row in set (0.00 sec)
mysql> create table ad3(name varchar(32));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into ad3 values('漢字');
Query OK, 1 row affected (0.00 sec)
mysql> select * from ad3;
+------+
| name |
+------+
| ?? |
+------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------------------------+
| Variable_name | Value |
+--------------------------+---------------------------+
| character_set_client | gbk |
| character_set_connection | latin1 |
| character_set_database | gbk |
| character_set_results | gbk |
| character_set_server | gbk |
| character_set_system | utf8 |
| character_sets_dir | D:mysql5sharecharsets |
+--------------------------+---------------------------+
7 rows in set (0.00 sec)
如果再設定character_set_connection，不影響已經建立和插入的表，但是新建立、插入漢字的表就能正確顯示
mysql> set character_set_connection='gbk';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ad3;
+------+
| name |
+------+
| ?? |
+------+
1 row in set (0.00 sec)
mysql> select * from ad2;
+-------+
| name |
+-------+
| ??×? |
+-------+
1 row in set (0.00 sec)
mysql> select * from ad;
+-------+
| name |
+-------+
| ??×? |
+-------+
1 row in set (0.00 sec)
我們可以查看各個表的字符集
mysql> show create table ad;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------+
| ad | CREATE TABLE `ad` (
`name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table ad2;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| ad2 | CREATE TABLE `ad2` (
`name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table ad3;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| ad3 | CREATE TABLE `ad3` (
`name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
此時新建的表、此時插入漢字，顯示就正確了
mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> create table ad4(name varchar(32));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into ad4 values('漢字');
Query OK, 1 row affected (0.00 sec)
mysql> select * from ad;
+-------+
| name |
+-------+
| ??×? |
+-------+
1 row in set (0.00 sec)
mysql> select * from ad4;
+------+
| name |
+------+
| 漢字 |
+------+
1 row in set (0.00 sec)
mysql> create table ad5(name varchar(32)) DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into ad5 values('漢字');
Query OK, 1 row affected (0.00 sec)
mysql> select * from ad5;
+------+
| name |
+------+
| 漢字 |
+------+
1 row in set (0.00 sec)
mysql> select * from ad4;
+------+
| name |
+------+
| 漢字 |
+------+
1 row in set (0.00 sec)
mysql> show create table ad4;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| ad4 | CREATE TABLE `ad4` (
`name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table ad5;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| ad5 | CREATE TABLE `ad5` (
`name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table ad3;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------+
| ad3 | CREATE TABLE `ad3` (
`name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
--------------
由此，我們可以認識到，當服務器端指定字符集時。如果客戶端沒有指定character_set_results字符集，
那么可以正確顯示服務器端未指定字符集時建立、插入漢字的表。
如果要新建的表（默認字符集已經改變為服務器端指定字符集）正確顯示漢字，必須同時設定character_set_client和character_set_connection參數和
服務器端指定字符集保持一致，才能正確插入、顯示。
如果只是要顯示以前帶字符集正確插入漢字的表，可以只在客戶端指定character_set_results就夠了。
實際上服務器端和客戶端都未指定字符集就是所有字符集都默認latin1，也是保持一致的。
所以，如果要指定字符集，要在數據庫還不包含表的時候進行比較保險。如果中途改變，可能引起數據丟失。