查看: 69798|回复: 143

[精华] 搞懂oracle字符集

[复制链接]
认证徽章
论坛徽章:
139
2009日食纪念
日期:2009-07-22 09:30:00ITPUB8周年纪念徽章
日期:2009-09-27 10:21:21祖国60周年纪念徽章
日期:2009-10-09 08:28:002010年世界杯参赛球队:葡萄牙
日期:2010-01-18 09:23:302010年世界杯参赛球队:意大利
日期:2010-01-21 07:30:192010年世界杯参赛球队:南非
日期:2010-01-22 09:48:242010年世界杯参赛球队:加纳
日期:2010-02-13 16:34:422010新春纪念徽章
日期:2010-03-01 11:04:572010年世界杯参赛球队:斯洛伐克
日期:2010-05-21 11:24:312010年世界杯参赛球队:塞尔维亚
日期:2010-06-30 13:43:14
发表于 2007-8-22 08:30 | 显示全部楼层 |阅读模式
搞懂oracle字符集

作为一个ORACLE DBA,在工作中会经常处理由于字符集产生的一些问题。但是当真正想写一些这方面的东西时,却突然又没有了头绪。发了半天呆,还是决定用两个字符集方面的例子作为切入点,倒不失为一个头绪,说不定在实验的过程中,问题就会一个接着一个的浮现出来。
现在,让我们切入正题。
我用的数据库是oracle10.2.0.3,数据库字符集是al32utf8。
客户端就是同一台机器的windows xp.
下面是演示的例子:
[php]
        SQL> drop table test purge;
        Table dropped.
        SQL> create table test(col1 number(1),col2 varchar2(10));
        Table created.
[/php]
--session 1 设置客户端字符集为 zhs16gbk(修改注册表nls_lang项的characterset 为zhs16gbk) 向表中插入两个中文字符。
[php]
        SQL> insert into test values(1,'中国'); --1为session 1的标记
        1 row created.
        SQL> commit;
        Commit complete.
[/php]
--session 2 设置客户端字符集 al32utf8(修改注册表nls_lang项的characterset 为al32utf8),与数据库字符集相同。 向表中插入两个和session 1相同的中文字符。
[php]
        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 涓?浗
[/php]
        从session 1和session 2的结果中可以看到,相同的字符(注意,我指的是我们看到的,显示为相同的字符),在不同的字符集输入环境下,显示成了乱码。
  在zhs16gbk字符集的客户端,我们看到了utf8字符集客户端输入的相同的中文变成了乱码-->col1=2的col2字段
  在utf8字符集客户端,我们看到zhs16gbk字符集的客户端输入的中文变成了另外的字符  -->col1=1的col2字段
从这个例子里,我们好像感觉到出了什么问题,也可能会联想起现实环境中出现的乱码问题。
        问题似乎有了思路,ok,让我们继续把实验做下去:
[php]
--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                                                                  
[/php]
   我们使用了dump函数,结果看起来很明显了,两个完全相同的字符,在不同的字符集环境下,在数据库中存储成了不同的编码。
   对于ZHS16GBK的字符集客户端输入的字符"中国",AL32UTF8使用了3个字节来分别存储一个字符,即:
   中--e4,b8,ad
   国--e5,9b,bd
   我们也可以分别对这个字符进行验证:
[php]
--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 --字符“国” ,和上面直接从数据库中读取存储的字符编码一致。
[/php]
如果使用session 2直接对着两个字符进行测试,一样会得到相同的结果(笔者已经做过测试,这里为了避免冗长,删掉了).

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

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

        c:\chcp
        活动的代码页: 936
