再论Oracle数据库字符集转换-技术开发专区

再论Oracle数据库字符集转换

作者：IT168 老熊编辑：晓熊 2009-11-03 10:34 来源：IT168�

　　【IT168 技术文档】字符集是一个老生常谈的问题了。这个问题的引起，绝大部分是因为“乱码”。而乱码是由于客户端与服务器的字符集的不同进行字符集转换而引起的。不过很多提到了转换，却没有提到这个转换是在哪个阶段和哪里发生的?是在服务器向块里写入数据的时候吗?在客户端还是在服务器端?

　　正确的答案是，普通字符串转换发生在客户端(具体来说是由OCI LIBRARY完成的)，国家字符串经过两次转换，第一次发生在客户端，第二次发生在服务器端。下面做个测试：

　　连接到:

　　Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

　　With the Partitioning, Real Application Clusters, OLAP and Data Mining options

　　SQL> select * from nls_database_parameters where parameter like ‘%CHARACTERSET%’;

　　PARAMETER VALUE

　　------------------------------ ------------------------------

　　NLS_CHARACTERSET ZHS16GBK

　　NLS_NCHAR_CHARACTERSET AL16UTF16

　　SQL> create table t1(a varchar2(100));

　　表已创建。

　　SQL>

　　SQL> insert into t1 values (’中’);

　　已创建 1 行。

　　SQL>

　　在本次连接中，我没有设置NLS_LANG变量。则客户端字符集为操作系统的缺省字符集ZHS16GBK。通过捕获网络包，可以发现客户端传送给客户端的数据(不能上传图片，郁闷)：

