搞懂oracle字符集

搞懂oracleoracleoracleoracle字符集作为一个ORACLEDBA,在工作中会经常处理由于字符集产生的一些问题。但是当真正想写一些这方面的东西时，却突然又没有了头绪。发了半天呆，还是决定用两个字符集方面的例子作为切入点，倒不失为一个头绪，说不定在实验的过程中，问题就会一个接着一个的浮现出来。现在，让我们切入正题。我用的数据库是oracle10.2.0.3,数据库字符集是al32utf8。客户端就是同一台机器的windowsxp.下面是演示的例子：SQLdroptabletestpurge;Tabledropped.SQLcreatetabletest(col1number(1),col2varchar2(10));Tablecreated.--session1设置客户端字符集为zhs16gbk（修改注册表nls_lang项的characterset为zhs16gbk）向表中插入两个中文字符。SQLinsertintotestvalues(1,'中国');--1为session1的标记1rowcreated.SQLcommit;Commitcomplete.--session2设置客户端字符集al32utf8（修改注册表nls_lang项的characterset为al32utf8），与数据库字符集相同。向表中插入两个和session1相同的中文字符。SQLinsertintotestvalues(2,'中国');--2为session2的标记1rowcreated.SQLcommit;Commitcomplete.--session1SQLselect*fromtest;COL1COL2------------------------------2？??1中国--session2SQLselect*fromtest;COL1COL2--------------------2中国1涓 浗从session1和session2的结果中可以看到，相同的字符（注意，我指的是我们看到的，显示为相同的字符），在不同的字符集输入环境下，显示成了乱码。在zhs16gbk字符集的客户端，我们看到了utf8字符集客户端输入的相同的中文变成了乱码--col1=2的col2字段在utf8字符集客户端，我们看到zhs16gbk字符集的客户端输入的中文变成了另外的字符--col1=1的col2字段从这个例子里，我们好像感觉到出了什么问题，也可能会联想起现实环境中出现的乱码问题。问题似乎有了思路，ok，让我们继续把实验做下去：--session1（或者session2，在这里无所谓）SQLselectcol1,dump(col2,1016)fromtest;COL1----------DUMP(COL2,1016)--------------------------------------------------------------------------------2Typ=1Len=4CharacterSet=AL32UTF8:d6,d0,b9,fa1Typ=1Len=6CharacterSet=AL32UTF8:e4,b8,ad,e5,9b,bd我们使用了dump函数，结果看起来很明显了，两个完全相同的字符，在不同的字符集环境下，在数据库中存储成了不同的编码。对于ZHS16GBK的字符集客户端输入的字符中国，AL32UTF8使用了3个字节来分别存储一个字符，即：中--e4,b8,ad国--e5,9b,bd我们也可以分别对这个字符进行验证：--session1SQLselectdump('中',1016)fromdual;DUMP('中',16)--------------------------------------------Typ=96Len=3CharacterSet=AL32UTF8:e4,b8,ad--字符“中”，和上面直接从数据库中读取存储的字符编码一致。SQLselectdump('国',1016)fromdual;DUMP('国',16)--------------------------------------------Typ=96Len=3CharacterSet=AL32UTF8:e5,9b,bd--字符“国”，和上面直接从数据库中读取存储的字符编码一致。如果使用session2直接对着两个字符进行测试，一样会得到相同的结果（笔者已经做过测试，这里为了避免冗长，删掉了）.让我们重新来理一下思路，并提出几个问题：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输入的相同字符在数据库中存储对应的编码：SQLselectcol1,dump(col2,1016)fromt1;COL1----------DUMP(COL2,1016)--------------------------------------------------------------------------------2Typ=1Len=4CharacterSet=AL32UTF8:d6,d0,b9,fa1Typ=1Len=6CharacterSet=AL32UTF8:e4,b8,ad,e5,9b,bd对于session1,我们设置的客户端字符集为zhs16gbk。当我们和数据库建立session后，数据库将认为这个客户端以zhs16gbk字符集编码的方式向数据库发送字符，因为数据库的字符集是al32utf8,所以字符要以这个字符集的编码来存储，此时oracle就会做一个字符编码转换，也就是将字符集zhs16gbk中编码为d6,d0,b9,fa的字符编码映射成字符集为al32utf8编码为e4,b8,ad,e5,9b,bd，在字符集al32utf8的编码里，e4,b8,ad,e5,9b,bd对应的字符为中国.对于session2,我们设置的客户端字符集为al32utf8。当我们和数据库建立session后，数据库看到客户端的字符集和数据库的字符集一致，此时oracle将不会再对字符作转换，因为它认为两边的字符编码是一致的。而此时，我们欺骗了数据库，尽管我们将客户端字符集设置为和数据库一致，但是其实我们使用的是zhs16gbk字符集编码（因为此时windows使用的就是这个字符编码），对于字符中国,zhs16gbk字符集里对应的编码为d6,d0,b9,fa。此时，oracle不加理会的直接将这个编码保存到了数据库中。当我们分别将这两个字符dump出来的时候，就得到下面这样的结果。SQLselectcol1,dump(col2,1016)fromtest;COL1----------DUMP(COL2,1016)--------------------------------------------------------------------------------2Typ=1Len=4CharacterSet=AL32UTF8:d6,d0,b9,fa1Typ=1Len=6CharacterSet=AL32UTF8:e4,b8,ad,e5,9b,bd下面我们就进入到了我们最关心的地方，乱码，让我们继续我们的试验。--session1SQLSQLinsertintot1values('中国',1);1rowcreated.SQLcommit;Commitcomplete.SQLselect*fromt1;COLCOL2----------------------中国1？??2--session2SQLinsertintot1values('中国',2);1rowcreated.SQLcommit;Commitcomplete.SQLselect*fromt1;COLCOL2----------------涓 浗1中国2session1,我们看到session2输入的字符中国变成了乱码？??,session2,我们看到session1输入的字符中国变成了另外的字符涓 浗,下面我们来分析一下这中间数据库，客户端和操作系统都发生了那些事情。上面已经讨论了：session1输入的字符中国在数据库中存储的字符编码为”e4,b8,ad,e5,9b,bd.session2输入的字符中国在数据库中存储的字符编码为”d6,d0,b9,fa.当session1开始查询时，oracle从表中取出这两个字符，并按照字符集al32utf8和字符集zhs16gbk的编码映射表，将它的转换成zhs16gbk字符编码，对于编码“e4,b8,ad,e5,9b,bd”，它对应的zhs16gbk的字符编码为d6,d0,b9,fa,这个编码对应的字符为”中国“，所以我们看到了这个字符正常显示出来了，而对于字符集al32utf8字符编码“d6,d0,b9,fa”，由于我们用于显示字符的windows环境使用的是zhs16gbk字符集,而在zhs16gbk字符集里面并没有对应这个编码的字符或者属于无法显示的符号，于是使用了?这样的字符来替换，这就是为什么我们看到session2输入的字符变成了这样的乱码。当session2开始查询时，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和al3