[/php]
通过oracle的官方文档阅读,我们可以将它等同于ZHS16GBK字符集(在安装oracle时,oracle会找到安装平台的字符集,并默认将对应的字符集设置成与它相同,在这里,数据库默认的字符集本身应该是ZHS16GBK,但我强制将它修改为AL32UTF8)。
所以现在我们可以认为,我们使用的操作系统是ZHS16GBk字符集,那么我们在这个环境下输入的字符(也可以说是显示的字符,用的就是这个字符集的编码)。
让我们继续讨论问题。
我们现在要讨论一下客户端字符集究竟是用来做什么的。
我们知道,很多字符集都有自己的编码方式,换句话说,相同的字符,在不同的字符集里对应的编码可能是不一样的。
客户端的字符集就是为了让数据库知道我们传递过去的字符是属于那种字符集,以便于oracle在存储字符时做相应的编码映射。
拿上面的例子来说:
比如字符"中国"
在ZHS16GBK字符集中,它的编码是:d6,d0,b9,fa
在AL32UTF8字符集中,它的编码是:e4,b8,ad,e5,9b,bd
让我们看看例子中两个session输入的相同字符在数据库中存储对应的编码:
[php]
        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
[/php]
对于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出来的时候,就得到下面这样的结果。
[php]
        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
[/php]
下面我们就进入到了我们最关心的地方,乱码,让我们继续我们的试验。
[php]
--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
[/php]
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
论坛徽章:
24
生肖徽章:狗
日期:2006-09-07 10:14:43数据库板块每日发贴之星
日期:2008-07-26 01:02:20生肖徽章2007版:兔
日期:2008-10-13 11:10:11奥运会纪念徽章:铁人三项
日期:2008-10-24 13:27:21开发板块每日发贴之星
日期:2008-12-27 01:01:09生肖徽章2007版:马
日期:2009-11-18 10:45:032010新春纪念徽章
日期:2010-03-01 11:21:02ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51ERP板块每日发贴之星
日期:2011-05-18 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-8-22 08:41 | 显示全部楼层
shafa!

支持!

使用道具 举报

回复
认证徽章
论坛徽章:
9
奥运会纪念徽章:体操
日期:2008-10-24 13:08:312009新春纪念徽章
日期:2009-01-04 14:52:28CTO参与奖
日期:2009-02-09 09:47:57祖国60周年纪念徽章
日期:2009-10-09 08:28:00参与WIN7挑战赛纪念
日期:2009-11-06 16:05:252010新春纪念徽章
日期:2010-03-01 11:19:502010广州亚运会纪念徽章:壁球
日期:2010-11-22 15:34:18秀才
日期:2015-08-28 09:13:22金牛座
日期:2015-08-28 09:13:22
发表于 2007-8-22 08:45 | 显示全部楼层
学习了一下.   辛苦了.
想知道, 如果 数据已经存在数据库中了 , 又不知 客户端的 情况, 如果 去修正这些数据.

使用道具 举报

回复
认证徽章
论坛徽章:
139
2009日食纪念
日期:2009-07-22 09:30:00ITPUB8周年纪念徽章
日期:2009-09-27 10:21:21祖国60周年纪念徽章
日期:2009-10-09 08:28:002010年世界杯参赛球队:葡萄牙
日期:2010-01-18 09:23:302010年世界杯参赛球队:意大利
日期:2010-01-21 07:30:192010年世界杯参赛球队:南非
日期:2010-01-22 09:48:242010年世界杯参赛球队:加纳
日期:2010-02-13 16:34:422010新春纪念徽章
日期:2010-03-01 11:04:572010年世界杯参赛球队:斯洛伐克
日期:2010-05-21 11:24:312010年世界杯参赛球队:塞尔维亚
日期:2010-06-30 13:43:14
发表于 2007-8-22 08:47 | 显示全部楼层
最初由 leniz 发布
[B]学习了一下.   辛苦了.
想知道, 如果 数据已经存在数据库中了 , 又不知 客户端的 情况, 如果 去修正这些数据. [/B]

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

使用道具 举报

