MySQL客户端显示汉字乱码的解决
D:mysql5>.binmysqld-max-nt.exe --console061106 20:35:21 InnoDB: Started; log sequence number 0 43655061106 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:mysql5binC:Documents and SettingsAdministrator>d:D:mysql5bin>mysql -urootWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 1 to server version: 5.0.18-nt-maxType 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> use testDatabase changedmysql> 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=gbkD:mysql5>.binmysqld-max-nt.exe --defaults-file=my.ini --ansi --consoleInnoDB: Error: log file .ib_logfile0 is of different size 0 5242880 bytesInnoDB: 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 shutdown061106 21:37:48 [Note] .binmysqld-max-nt.exe: Shutdown completeD:mysql5>binmysqld-max-nt.exe --character_set_server=gbk --consoleInnoDB: Error: log file .ib_logfile0 is of different size 0 5242880 bytesInnoDB: 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 -urootWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 1 to server version: 5.0.18-nt-maxType 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql> use testDatabase changedmysql> 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> exitBye?