关于ORACLE和MYSQL中文字符乱码的根源剖析

关于数据库的字符集问题一直都是一个比较恶心的问题，如果不了解其实质可能一直
都搞不清楚这个问题的根源，只能出了问题去度娘，这里我打算兼容ORACLE和MYSQL对字符集
的处理来描述乱码出现的情形和如何防止乱码问题出现的可能，本文只用UTF-8和GBK为
例子进行描述，请大家先记住'去'这个字的UTF8和GBK编码，因为整个文章将用'去'字为
例子进行讲述
GBK     UTF8   中文
C8A5    E58EBB  去
同时整篇文章数据库DATABASE端的字符集始终为UTF8
一般来讲我们所说的乱码一般来自于非英文字符，如中文，因为英文是ASCII中进行了定义的
所有的编码方式一致

首先我们从2个报错的例子来进行描述
ORACLE:
NLS_LANG设置NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
终端设置为UTF8
SQL> create table testchar(name varchar2(20));
Table created.
SQL> insert into testchar values('去');
ERROR:
ORA-01756: quoted string not properly terminated

MYSQL:
character_set_client       gbk
character_set_connection   gbk 
character_set_results      gbk
终端设置为UTF8
mysql> create table testchar(name varchar(20));
Query OK, 0 rows affected (0.25 sec)
mysql> insert into testchar values('去');
ERROR 1406 (22001): Data too long for column 'name' at row 1
终端设置为UTF8

咋一看这两个报错不知所云啊，明明我给的varchar是20，ORACLE直接
报错单引号没有正确结束，MYSQL更狠直接报超长。
其实这就是字符集引起的错误。我会慢慢讲述

首先解释有关的字符集：
1、DATABASE字符集   数据库字符集就是你期望的使用存储数据的字符集，但是这里说只是期望的，比如我的数据库是 
                    UTF8，是不是只能存储UTF8的字符呢?当然答案是否定的，他可以存储任何字符集的字符，其实
                    字符存储在文件中就是一堆二进制0和1，要看你的终端怎么解释他们了。比如如果文件中存储是
                    二进制E58EBB，那么你用UF8的终端字符集去解释他就是'去'字，用GBK去解释就是乱码。
                    ORALCE中可以查看nls_database_parameters 中的NLS_CHARACTERSET查看数据库的字符集
                    MYSQL中可以查看character_set_database参数查看数据库的字符集
2、CLIENT转换字符集 转换字符集用于让数据库服务端知道客户端过来的数据是什么字符集，如果过来的不是数据库的字符集
                    则进行转换，当然转换必须是兼容的字符集，如果CLIENT使用的是GBK而DATABASE字符集是UTF8那么进行
                    SQL解析的时候会进行转为相应的字符集GKB->UT8，也就是说CLIENT字符集代表是你告诉数据库你使用的什么字符集
                    然后数据库来决定是否需要转换。
                    ORACLE中使用NLS_LANG来设置 如NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                    MYSQL中使用set names进行设置他实际改变了三个参数character_set_client,character_set_connection
                    和character_set_results
3、终端字符集       这里终端的字符集是你录入数据和显示数据的字符集，比如我用的securtCRT伪终端就是在
                    会话选项-->终端-->外观-->字符编码，当然如果不使用伪终端那么LINUX中使用的locale进行
                    查看终端的字符编码，export LC_ALL=en_US.gbk 可以更改所有的，具体可以自己看一下。
                    当然windows也有
                    
那么了解了3种字符集我们来简单描述一下：
如果我设置终端为GBK,CLIENT转换字符集为GBK,DATABASE字符集为UTF8，那么我们输入一个'去'字，并且SELECT出来的流程为

