设置MySQL客户端连接使用的字符集

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

时间：2014-03-05 来源：服务器之家投稿：root

考虑什么是一个“连接”：它是连接服务器时所作的事情。客户端发送SQL语句如查询，通过连接发送到服务器，服务器通过连接发送响应给客户端如结果集。
对于客户端连接，这样会导致一些关于连接的字符集和校对规则的问题，这些问题均能够通过系统变量来解决：
i   当查询离开客户端后，在查询中使用哪种字符集？
character_set_client变量“声明告知”服务器客户端的字符集是什么，它和字符串文字的引介词起着同样的作用。注意，是“声明”，故实际上有可能不是，即客户端可以用这个“声明”来欺骗服务器，从而“隐藏”自己真正的字符集。
ii  服务器接收到查询后应该转换为哪种字符集？
当服务器接到客户端发来的语句后，它会将“这串字符”做一层字符编码的映射转换。它会把字符串从character_set_client字符集转换到character_set_connection字符集（除非字符串文字具有象_latin1或_utf8的引介词）。
iii 服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集？
character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据（如列名）。
iv  字符串最后存储是哪种字符集？
字符串最终保存下来是什么字符集，这个是分别受到server、db、table和column四个级别的定义控制的，它们决定了字符串最终以什么字符集存储。
v   不同字符集之间会发生转换吗？
当字符串在不同字符集之间“穿梭”时，会发生字符集编码的映射转换，如果发生相互转换的两种字符集不是相互兼容的，那么字符串就有可能会发生丢失。

在mysql server中，下面的两个语句影响连接字符集：
SET NAMES 'charset_name';
SET CHARACTER SET charset_name;
SET NAMES显示客户端发送的SQL语句中使用什么字符集。因此SET NAMES 'cp1251'语句告诉服务器“将来从这个客户端传来的信息采用字符集cp1251”。它还为服务器发送回客户端的结果指定了字符集（如你使用一个SELECT语句，它表示列值使用了什么字符集）。SET NAMES 'x'语句与这三个语句等价：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x;
将x设置为character_set_connection也就设置了collation_connection是x的默认校对规则。
SET CHARACTER SET语句是类似的，但是为默认数据库设置连接字符集和校对规则。SET CHARACTER SET x语句与这三个语句等价：

mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
当一个客户端连接时，它向服务器发送希望使用的字符集名称，服务器把变量character_set_client、character_set_results和character_set_connection设置为该字符集，即实际上服务器为使用该字符集执行一个SET NAMES操作。

对于mysql客户端，如果你希望使用与默认字符集不同的字符集，不需要每次启动时执行SET NAMES语句，那么可以在mysql语句行中或者选项文件中添加一个--default-character-set选项设置。例如，你每次运行mysql时，以下的选项文件设置把三个字符集变量修改为koi8r：
[mysql]
default-character-set=koi8r
例如：假设column1定义为CHAR(5) CHARACTER SET latin2。如果没有设定SET NAMES或SET CHARACTER SET，那么对于SELECT column1 FROM t，当连接后，服务器使用客户端指定的字符集（即character_set_client声明的字符集）返回列column1的所有值。另一方面，如果你设定SET NAMES 'latin1'或SET CHARACTER SET latin1，那么发送结果之前，服务器转换latin2值到latin1。转换可能会丢失那些不属于两种字符集的字符。如果不希望服务器执行任何转换，设置character_set_results为NULL：
mysql> SET character_set_results = NULL;

下面我来做实验来证明上面所说的内容。
首先，用chcp命令可以查询到自己的cmd客户端是什么编码格式：
C:\Documents and Settings\shengtong>chcp
活动的代码页: 936  -- 936就是表示gbk的编码的意思
然后，在gbk编码对照表中，查询出汉字“中”的编码是D6D0。至此，已经清楚cmd客户端的字符集和汉字“中”gbk编码了。

第一个实验，我们保证character_set_client变量和character_set_connect变量一样，即保证声明的客户端和服务器是一样的编码，这样字符串传到服务器的时候就不需要转换，然后将字符串存储下来。
mysql> create table t (a varchar(100),b varchar(20),c int) default character set=gbk;  -- 创建一个最终以gbk字符集存储数据的表t
Query OK, 0 rows affected (0.06 sec)

mysql> set names 'gbk';  -- 在服务器中“声明告知”客户端的字符集的“实情”，即character_set_client变量通过set names设置为gbk
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('中','gbk',1);
Query OK, 1 row affected (0.05 sec)

mysql> select a,b,c,hex(a),hex('中') from t;
+------+------+------+--------+-----------+
| a | b | c | hex(a) | hex('中') | -- hex(a)列显示了表字段a的存储格式，hex('中')显示了gbk编码格式
+------+------+------+--------+-----------+
| 中 | gbk | 1 | D6D0 | D6D0 |
+------+------+------+--------+-----------+
1 row in set (0.00 sec)

这里可以清楚的看到表t正确的存储了汉字“中”，同时也表名了“中”的gbk编码为D6D0。解释一下这个过程，客户端字符集是gbk，然后其通过set names想server声明其字符集为gbk，并且底层表的字符集也是gbk，故一路下来在字符串的穿梭中没有发生任何字符集的转换，其最终在表t中正确的存储了汉字中，并且是正确的编码D6D0。查询时，同样字符串从存储引擎到server到客户端的传递游离中并没有发生字符集的转换，一切都可以正确显示出来。

