此前关于字符集转换的文章,已经有三篇。写这新的一篇来源于最近有几次朋友问到的关于导入导出(exp/imp)的问题。这个问题是这样的:
使用imp导入数据后,发现数据是正确的,没有乱码,但是表和列上的注释(comments)、中文列名、Procedure/Package里面的中文全部变成了乱码。

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

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的实际数据了,红色部分就是表的实际数据。

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

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文件中的数据:

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 05 00 27 3F 3F 3F 27 0A 45 58 49 54 0A 45 ; ...'???'.EXIT.E
00000a40h: 58 49 54 0A 00 00 00 00 00 00 00 00 00 00 00 00 ; XIT.............

这一次我们可以看到,红色部分的数据,也就是表的实际数据没有发生任何变化。
有变化的地方在哪里?稍微对比就可以发现,代码部分发生了变化。第一次导出时的COMMENT语句,明显可以看到“测试表”三个汉字,而第二次导出时,这三个汉字变成了三个问号。显然就是导出时GBK转换为US7ASCII码时,发生了乱码。

测试到这里,结论已经很明了了。exp导出时,表中的数据没有发生任何变化,以存储在数据库时的二进制一致的形式存储在了dmp文件中。然而,代码部分则于是纯文本形式的数据,在导出时要遵循字符集转换原则,发生转换。既然转换部分是代码形式的数据,那么下列代码中的数据都会发生转换:建表(CREATE TABLE),注释(COMMENT),创建视图(CREATE VIEW),其他程序代码(FUNCTION/PACKAGE/TRIGGER/PROCEDURE)如此等等。因此如果exp/imp时字符集不兼容,那么中文列名,注释、视图、程序代码中的中文都将会是乱码,而表中的实际数据则不会发生变化。这也是本文开头提到的问题的来源。

Trackback

9 comments untill now

  1. 谢谢老熊。。

  2. 老熊讲的很好啊!
    期待《DBA手记 – 诊断案例与性能调整》——老熊的DBA手记 !

    老熊 Reply:

    @TQ, 谢谢支持

  3. luolongjiu @ 2009-09-12 10:50

    laoxiong在四川啊!
    我还是一名学生,很高兴能够认识你!
    还是在pub上找到你的链接!
    字符集的这四张讲的很好!
    想请教一下这样从网络捕捉客户端传到服务器的数据信息!
    谢谢
    best wishes!!

    老熊 Reply:

    @luolongjiu, 捕获网络包,可以用一些专用的软件,比如像wireshark,tcpdump等。在文章中我用到的是windows自带的“网络监视器”

  4. luolongjiu @ 2009-09-12 10:50

    我也是四川的哈!

    老熊 Reply:

    @luolongjiu, 很高兴认识你。

  5. 小弟也是這種情形,imp完,數據中文沒有亂碼,但PROCEDURE、functions等含有中文部份都變成亂碼,小弟database server 是linux system ,LANG=”zh_TW.UTF-8″ ,試了很多方法,也把字符集改成相同狀態,一直重覆作 imp 動作好幾次還是不行,突然今天下午看了網主文章,靈感一動把imp 動作變到 client 端去作(winxp 中文環境),database 竟然可以看到中文,不是亂碼,真是太神奇了。

    老熊 Reply:

    @jacky,
    Oracle在处理字符集转换时,使用的NLS_LANG这个环境变量,而不是LANG环境变量。或许是你的环境变量名搞错了?

Add your comment now