终端以GBK字符集编码录入数据为C8A5--->
数据来到了CLIENT比如SQL命令窗口编码为C8A5--->
回车后对SQL进行解析CLIENT的字符集为GBK和DATABASE UTF8不同进行转换为E58EBB--->
数据进入DATAFILE 存储格式为E58EBB--->
终端SQL命令窗口发起SELECT查看要求插叙数据'去'--->
DATABASE查询数据文件读取E58EBB返回给用户SESSION--->
用户SESSION发现CLIENT字符集为GBK进行转为C8A5--->
终端显示数据解析C8A5为GBK格式显示为'去'

整个过程大概就是这样，如果一个环节出现问题可能出现乱码，比如转换后为C8A5但是终端为UTF8格式
那么必然乱码

回到刚才的问题
mysql> insert into testchar values('去');
ERROR 1406 (22001): Data too long for column 'name' at row 1

SQL> insert into testchar values('去');
ERROR:
ORA-01756: quoted string not properly terminated

由于我的CLIENT字符集设置为GBK，而终端字符集设置为UTF8，数据库字符集为UTF8

那么我们录入的数据编码为E58EBB，然后进行解析由于CLIENT设置为GBK，DATABASE为UTF8，认为要
进行转换，那么叫E58EBB当做GBK进行转为UTF8。当然就出现了问题因为E58EBB压根不是GBK的编码
转出来肯定是乱码，那么我们如何纠正呢？
1、设置CLIENT字符集设置为UTF8
或者
2、设置终端字符集为GBK
都可以

接下来我们来验证我们的说法。
一、在ORACLE和MYSQL中设置终端字符集为GBK,设置CLIENT字符集为UTF8，DATABASE字符集为UT8
   那么这种情况下我们插入'去'字，那么终端是C8A5，而CLIENT字符集和DTABASE字符集相同
   不会进行转换，这样就吧GBK的字符编码数据存入了UTF8的字符集的库，接下来演示
1、ORACLE
SQL> insert into testchar values('去');
1 row created.
SQL> select dump(name,16) from testchar;

DUMP(NAME,16)
--------------------------------------------------------------------------------
Typ=1 Len=2: c8,a5

我们看到我们的UTF8字符集的库存储进了C8A5为GBK字符集数据。
当然查看也没有问题因为我们的终端字符集为GBK，它会自动进行转换
SQL> select * from testchar;
NAME
--------------------
去
如果我们设置终端字符集为UTF8那么就出现了乱码
SQL> r
  1* select * from testchar

NAME
--------------------
?
2、MYSQL

mysql> show variables like '%char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | utf8                        |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | /mysql/test/share/charsets/ |
+--------------------------+-----------------------------+
mysql> insert into testchar values('去');
Query OK, 1 row affected (0.00 sec)
直接查看数据文件的使用工具bcview(是我自己用C语言编写的)，来查看实际的数据文件
testchar.ibd提取出其数据。
可以在百度云
http://pan.baidu.com/s/1num76RJ
下载到
[root@hadoop1 test]# bcview testchar.ibd 16 146 2;
******************************************************************
This Tool Is Uesed For Find The Data In Binary format(Hexadecimal)
Usage:./bcview file blocksize offset cnt-bytes!                   
file: Is Your File Will To Find Data!                             
blocksize: Is N kb Block.Eg: 8 Is 8 Kb Blocksize(Oracle)!         
                         Eg: 16 Is 16 Kb Blocksize(Innodb)!       
offset:Is Every Block Offset Your Want Start!                                     
cnt-bytes:Is After Offset,How Bytes Your Want Gets!                               
Edtor QQ:22389860!                                                
Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)                
******************************************************************
----Current file size is :0.093750 Mb
----Current use set blockszie is 16 Kb
current block:00000000--Offset:00146--cnt bytes:02--data is:0002
current block:00000001--Offset:00146--cnt bytes:02--data is:0000
current block:00000002--Offset:00146--cnt bytes:02--data is:ffff
current block:00000003--Offset:00146--cnt bytes:02--data is:c8a5
current block:00000004--Offset:00146--cnt bytes:02--data is:0000
current block:00000005--Offset:00146--cnt bytes:02--data is:0000

这里块00000003就是这个表的第一个数据块关于如何使用BCVIEW和查看
数据参考我的博客
http://blog.itpub.net/7728585/viewspace-2071787/
可以看到数据为c8a5为GBK的字符集编码。
如果这个时候我们修改终端字符集为UTF8，也会出现乱码
mysql> select * from testchar;
+------+
| name |
+------+
| ?    |
+------+
1 row in set (0.00 sec)
这样一看ORACLE MYSQL都是一样的没有问题。乱码的出现在于GBK编码的字符集不能在
终端进行UTF8编码的解析。

二、那么如何将正确的UTF8 '去'字的编码E58EBB存入到数据库呢？其实前面已经给出了答案

将CLIENT字符集设置为GBK同时终端字符集设置为GBK
或者CLIENT字符集设置为UTF8同时终端设置为UTF8   

我们使用复杂一点的将CLIENT字符集设置为GBK同时终端字符集设置为GBK
测试
1、ORACLE
设置终端字符集为GBK同时
[oradba@hadoop1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
SQL> insert into testchar values('去');
1 row created.

SQL> commit;
Commit complete.

SQL> select dump(name,16) from testchar;
DUMP(NAME,16)
--------------------------------------------------------------------------------
Typ=1 Len=3: e5,8e,bb

可以看到e5,8e,bb为我们正确的UTF8字符集表面的'去'字

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

mysql> show variables like '%char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | gbk                         |
| character_set_connection | gbk                         |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | gbk                         |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | /mysql/test/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.01 sec)

[root@hadoop1 test]# bcview testchar.ibd 16 146 3;
current block:00000003--Offset:00146--cnt bytes:03--data is:e58ebb
可以看到e58ebb为UTF8的字符集编码了。
SQL> select * from testchar;

NAME
----------------------------------------
?

这个时候乱码是因为回显的时候 utf8 服务端字符集编码 e58ebb--> GBK CLIENT字符集转换c8a5-->utf8 终端解析乱码

那么也许大家要问如何最大限度的避免乱码的出现
方案为
1、设置终端字符集(或者确定.sql文件)为UTF8、CLIENT字符集为UTF8、database字符集为UTF8。
   这样不存在转换完全取决于你终端的输入的字符的编码，关于有时候我们要SOURCE SQL文件，
   那么如果我们设置终端字符集为UTF8然后more一下这个文件如果看到的中文没有问题，如果没有
   问题那么就能你能正常导入数据。
2、按照需求设置比如你文件或者终端是GBK编码，那么你必须设置CLIENT字符集为GBK,也就是说转换
   字符集修改为你终端输入或者文件本身的编码。这样才能正常的进行转化不会出现乱码。

同时在重点强调一下数据库database的字符集为UTF8并不表示只能存储UTF8的字符编码，这个已经在前面的
例子验证过。
   
最后测试一下WINDOWS 我们修改环境变量为
american_america.AL32UTF8
sqlplusw /nolog
终端字符集为
C:/Users/Administrator>chcp
活动代码页: 936
936是GBK编码

然后查看本来的'去'字
SQL> connect sys/gelc123@192.168.190.30:1521/test as sysdba
Connected.
SQL> select * from testchar;

NAME
--------------------
鍘?

发现出现问题乱码，为什么呢？因为WINDOWS终端本生的字符集编码为GBK，
而你去告诉ORACLE 不需要进行转换，那么正确的UTF8编码e5,8e,bb被原封不动
的传输给终端，终端用GBK进行解析当然GBK解析e5,8e,bb肯定乱码。
我们只需要修改转换字符集为
american_america.ZHS16GBK

再次查看
SQL> connect sys/gelc123@192.168.190.30:1521/test as sysdba
Connected.
SQL> select * from testchar;

NAME
----------------------------------------
去

没有问题。