mysql> set names 'latin1'; -- 对服务器“隐瞒”客户端字符集的“真实面目”，声明了一个错误的character_set_client变量为latin1
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('中','latin1',2);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select a,b,c,hex(a),hex('中') from t;
+------+--------+------+--------+-----------+
| a    | b      | c    | hex(a) | hex('中') |
+------+--------+------+--------+-----------+
| ?    | gbk    |    1 | D6D0   | D6D0      |
| ??   | latin1 |    2 | 3F3F   | D6D0      |
+------+--------+------+--------+-----------+
2 rows in set (0.00 sec)

mysql> create table t1 ( a varchar(10)) default character set=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('中');
Query OK, 1 row affected (0.00 sec)

通过SET NAMES，客户端向服务器声明它使用的是latin1字符集，但实际上它欺骗了它，它实际上上是gbk字符集。由于character_set_client和character_set_connection两个变量相同，故在server中他们是不需要转换的，故hex('中')的值仍然是D6D0。由于表的字符集是gbk，故当存储数据的时候就会发生一次字符集的编码映射转换。转换的过程是这样的，由于latin1是单字节编码字符集，那么传过来的两个字节0xD6和0xD0，会根据其在latin1中对应的“字”，然后转到相应的gbk编码的“字”，然后根据这个gbk字编码存储起来。在转换发生时，发现latin1字符集中的0xD6和0xD0在gbk找不到对应的字，故报warnings，无法完成转换，那么就只有插入个无意义的“字”，故就用gbk编码的无意义字0x3F存储了起来（？的编码在所有字符集中都是0x3F，而且对某个字符集不能表示的编码所对应的字符就用？）。但是在utf8的编码表中latin1编码为D6和D0的“字”能够正确表示，故能将他们存储起来。
解释一下，为什么不能正确查看c=1的这行记录。因为这里通过set names已经将character_set_results设置为latin1了，故数据从表中出来以后，会做一层转换，从gbk转到latin1。转换时，发现“中”在latin1中无法显示，故以0x3F编码的？来显示了。
接下来接着验证一下latin1和gbk中0xD6和0xD0是什么字符，？的编码等：
mysql> select _latin1 x'D6';
+---------------+
| _latin1 x'D6' |
+---------------+
| ? |
+---------------+
1 row in set (0.01 sec)

mysql> select _latin1 x'D0';
+---------------+
| _latin1 x'D0' |
+---------------+
| ? |
+---------------+
1 row in set (0.00 sec)

mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)

mysql> select hex('?');
+----------+
| hex('?') |
+----------+
| 3F |
+----------+
1 row in set (0.03 sec)

mysql> set names 'latin1';
Query OK, 0 rows affected (0.00 sec)

mysql> select hex('?');
+----------+
| hex('?') |
+----------+
| 3F |
+----------+
1 row in set (0.00 sec)

以上转换过程，看看在变成字节编码的utf8中是不是也是根据我们所阐述的发生着转换。在utf8中，汉字的编码都是3个字节编码的：
mysql> set names 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('中','utf8',3);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select a,b,c,hex(a),hex('中') from t;
+------+--------+------+--------+-----------+
| a    | b      | c    | hex(a) | hex('中') |
+------+--------+------+--------+-----------+
| 涓? | gbk    |    1 | D6D0   | D6D0      |
| ??   | latin1 |    2 | 3F3F   | D6D0      |
| ?    | utf8   |    3 | 3F     | D6D0      |
+------+--------+------+--------+-----------+
3 rows in set (0.00 sec)

mysql> select _utf8 x'D6D0D3'; -- 这里说明0xD6D0在utf8中是无法显示的编码，故其最终以字符“？”代替，且就一个，那么存到gbk编码中就说一个0x3f编码的？了。
ERROR 1300 (HY000): Invalid utf8 character string: 'D6D0D3'

上面这个实验已经很好的说明了mysql中字符集之间的映射转换过程，整个实验是保证character_set_client和character_set_connection、character_set_results一样来完成的，那么如果他们不一样呢？
请看实验二：
mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)

mysql> select hex('中');
+-----------+
| hex('中') |
+-----------+
| D6D0 |
+-----------+
1 row in set (0.00 sec)

mysql> set character_set_client=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select hex('中');
+-----------+
| hex('??') |
+-----------+
| C396C390 |
+-----------+
1 row in set (0.02 sec)

mysql> set character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select hex('中');
+-----------+
| hex('中') |
+-----------+
| C396C390 |
+-----------+
1 row in set (0.00 sec)

latin1编码为0xd6和0xd0对应的utf8编码为0xc396和0xc390，client到connection完成了正确的转换，而hex函数返回的结果都是英文字母和数字，故不论results设置成什么，结果都是c396c390。

实验三，再来看看character_set_results参与的转换：
mysql> set character_set_client=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select '中';
+----+
| 中 |
+----+
| 中 |
+----+
1 row in set (0.00 sec)