Java Tools

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  83 随笔 :: 0 文章 :: 16 评论 :: 0 Trackbacks
 

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,也是保持一致的。
所以,如果要指定字符集,要在数据库还不包含表的时候进行比较保险。如果中途改变,可能引起数据丢失。
posted on 2007-07-19 20:56 和田雨 阅读(5014) 评论(0)  编辑  收藏 所属分类: MySQL

只有注册用户登录后才能发表评论。


网站导航: