搞懂ORACLE字符集（转）

作为一个ORACLE DBA,在工作中会经常处理由于字符集产生的一些问题。但是当真正想写一些这方面的东西时，却突然又没有了头绪。发了半天呆，还是决定用两个字符集方面的例子作为切入点，倒不失为一个头绪，说不定在实验的过程中，问题就会一个接着一个的浮现出来。
现在，让我们切入正题。
我用的数据库是oracle10.2.0.3,数据库字符集是al32utf8。
客户端就是同一台机器的windows xp.
下面是演示的例子：

SQL> drop table test purge;
Table dropped.
SQL> create table test(col1 number(1),col2 varchar2(10));
Table created.

--session 1 设置客户端字符集为 zhs16gbk（修改注册表nls_lang项的characterset 为zhs16gbk） 向表中插入两个中文字符。

SQL> insert into test values(1,'中国'); --1为session 1的标记
1 row created.
SQL> commit;
Commit complete.

--session 2 设置客户端字符集 al32utf8（修改注册表nls_lang项的characterset 为al32utf8），与数据库字符集相同。 向表中插入两个和session 1相同的中文字符。

SQL> insert into test values(2,'中国'); --2为session 2的标记
1 row created.
SQL> commit;
Commit complete.

--session 1
SQL> select * from test;
COL1 COL2
---------- --------------------
2 ？??
1 中国
--session 2
SQL> select * from test;
COL1 COL2
---------- ----------
2 中国
1 涓浗

从session 1和session 2的结果中可以看到，相同的字符（注意，我指的是我们看到的，显示为相同的字符），在不同的字符集输入环境下，显示成了乱码。
在zhs16gbk字符集的客户端，我们看到了utf8字符集客户端输入的相同的中文变成了乱码-->col1=2的col2字段
在utf8字符集客户端，我们看到zhs16gbk字符集的客户端输入的中文变成了另外的字符 -->col1=1的col2字段
从这个例子里，我们好像感觉到出了什么问题，也可能会联想起现实环境中出现的乱码问题。
问题似乎有了思路，ok，让我们继续把实验做下去：

--session 1 （或者session 2，在这里无所谓）
SQL> select col1,dump(col2,1016) from test;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd

我们使用了dump函数，结果看起来很明显了，两个完全相同的字符，在不同的字符集环境下，在数据库中存储成了不同的编码。
对于ZHS16GBK的字符集客户端输入的字符"中国"，AL32UTF8使用了3个字节来分别存储一个字符，即：
中--e4,b8,ad
国--e5,9b,bd
我们也可以分别对这个字符进行验证：

--session 1
SQL> select dump('中',1016) from dual;
DUMP('中',16)
--------------------------------------------
Typ=96 Len=3 CharacterSet=AL32UTF8: e4,b8,ad --字符“中” ，和上面直接从数据库中读取存储的字符编码一致。
SQL> select dump('国',1016) from dual;
DUMP('国',16)
--------------------------------------------
Typ=96 Len=3CharacterSet=AL32UTF8: e5,9b,bd --字符“国” ，和上面直接从数据库中读取存储的字符编码一致。

如果使用session 2直接对着两个字符进行测试，一样会得到相同的结果（笔者已经做过测试，这里为了避免冗长，删掉了）.

让我们重新来理一下思路，并提出几个问题：
1：为什么显示为相同的字符，存储到数据库中却变成了不同的编码？
2：我们在向数据库中插入数据的时候，oracle究竟做了些什么？
3：操作系统字符集，客户端字符集，数据库字符集究竟是什么关系？

带着这些疑惑，让我们接着做实验，所有的疑团和猜测都会在试验中得以验证。
我的思路是，先取得测试环境的相关参数。
1：windows字符集(codepage)
我们使用chcp命令来获得windows使用的字符集


c:chcp
活动的代码页: 936

通过oracle的官方文档阅读，我们可以将它等同于ZHS16GBK字符集（在安装oracle时，oracle会找到安装平台的字符集，并默认将对应的字符集设置成与它相同，在这里，数据库默认的字符集本身应该是ZHS16GBK,但我强制将它修改为AL32UTF8）。
所以现在我们可以认为，我们使用的操作系统是ZHS16GBk字符集，那么我们在这个环境下输入的字符（也可以说是显示的字符，用的就是这个字符集的编码）。
让我们继续讨论问题。
我们现在要讨论一下客户端字符集究竟是用来做什么的。
我们知道，很多字符集都有自己的编码方式，换句话说，相同的字符，在不同的字符集里对应的编码可能是不一样的。
客户端的字符集就是为了让数据库知道我们传递过去的字符是属于那种字符集，以便于oracle在存储字符时做相应的编码映射。
拿上面的例子来说：
比如字符"中国"
在ZHS16GBK字符集中，它的编码是：d6,d0，b9,fa
在AL32UTF8字符集中，它的编码是:e4,b8,ad,e5,9b,bd
让我们看看例子中两个session输入的相同字符在数据库中存储对应的编码：

SQL> select col1,dump(col2,1016) from t1;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd

对于session 1,我们设置的客户端字符集为zhs16gbk。
当我们和数据库建立session后，数据库将认为这个客户端以zhs16gbk字符集编码的方式向数据库发送字符，因为数据库的字符集是al32utf8,所以字符要以这个字符集的编码来存储，此时oracle就会做一个字符编码转换，也就是将字符集zhs16gbk中编码为d6,d0,b9,fa 的字符编码映射成字符集为al32utf8编码为e4,b8,ad,e5,9b,bd，在字符集al32utf8的编码里，e4,b8,ad,e5,9b,bd对应的字符为"中国".
对于session 2,我们设置的客户端字符集为al32utf8。
当我们和数据库建立session后，数据库看到客户端的字符集和数据库的字符集一致，此时oracle将不会再对字符作转换，因为它认为两边的字符编码是一致的。而此时，我们欺骗了数据库，尽管我们将客户端字符集设置为和数据库一致，但是其实我们使用的是zhs16gbk字符集编码（因为此时windows使用的就是这个字符编码），对于字符"中国",zhs16gbk字符集里对应的编码为d6,d0,b9,fa。此时，oracle不加理会的直接将这个编码保存到了数据库中。当我们分别将这两个字符dump出来的时候，就得到下面这样的结果。

SQL> select col1,dump(col2,1016) from test;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd

下面我们就进入到了我们最关心的地方，乱码，让我们继续我们的试验。

--session 1
SQL>
SQL> insert into t1 values('中国',1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL COL2
------------ ----------
中国 1
？?? 2
--session 2
SQL> insert into t1 values('中国',2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL COL2
------ ----------
涓浗 1
中国 2

session 1,我们看到session 2输入的字符"中国"变成了乱码"？??",
session 2,我们看到session 1输入的字符"中国"变成了另外的字符"涓浗",
下面我们来分析一下这中间数据库，客户端和操作系统都发生了那些事情。
上面已经讨论了：
session 1 输入的字符"中国" 在数据库中存储的字符编码为”e4,b8,ad,e5,9b,bd".
session 2 输入的字符"中国" 在数据库中存储的字符编码为”d6,d0,b9,fa".
当session 1开始查询时，oracle从表中取出这两个字符，并按照字符集al32utf8和字符集zhs16gbk的编码映射表，将它的转换成zhs16gbk字符编码，对于编码“e4,b8,ad,e5,9b,bd”，它对应的zhs16gbk的字符编码为"d6,d0,b9,fa",这个编码对应的字符为”中国“，所以我们看到了这个字符正常显示出来了，而对于字符集al32utf8字符编码“d6,d0,b9,fa”，由于我们用于显示字符的windows环境使用的是zhs16gbk字符集,而在zhs16gbk字符集里面并没有对应这个编码的字符或者属于无法显示的符号，于是使用了"?"这样的字符来替换，这就是为什么我们看到session 2输入的字符变成了这样的乱码。
当session 2开始查询时，oracle从表中取出这两个字符，由于客户端（nls_lang)和数据库的字符集设置一致，oracle将忽略字符的转换问题，于是直接将数据库中存储的字符返回给客户端。对于编码为"d6,d0,b9,fa"的字符，返回给客户端，而客户端显示所用的字符集正好是zhs16gbk，在这个字符集里，这个编码对应的是"中国"两个字符，所以就正常显示出来了。对于字符编码“e4,b8,ad,e5,9b,bd”，返回到客户端後，因为在zhs16gbk里采用的是双字节存储字符方式，所以这6字节对应了zhs16gbk字符集的3个字符,也就是我们看到的"涓浗".

到现在为止，我想我们基本上搞清楚了为什么日常查询时会遇到乱码的问题。
其实乱码，说到底就是用于显示字符的操作系统没有在字符编码中找到对应的字符导致的，造成这种现象的主要原因就是：
1：输入操作的os字符编码和查询的os字符编码不一致导致出现乱码。
2：输入操作的客户端字符集(nls_lang)和查询客户端字符集（nls_lang)不同，也可能导致查询返回乱码或者错误的字符。

还有一个问题需要解释一下：
在上面的例子中，相同的字符在不同的字符集中对应着不同的字符编码，这个通常称为字符集不兼容或者不完全兼容，比如zhs16gbk和al32utf8,他们存储的ascii码的字符编码都是相同的，但对于汉字却是不同的。
如果两个字符集对于相同的字符采用的相同的字符编码，我们称之为字符兼容，范围大的叫做范围小的字符集的超级。我们通常遇到的zhs16cgb231280,zhs16gbk就是这样的情况，后者是前者的超级。

在实际的环境中除了字符显示之外，还有其他的地方会涉及到字符集问题。比如：
1:exp/imp
2:sql*lorder
3：应用程序的字符输入
......
一个误区：
看到很多人在出现乱码的时候都首先要做的就是将客户端字符集设置和数据库一致，其实这是没有太多根据的。
设想一下，假如数据库字符集是al32utf8,里面存储这一些中文字符，而我的客户端操作系统是英文的，此时我将客户端的nls_lang设置成al32utf8，这样会解决问题吗？这样客户端就能显示中文了吗？客户端就能输入中文了吗？现在客户端是英文的，它的字符集里根本就没有汉字的编码，我们简单的修改一下客户端的字符集又有什么用？前面已经讨论了，这个设置无非就是告诉oracle我将以什么样的字符集与数据库进行数据交换，对于解决乱码问题毫无关系。
正确的做法是将客户端的操作系统改成支持中文字符，并将客户端字符集改成和操作系统一致的字符集，这样才能真正的解决问题。
--作者 alantany

 

通过学习得知

如果数据库服务器的字符集为AL32UTF8，操作系统的活动的代码页为936即Simplified Chinese GBK（相对应的ORACLE charset为ZHS16GBK）那么NLS_LANG的字符集段应设置为ZHS16GBK，和操作系统一样如NLS_LANG=AMERICAN_AMERICA.ZHS16GBK.

这样设置后，当用EXP,IMP进行数据迁移时，只要源和目标数据库的字符集一致，EXP和IMP的SESSION中设置的NLS_LANG字符集和数据库的字符集可以转换，那就永远不会出现乱码问题。