http://jackxiang.com/index.php zh-cn http://jackxiang.com/post// jack <xdy108@126.com> Wed, 15 Oct 2008 07:12:07 +0000 http://jackxiang.com/post//
MySQL 服务端不加任何参数启动时，客户端可以默认正常显示汉字，如下面所示

    PHP code:


    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=utf8 |


    +-------+----------------------------------------------------------------------------------------------+


    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，也是保持一致的。
所以，如果要指定字符集，要在数据库还不包含表的时候进行比较保险。如果中途改变，可能引起数据丢失。
]]> http://jackxiang.com/post//#blogcomment <user@domain.com> Thu, 01 Jan 1970 00:00:00 +0000 http://jackxiang.com/post//#blogcomment