搞懂oracle字符集 - Oracle数据库管理 - ITPUB论坛

ITPUB论坛 » Oracle数据库管理 » 搞懂oracle字符集

大型有奖征文:讲述DBA自己的故事

‹‹ 上一主题 | 下一主题 ››

100 1/10 1 2 3 4 5 6 7 8 9 10 ››

alantany
版主

精华贴数 2
个人空间 0
技术积分 11833 (95)
社区积分 341 (1612)
注册日期 2001-9-28
论坛徽章:36

使用道具

发表于 2007-8-22 08:30

搞懂oracle字符集

搞懂oracle字符集

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

PHP code:



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）向表中插入两个中文字符。

PHP code:



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相同的中文字符。

PHP code:



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 中国


            &nb

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

PHP code:



--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,


--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使用的字符集

PHP code:



c:chcp


    活动的代码页: 


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,


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,


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


    中国          &nb

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

__________________

世事洞明皆学问，人情练达即文章。 MSN:alantany@gmail.com

只看该作者

hanjs
高级会员

精华贴数 1
个人空间 0
技术积分 9012 (126)
社区积分 51 (4560)
注册日期 2006-7-30
论坛徽章:14

使用道具

发表于 2007-8-22 08:41

shafa！

支持！

__________________

Database Concepts
Database Performance Tuning Guide and Reference

只看该作者

leniz
中级会员

精华贴数 0
个人空间 70
技术积分 339 (5519)
社区积分 1 (37976)
注册日期 2005-8-20
论坛徽章:0

使用道具

发表于 2007-8-22 08:45

学习了一下. 辛苦了.
想知道, 如果数据已经存在数据库中了 , 又不知客户端的情况, 如果去修正这些数据.

只看该作者

alantany
版主

精华贴数 2
个人空间 0
技术积分 11833 (95)
社区积分 341 (1612)
注册日期 2001-9-28
论坛徽章:36

使用道具

发表于 2007-8-22 08:47

QUOTE:

最初由 leniz 发布
学习了一下. 辛苦了.
想知道, 如果数据已经存在数据库中了 , 又不知客户端的情况, 如果去修正这些数据.

其实我上面已经写的很清楚了。你要看你的问题出在哪个环节上。
关于字符集的设置，最好的方式是:
数据库字符集-unicode
客户端字符集=操作系统字符集

__________________

世事洞明皆学问，人情练达即文章。 MSN:alantany@gmail.com

只看该作者

hanjs
高级会员

精华贴数 1
个人空间 0
技术积分 9012 (126)
社区积分 51 (4560)
注册日期 2006-7-30
论坛徽章:14

使用道具

发表于 2007-8-22 08:51

QUOTE:

最初由 leniz 发布
学习了一下. 辛苦了.
想知道, 如果数据已经存在数据库中了 , 又不知客户端的情况, 如果去修正这些数据.

LZ的是避免出现这个的数据，如果数据都已经这样了，数据库中存放的脏数据是否需要更新的问题，你试一试LZ说的，看看能行不？

__________________

Database Concepts
Database Performance Tuning Guide and Reference

只看该作者

warehouse
自由职业

来自大连
精华贴数 3
个人空间 0
技术积分 17344 (51)
社区积分 522 (1271)
注册日期 2002-8-16
论坛徽章:50

使用道具

发表于 2007-8-22 08:52

不错

1、不过提点意见，修改client端的字符集信息时不用来来回回修改注册表了，在进入sqlplus之前设置下nls_lang就可以的
2、一个误区：
看到很多人在出现乱码的时候都首先要做的就是将客户端字符集设置和数据库一致，其实这是没有太多根据的这句话应该值得商榷，我认为还是应该设置成一致的，如果server端和client端字符集设置的不一致，oracle在将字符从server端检索到client端或者从client端到server端都会发生转换，转不了的话就出现了乱码，至于操作系统的字符集，我自己认为对数据库字符集在存储上本身影响不大，仅仅影响的是输入和显示的问题
3 还在仔细拜读当中，欢迎大家热烈讨论字符集的问题，这是大问题啊