　　00000090 00 00 00 00 00 00 00 00 00 00 00 28 DB 00 01 1C ………..(….

　　000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v

　　000000B0 61 6C 75 65 73 20 28 27D6 D027 29 01 00 00 00 alues.(’..’)….

　　000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….

　　注意红色的部分，16进制D6 D0正是“中”字的GBK编码。(关于怎么获取汉字的各种编码，暂且略过，如有需要再交流)

　　现在我们退出SQLPLUS，设置环境变量NLS_LANG：

　　SQL> rollback;

　　回退已完成。

　　SQL> exit

　　从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

　　With the Partitioning, Real Application Clusters, OLAP and Data Mining options

　　断开

　　C:\Documents and Settings\Administrator>set nls_lang=american_america.us7ascii

　　C:\Documents and Settings\Administrator>sqlplus test/test@dmdb

　　SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 28 00:48:41 2008

　　Connected to:

　　Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

　　With the Partitioning, Real Application Clusters, OLAP and Data Mining options

　　SQL> insert into t1 values (’中’);

　　1 row created.

　　抓获的网络包发现，在SQL提交给服务器之前已经转换了。OCI库认为提交过来的编码是US7ASCII，因此要将转换为服务器端的ZHS16GBK编码，然而“中”的编码即16进制D6 D0并不是有效的US7ASCII编码，所以ORACLE OCI就转为了转省值3F3F(US7ASCII是单字节字符集，会认为“中”字是两个字符，因此为有两个3F) 这就是“??”号的由来。

　　00000090 00 00 00 00 00 00 00 00 00 00 00 C8 1D FF 00 1C …………….

　　000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v

　　000000B0 61 6C 75 65 73 20 28 273F 3F27 29 01 00 00 00 alues.(’??’)….

　　000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….

　　我们再看看将客户端NLS_LANG设置为simplified chinese_china.zhs16cgb231280会发生什么：

　　SQL> insert into t1 values (’中’);

　　已创建 1 行。

　　00000090 00 00 00 00 00 00 00 00 00 00 00 00 EC 01 01 1C …………….

　　000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v

　　000000B0 61 6C 75 65 73 20 28 27D6 D027 29 01 00 00 00 alues.(’..’)….

　　000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….

　　嗯，这里仍然是D6 D0，我们知道ZHS16GBK近似于ZHS16CGB231280超级。“中”对两种字符集来说，都是同一个编码。

　　看看我们使用生僻字会发生什么：

　　SQL> insert into t1 values (’喫’);

　　ERROR:

　　ORA-01756: 引号内的字符串没有正确结束

　　居然没有捕获到这个INSERT INTO语句提交到服务器的网络吧。由于在客户端要将“喫”字从ZHS16GB231280转换为ZHS16GBK，但这个字并不是一个有效的GB2312编码的字。但为什么出现了ORA-01756?转换过程认为“喫”字是GB2312编码，而操作系统传过来的编码是16进制86 CB，GB2312的编码，每个字节都是大于A1，因此认为第1个字节是一个8位的单字符，下一个字节大于A1，因此转换过程就将CB和下一个字节“’”合起来成为一个GB2312的双字节字符，因此就造成了这个错误信息。然而下面的语句是可以通过的：

　　SQL> insert into t1 values (’喫1′);

　　已创建 1 行。

　　抓获的网络包却发现是下面的结果：

　　00000090 00 00 00 00 00 00 00 00 00 00 00 10 EC 01 01 1D …………….

　　000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v

　　000000B0 61 6C 75 65 73 20 28 273F A3 BF27 29 01 00 00 alues.(’?..’)…

　　000000C0 00 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….

　　验证了上面的观点。第1字节被作为一个单字节字符转换，但是也不能转换为GBK字符，因此就转为了3F，但后面的两个字节仍然不是有效的GBK编码，就转为了A3 BF(全角的“?”)

　　上一篇讲到普通字符串的转换，本篇将讲到国家字符集字符串的转换：

　　客户端的NLS_LANG为默认值，即ZHS16GBK：

　　SQL> create table t1 ( id number ,aa varchar2(20),bb nvarchar2(20));

　　表已创建。

　　SQL> insert into t1 values (1,’中’,'中’);

　　已创建 1 行。

　　捕获的网络包如下：

　　00000090 00 00 00 00 00 00 EA 4E DB 00 AC 0D DC 00 00 00 …….N……..

　　000000A0 00 00 23 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 ..#insert.into.t

　　000000B0 31 20 76 61 6C 75 65 73 20 28 31 2C 27D6 D027 1.values.(1,’..’

　　000000C0 2C 27D6 D027 29 01 00 00 00 01 00 00 00 00 00 ,’..’)……….

　　SQL> select dump(aa) aa,dump(bb) bb from t1;

　　AA BB

　　------------------------------ ------------------------------

　　Typ=1 Len=2: 214,208 Typ=1 Len=2: 78,45

　　客户端发送给数据库的SQL语句，两个“中”字均为D6 D0，但服务器对NVARCHAR2类似的列作了转换，将其从ZHS16GBK编码转换为AL16UTF16，转换后的结果为10进制78，45,即16进制的4E 2D

　　因此对于国家字符集，客户端在提交SQL时实际并不区分是否国家字符集，统一将SQL中的字符转换为数据库字符集，服务器端再将国家字符集的列，从数据集字符集转换为国家字符集。因此，我们可以设想，如果数据库字符集与国家字符集不兼容，会发生什么?或者说是从数据库字符集转换为国家字符集是不是也会出现问题?我们用另一个数据库测试一下：

　　SQL> select * from nls_database_parameters where parameter like ‘%CHARACTERSET%’

　　;

　　PARAMETER VALUE

　　------------------------------ ------------------------------

　　NLS_CHARACTERSET US7ASCII

　　NLS_NCHAR_CHARACTERSET AL16UTF16

　　将客户端的NLS_LANG设置为AMERICAN_AMERICA.US7ASCII

　　SQL> create table t1 (id number,aa varchar2(20),bb nvarchar2(20));

　　SQL> insert into t1 values (1,’中’,'中’);

　　1 row created.

　　SQL> select dump(aa) aa,dump(bb) bb from t1;

　　AA BB

　　------------------------------ ------------------------------

　　Typ=1 Len=2: 214,208 Typ=1 Len=4: 0,86,0,80

　　注意看这里dump出的结果，与前一个库dump出的结果，aa列是一样的，而bb列dump出来变成了10进制的0,86,0,80。我们看看这个值是怎么来的：

　　1.客户端NLS_LANG与数据库字符集相同，因此在客户端并没对SQL中的字符进行转换。

　　2.服务器在执行SQL时，将bb列的值从数据库字符集编码(10进制214，208)转换为AL16UTF16编码(这种编码每个字符为固定的两字节)。由于数据库字符集为单字节字符集，在转换时认为是两个字符，同时US7ASCII字符的高位应该为0，而214-128=86，208-128=80.因此转换后其结果就为字符串“VP"了：

　　SQL> select * from t1;

　　ID AA BB

　　---------- -------------------- --------------------

　　1 中 VP

　　因此，如果选择了错误的数据库字符集，虽然可以通过设置NLS_LANG将客户端字符集设置为与服务器字符集一致，但国家字符集却有可能不能正常地从数据库字符集转换为国家字符集。

　　前文主要讲到的是执行DML的字符集转换，下面再讨论检索数据时的字符集转换，还是先看测试：

　　先将NLS_LANG设置为默认值ZHS16GBK

　　SQL> insert into t1 values (1,’中’,'中’);

　　已创建 1 行。

　　SQL> commit;

　　提交完成。

　　SQL> select * from t1;

　　ID AA BB

　　---------- -------------------- ----------------------------------------

　　1 中中

　　从抓取的网络包中找到返回的数据：

　　00000030 01 3D 00 00 06 00 00 00 00 00 .=……..

　　00000040 10 17 3A 08 C0 CA 9B 07 F7 10 15 1A EA 23 F7 68 ..:……….#.h

　　00000050 DD 85 78 6C 01 1C 0D 22 36 52 00 00 00 03 00 00 ..xl…"6R……

　　00000060 00 39 02 00 00 81 16 00 00 00 00 00 00 00 00 00 .9…………..

　　00000070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 …………….

　　00000080 02 02 00 00 00 02 49 44 00 00 00 00 00 00 00 00 ……ID……..

　　00000090 01 80 00 00 14 00 00 00 00 00 00 00 00 00 00 00 …………….

　　000000A0 00 00 00 00 00 0054 0301 14 00 00 00 01 02 02 ……T………

　　000000B0 00 00 00 02 41 41 00 00 00 00 00 00 00 00 01 80 ….AA……….

　　000000C0 00 00 28 00 00 00 00 00 00 00 00 10 00 00 00 00 ..(………….

　　000000D0 00 00 00 00D0 0702 14 00 00 00 01 02 02 00 00 …………….

　　000000E0 00 02 42 42 00 00 00 00 00 00 00 00 07 00 00 00 ..BB…………

　　000000F0 07 78 6C 01 1C 0D 22 36 06 02 03 00 00 00 01 00 .xl…"6……..

　　00000100 00 00 00 00 00 00 00 00 00 00 07 02 C1 02 02D6…………….

　　00000110 D0024E 2D08 06 00 F2 DF 02 00 00 00 00 00 02 ..N-…………

　　00000120 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….

　　00000130 00 00 00 04 01 00 00 00 01 00 00 00 00 00 00 00 …………….

　　00000140 00 00 02 00 0E 00 03 00 00 00 00 00 07 28 00 00 ………….(..

　　00000150 04 00 00 16 00 00 00 01 00 00 00 00 00 00 2C 00 …………..,.

　　00000160 00 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….

　　00000170 00 00 00 …

　　上面展示的是返回的数据。红色分别为AA列和BB列的字符集ID：

　　SQL> select nls_charset_name(to_number(’0354′,’xxxx’)) from dual;

　　NLS_CHARSET_NAME(TO_NUMBER(’0354′,’XXXX’

　　----------------------------------------

　　ZHS16GBK

　　SQL> select nls_charset_name(to_number(’07D0′,’xxxx’)) from dual;

　　NLS_CHARSET_NAME(TO_NUMBER(’07D0′,’XXXX’

　　----------------------------------------

　　AL16UTF16

　　蓝色部分是列数据，D6 D0为ZHS16GBK编码的“中”，而4E 2D为AL16UTF16编码的“中”字，数据原样从数据库中返回。这两个不同的编码，最后显示的结果均为“中”字。由于数据库字符集ZHS16GBK与客户端相同，客户端没有对数据作转换，而国家字符集的“中”字，要转换为ZHS16GBK，再最终由客户端程序(SQLPLUS)显示出来。

　　下面把NLS_LANG设置为AMERICAN_AMERICA.US7ASCII，再进行同样的测试，发现，返回的网络包是一样，即服务器端返回的数据是一样的，并没有因为NLS_LANG的不同而不同，因此转换仍然是发生在客户端。在这次测试中，将服务器返回的数据，转换成US7ASCII编码，出现了乱码，显示为?号

　　再将NLS_LANG设置为AMERICAN_AMERICA.UTF8，看看返回的结果

　　SQL> select * from t1;

　　ID AA BB

　　---------- -------------------- --------------------

　　1 涓? 涓

　　这次是出现了将“中”字转换成了其他汉字。为什么是转成了这个“涓”字，在此不在细述。

　　下面把NLS_LANG设置为AMERICAN_AMERICAN.UTF8，但增加了一个环境变量NLS_NCHAR=ZHS16GBK

　　SQL> select * from t1;

　　ID AA BB

　　---------- -------------------- --------------------

　　1 涓? 中

　　在本次测试中，字符集为国家字符集AL16UTF16的列BB显示了正确的结果。这说明客户端OCI库在转换时，对国家字符集是根据NLS_NCHAR进行转换的，在这个测试中NLS_NCHAR为ZHS16GBK，将AL16UTF16编码正确地转换到了ZHS16GBK编码。

　　再作一个测试，将NLS_LANG设置为AMERICAN_AMERICA.ZHS16GBK，将NLS_NCHAR设置为AL16UTF16

　　SQL> select * from t1;

　　ID AA BB

　　---------- -------------------- -----------

　　1 中 N-

　　由于NLS_NCHAR与国家字符集相同，因此对国家字集符的列没有作转换，直接返回。“中”字的AL16UTF16的编码为 4E 2D，在客户端操作系统中，正好是英文字符“N”和“-”的编码

　　结论：

　　在客户端向服务器端提交SQL语句时，客户端根据NLS_LANG和服务器数据库字符集，对SQL中的字符进行转换处理。如果NLS_LANG设置的字符集与服务器数据库字符集相同，不作转换，否则要转换成服务器端字符符。如果有国家字符集，客户端不作处理，由服务器端再将其转换为国家字符集。

　　在查询数据时，服务器端原服务器端的编码返回数据，由客户端根据返回的元数据中的字符集与NLS_LANG和NLS_NCHAR的设置进行比较。如果NLS_NCHAR没有设置，则其默认值为NLS_LANG中的字符集设置。如果数据中的字符集与客户端设置一致，不进行转换，否则要进行转换。国家字符集的转换根据NLS_NCHAR设置进行转换。

　　此前关于字符集转换的文章，已经有三篇。写这新的一篇来源于最近有几次朋友问到的关于导入导出(exp/imp)的问题。这个问题是这样的：

　　使用imp导入数据后，发现数据是正确的，没有乱码，但是表和列上的注释(comments)、中文列名、Procedure/Package里面的中文全部变成了乱码。

　　网上很少有文章讨论到这一点，其实exp/imp与通常执行SQL引起的字符集转换有一些不同。这得从dmp文件的格式说起。

　　先看看下面的测试：

　　view plaincopy to clipboardprint?

　　SQL> create table t1 ( a number,b varchar2(100));

　　SQL> insert into t1 values (123456,'aaaaaa');

　　SQL> insert into t1 values (67890,'中中中中');

　　SQL> commit;

　　SQL> comment on table t1 is '测试表';

　　SQL> create table t1 ( a number,b varchar2(100));

　　SQL> insert into t1 values (123456,'aaaaaa');

　　SQL> insert into t1 values (67890,'中中中中');

　　SQL> commit;

　　SQL> comment on table t1 is '测试表';

　　现在将NLS_LANG设置为AMERICAN_AMERICA.ZHS16GBK，导出T1表，然后看看导出的dmp文件中的数据：

　　000008f0h: 22 54 31 22 0A 43 52 45 41 54 45 20 54 41 42 4C ; “T1″.CREATE TABL

　　00000900h: 45 20 22 54 31 22 20 28 22 41 22 20 4E 55 4D 42 ; E “T1″ (”A” NUMB

　　00000910h: 45 52 2C 20 22 42 22 20 56 41 52 43 48 41 52 32 ; ER, “B” VARCHAR2

　　00000920h: 28 31 30 30 29 29 20 20 50 43 54 46 52 45 45 20 ; (100)) PCTFREE

　　00000930h: 31 30 20 50 43 54 55 53 45 44 20 34 30 20 49 4E ; 10 PCTUSED 40 IN

　　00000940h: 49 54 52 41 4E 53 20 31 20 4D 41 58 54 52 41 4E ; ITRANS 1 MAXTRAN

　　00000950h: 53 20 32 35 35 20 53 54 4F 52 41 47 45 28 49 4E ; S 255 STORAGE(IN

　　00000960h: 49 54 49 41 4C 20 31 30 34 38 35 37 36 20 46 52 ; ITIAL 1048576 FR

　　00000970h: 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C 49 ; EELISTS 1 FREELI

　　00000980h: 53 54 20 47 52 4F 55 50 53 20 31 29 20 54 41 42 ; ST GROUPS 1) TAB

　　00000990h: 4C 45 53 50 41 43 45 20 22 54 45 53 54 5F 38 4B ; LESPACE “TEST_8K

　　000009a0h: 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 4F 4D 50 ; ” LOGGING NOCOMP

　　000009b0h: 52 45 53 53 0A 49 4E 53 45 52 54 20 49 4E 54 4F ; RESS.INSERT INTO

　　000009c0h: 20 22 54 31 22 20 28 22 41 22 2C 20 22 42 22 29 ; “T1″ (”A”, “B”)

　　000009d0h: 20 56 41 4C 55 45 53 20 28 3A 31 2C 20 3A 32 29 ; VALUES (:1, :2)

　　000009e0h: 0A 02 00 02 00 16 00 01 00 64 00 54 03 01 00 00 ; ………d.T….

　　000009f0h: 00 00 00 04 00 C3 0D 23 39 06 00 61 61 61 61 61 ; …..?#9..aaaaa

　　00000a00h: 61 00 00 04 00 C3 07 4F 5B 08 00 D6 D0 D6 D0 D6 ; a….?O[..中中?

　　00000a10h: D0 D6 D0 00 00 FF FF 0A 43 4F 4D 4D 45 4E 54 20 ; 兄?..COMMENT

　　00000a20h: 4F 4E 20 54 41 42 4C 45 20 22 54 31 22 20 49 53 ; ON TABLE “T1″ IS

　　00000a30h: 20 0A 08 00 27 B2 E2 CA D4 B1 ED 27 0A 45 58 49 ; …’测试表’.EXI

　　00000a40h: 54 0A 45 58 49 54 0A 00 00 00 00 00 00 00 00 00 ; T.EXIT……….

　　从上面的数据可以看到，有两部分数据，一部分是代码性质的数据，包括CREATE TABLE、COMMIT、INSERT等SQL语句;另一部分就是表T1的实际数据了,红色部分就是表的实际数据。

　　我们先看看表中的实际数据：

　　view plaincopy to clipboardprint?

　　SQL> select a,dump(a,16) da,dump(b,16) db from t1;

　　A DA DB

　　---------- ------------------------------ --------------------------------------

　　123456 Typ=2 Len=4: c3,d,23,39 Typ=1 Len=6: 61,61,61,61,61,61

　　67890 Typ=2 Len=4: c3,7,4f,5b Typ=1 Len=8: d6,d0,d6,d0,d6,d0,d6,d0

　　SQL> select a,dump(a,16) da,dump(b,16) db from t1;

　　A DA DB

　　---------- ------------------------------ --------------------------------------

　　123456 Typ=2 Len=4: c3,d,23,39 Typ=1 Len=6: 61,61,61,61,61,61

　　67890 Typ=2 Len=4: c3,7,4f,5b Typ=1 Len=8: d6,d0,d6,d0,d6,d0,d6,d0

　　对比一下就可以发现，dmp文件中T1表的数据，与数据库中原始数据是一模一样的，没有发生任何变化，也就是说，dmp文件中存储的表数据实际上与数据在数据库中存储的相同的二进制形式。

　　现在将NLS_LANG设置为AMERICAN_AMERICA.US7ASCII，导出T1表，然后看看导出的dmp文件中的数据：