回复
论坛徽章:
24
生肖徽章:狗
日期:2006-09-07 10:14:43数据库板块每日发贴之星
日期:2008-07-26 01:02:20生肖徽章2007版:兔
日期:2008-10-13 11:10:11奥运会纪念徽章:铁人三项
日期:2008-10-24 13:27:21开发板块每日发贴之星
日期:2008-12-27 01:01:09生肖徽章2007版:马
日期:2009-11-18 10:45:032010新春纪念徽章
日期:2010-03-01 11:21:02ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51ERP板块每日发贴之星
日期:2011-05-18 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-8-22 08:51 | 显示全部楼层
最初由 leniz 发布
[B]学习了一下.   辛苦了.
想知道, 如果 数据已经存在数据库中了 , 又不知 客户端的 情况, 如果 去修正这些数据. [/B]


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

使用道具 举报

回复
认证徽章
论坛徽章:
150
蓝锆石
日期:2011-11-16 22:31:22萤石
日期:2011-11-17 13:05:31祖母绿
日期:2008-06-14 15:23:26海蓝宝石
日期:2011-11-16 22:25:15紫水晶
日期:2011-11-16 22:31:22红宝石
日期:2011-10-09 08:54:30蓝锆石
日期:2009-01-31 15:20:54萤石
日期:2008-12-22 15:22:00祖母绿
日期:2011-11-17 13:13:26海蓝宝石
日期:2008-07-05 14:52:18
发表于 2007-8-22 08:52 | 显示全部楼层
不错
1、不过提点意见,修改client端的字符集信息时不用来来回回修改注册表了,在进入sqlplus之前设置下nls_lang就可以的
2、一个误区:
看到很多人在出现乱码的时候都首先要做的就是将客户端字符集设置和数据库一致,其实这是没有太多根据的
这句话应该值得商榷,我认为还是应该设置成一致的,如果server端和client端字符集设置的不一致,oracle在将字符从server端检索到client端或者从client端到server端都会发生转换,转不了的话就出现了乱码,至于操作系统的字符集,我自己认为对数据库字符集在存储上本身影响不大,仅仅影响的是输入和显示的问题
3 还在仔细拜读当中,欢迎大家热烈讨论字符集的问题,这是大问题啊

使用道具 举报

回复
认证徽章
论坛徽章:
150
蓝锆石
日期:2011-11-16 22:31:22萤石
日期:2011-11-17 13:05:31祖母绿
日期:2008-06-14 15:23:26海蓝宝石
日期:2011-11-16 22:25:15紫水晶
日期:2011-11-16 22:31:22红宝石
日期:2011-10-09 08:54:30蓝锆石
日期:2009-01-31 15:20:54萤石
日期:2008-12-22 15:22:00祖母绿
日期:2011-11-17 13:13:26海蓝宝石
日期:2008-07-05 14:52:18
发表于 2007-8-22 08:54 | 显示全部楼层
对了: alantany ,据说30号你们单位有批人要来大连,你过来吗?

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-8-22 08:56 | 显示全部楼层
支持原创

使用道具 举报

回复
招聘 : 技术支持/维护
论坛徽章:
6
数据库板块每日发贴之星
日期:2007-07-04 01:02:06数据库板块每日发贴之星
日期:2007-08-22 01:03:12数据库板块每日发贴之星
日期:2007-08-23 01:03:28会员2007贡献徽章
日期:2007-09-26 18:42:102009新春纪念徽章
日期:2009-01-04 14:52:28林肯
日期:2013-07-30 12:28:37
发表于 2007-8-22 08:59 | 显示全部楼层
搜藏,仔细读下

使用道具 举报

回复
论坛徽章:
17
授权会员
日期:2008-08-18 15:32:56阿斯顿马丁
日期:2013-07-29 18:48:42蛋疼蛋
日期:2013-05-21 11:22:192012新春纪念徽章
日期:2012-01-04 11:49:54ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41SQL大赛参与纪念
日期:2011-04-13 12:08:172011新春纪念徽章
日期:2011-03-21 14:26:302011新春纪念徽章
日期:2011-02-18 11:42:47ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522010年世界杯参赛球队:塞尔维亚
日期:2010-01-07 09:54:43
发表于 2007-8-22 09:01 | 显示全部楼层

总结得不错

仔细地读过了.LZ辛苦了.

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表