相信大家都对字符集有相当的了解了，废话就不多说了！直接步入正题：这里主要是测试含有 汉字的数据从ZHS16GBK的数据库导入到字符集为AL32UTF8 数据库. 如有我没想到的其他情况，请大家提建议，我继续完善测试实验。 测试环境： 目标库：英杰的rac测试库 由 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options sys@rac1>select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------- AMERICAN_AMERICA.AL32UTF8 yang@rac1>select * from nls_database_parameters; PARAMETER VALUE ------------------------- ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET AL32UTF8 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.2.0.1.0 20 rows selected. 源库： Release 11.1.0.6.0 - Production on Friday, 11 March, 2011 21:11:49 Conneted to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 源库的nls_ 信息： sys@ORACL> select * from nls_database_parameters; PARAMETER VALUE ------------------------- -------------------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.1.0.6.0 测试步骤：这里主要是测试含有 汉字的数据从ZHS16GBK 导入到 AL32UTF8 . 在源库创建含有汉字的表: yang@ORACL> create table chart ( val varchar2(20)); 表已创建。 ======创建英文字符数据 yang@ORACL> insert into chart 2 select dbms_random.string('l',15) 3 from dual 4 connect by level <50; 已创建49行。 ======创建中文汉字数据 yang@ORACL> insert into chart 2 select '阿里云' as val from 3 dual connect by level <50; 已创建49行。 yang@ORACL> select * from chart; VAL -------------------- 阿里云 已选择98行。 yang@ORACL> commit; 提交完成。 yang@ORACL> select dump('阿里云') from dual; DUMP('阿里云') ------------------------------------- Typ=96 Len=6: 176,162,192,239,212,198 ===三个 汉字 占用6个字节 ===================导出操作=================================== Microsoft Windows [版本 6.1.7600] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\aaaa>set nls_lang=american_america.al32utf8 这里后面测试不加也可以成功导入al32utf8 C:\Users\aaaa>expdp yang/yang tables=chart directory=dumpdir dumpfile=zhs16gbk.dmp Export: Release 11.1.0.6.0 - Production on Friday, 11 March, 2011 21:19:04 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "YANG"."SYS_EXPORT_TABLE_01": yang/******** tables=chart directory=dumpdir dumpfile=zhs16g bk.dmp Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "YANG"."CHART" 6.484 KB 98 rows Master table "YANG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for YANG.SYS_EXPORT_TABLE_01 is: D:\DUMP\ZHS16GBK.DMP Job "YANG"."SYS_EXPORT_TABLE_01" successfully completed at 21:19:20 ==============================导入到目标库========================================= oracle@rac1:rac1 /tmp/dump>export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 ==.bash_profile 文件里已经声明了，这里是强调一下。 oracle@rac1:rac1 /tmp/dump>impdp yang/yang tables=chart directory=dumpdir dumpfile=zhs16gbk.dmp log=zhs16gbk_to_al32utf8.log Import: Release 11.2.0.1.0 - Production on Fri Mar 11 21:23:13 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "log=zhs16gbk_to_al32utf8.log" Location: Command Line, Replaced with: "logfile=zhs16gbk_to_al32utf8.log" Master table "YANG"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "YANG"."SYS_IMPORT_TABLE_01": yang/******** tables=chart directory=dumpdir dumpfile=zhs16gbk.dmp logfile=zhs16gbk_to_al32utf8.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "YANG"."CHART" 6.484 KB 98 rows Job "YANG"."SYS_IMPORT_TABLE_01" successfully completed at 21:23:17 ==============================在目标库中验证======================================= sys@rac1>select dump('阿里云') from dual; DUMP('阿里云') ------------------------------------------------- Typ=96 Len=9: 233,152,191,233,135,140,228,186,145 阿里云 三个汉字 占用 9个字节。 yang@rac1>select table_name from user_tables; TABLE_NAME ------------------------------ CHARSET CHART yang@rac1>select val from chart; VAL ---------------- ogqodcrhyqaesks 98 rows selected. 小结：字符集子集向其超集转换是可行的，如此例 ZHS16GBK转换为AL32UTF8。 导出使用的字符集将会记录在导出文件中，当文件导入时，将会检查导出时使用的字符集设置，如果这个字符集不同于导入客户端的NLS_LANG 设置，字符集将根据导入客户端NLS_LANG设置进行转换，如果必要，在数据插入数据库之前会进行进一步转换。 通常在导出时最好把客户端字符集设置得和数据库端相同，这样可以避免在导出时发生不必要的数据转换，导出文件将和数据库具有相同的字符集。 即使将来会把导出文件导入到不同字符集的数据库中，这样做也可以把转换延缓至导入时刻。 当进行数据导入时，主要存在以下两种情况: 1.源数据库和目标数据库具有相同字符集设置 这时，只需要设置NLS_LANG等于数据库字符集即可导入(前提是，导出使用的是和源数据库相同字符集，即三者相同) 2.源数据库和目标数据库字符集不同 如果我们导出时候使用的NLS_LANG是和源数据库相同的字符集，那么导入时就可以设置客户端NLS_LANG等于导出时使用的字符集，这 样转换只发生在数据库端，而且只发生一次。 例如: 如果进行从ZHS16GBK到UTF8的转换 1)使用NLS_LANG=AMERICAN_AMERICA.ZHS16GBK导出数据库。 这时创建的导出文件包含ZHS16GBK的数据 2)导入时使用NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 这时转换仅发生在insert数据到UTF8的数据库中。 以上假设的转换只在目标数据库字符集是源数据库字符集的超集时才能转换。 参考文章： http://www.itpub.net/thread-538197-1-1.html http://www.itpub.net/viewthread.php?tid=276524&extra=page%3D1&page=1 http://www.eygle.com/archives/2004/09/nls_character_set_04.html
此文章是对于上一个实验的补充，上一次实验仅仅考虑的 varchar2 的情况。这次考虑到对于char类型的含有中文数据的情况。 对于英文： 对英文，在al32utf8中仍然和zhs16gbk一样用1个字节表示，因此导入固定长度英文字符数据时不会出错。 对于中文： 例如在字符集为zhs16gbk 数据库中创建表时指定字段 val char（15），该字段含有数据 ‘阿里云计算公司’在字符集为zhs16gbk 数据库中占用14个字 节，而在字符集为al32utf8 数据库中占用21个字节 大于 char（15）所指定的长度15.此时导入数据就会失败。 下面对于上述情况给以说明。 =======================迁移 英文固定长度字符数据==================== =======================创建表，含有英文固定长度字符数据============= yang@ORACL> create table fixed_char ( val char(15) ); 表已创建。 yang@ORACL> insert into fixed_char select 2 dbms_random.string('l',15) from dual 3 connect by level <125; 已创建124行。 yang@ORACL> select val from fixed_char; VAL --------------- oxmjgwgzbjhthcr elbdlcpkfajsmrc jrwccslaywxpiwj ............... ljkowkocmdqnkgj idialvaxohrahah zwrqynvtevfujao funkxaokotsblww snyetpafaneicjm kgrcrpbwlvtotcv knhcazjkgotzvmg myqgvjqnsingmxv klthqehltsyzrxe voucbpykpnsbopx vtvavjddyafwqxt omcnkpvlhlxdvvg ccfpttivbdvursz 已选择124行。 yang@ORACL> spool d:\fixed_char.txt yang@ORACL> insert into fixed_char select 2 dbms_random.string('l',15) from dual 3 connect by level <125; 已创建124行。 yang@ORACL> select val from fixed_char where rownum <10; VAL --------------- oxmjgwgzbjhthcr elbdlcpkfajsmrc jrwccslaywxpiwj clizpikggppgfwy yqqxljlscqaiqli lrwoayaxyjzgdhy rkqpujyupltmrqb qvycepfgtwipwat iccsgvrpfxwligq 已选择9行。 yang@ORACL> commit; 提交完成。 =========================导出数据================================ C:\Users\aaaa>expdp yang/yang tables=fixed_char directory=dumpdir dumpfile=fixed_char_zhs16gbk.dmp l ogfile=fixed_char.log Export: Release 11.1.0.6.0 - Production on 星期六, 12 3月, 2011 12:25:55 Copyright (c) 2003, 2007, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 启动 "YANG"."SYS_EXPORT_TABLE_01": yang/******** tables=fixed_char directory=dumpdir dumpfile=fixed _char_zhs16gbk.dmp logfile=fixed_char.log 正在使用 BLOCKS 方法进行估计... 处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA 使用 BLOCKS 方法的总估计: 64 KB 处理对象类型 TABLE_EXPORT/TABLE/TABLE . . 导出了 "YANG"."FIXED_CHAR" 9.851 KB 248 行 已成功加载/卸载了主表 "YANG"."SYS_EXPORT_TABLE_01" ****************************************************************************** YANG.SYS_EXPORT_TABLE_01 的转储文件集为: D:\DUMP\FIXED_CHAR_ZHS16GBK.DMP 作业 "YANG"."SYS_EXPORT_TABLE_01" 已于 12:27:32 成功完成 =====================================导入数据============================================= oracle@rac1:rac1 /tmp/dump>impdp yang/yang tables=fixed_char directory=dumpdir dumpfile=fixed_char_zhs16gbk.dmp log=fixedchar_zhs16gbk_to_al32utf8.log Import: Release 11.2.0.1.0 - Production on Sat Mar 12 12:31:34 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "log=fixedchar_zhs16gbk_to_al32utf8.log" Location: Command Line, Replaced with: "logfile=fixedchar_zhs16gbk_to_al32utf8.log" Master table "YANG"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "YANG"."SYS_IMPORT_TABLE_01": yang/******** tables=fixed_char directory=dumpdir dumpfile=fixed_char_zhs16gbk.dmp logfile=fixedchar_zhs16gbk_to_al32utf8.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "YANG"."FIXED_CHAR" 9.851 KB 248 rows Job "YANG"."SYS_IMPORT_TABLE_01" successfully completed at 12:31:38 ==================================在目标库里验证数据==================================== yang@rac1>select table_name from user_tables; TABLE_NAME ------------------------------ CHARSET CHART TCHAR FIXED_CHAR yang@rac1>desc fixed_char Name Null? Type ----------------------- -------- ------- VAL CHAR(15) yang@rac1>select val from fixed_char where rownum <12; VAL --------------- oxmjgwgzbjhthcr elbdlcpkfajsmrc jrwccslaywxpiwj clizpikggppgfwy yqqxljlscqaiqli lrwoayaxyjzgdhy rkqpujyupltmrqb qvycepfgtwipwat iccsgvrpfxwligq zrtwmeviosnsgmv chwsezhjfgnsjll 11 rows selected. yang@rac1>select dump(val) from fixed_char where rownum <10; DUMP(VAL) -------------------------------------------------------------------------- Typ=96 Len=15: 111,120,109,106,103,119,103,122,98,106,104,116,104,99,114 Typ=96 Len=15: 101,108,98,100,108,99,112,107,102,97,106,115,109,114,99 Typ=96 Len=15: 106,114,119,99,99,115,108,97,121,119,120,112,105,119,106 Typ=96 Len=15: 99,108,105,122,112,105,107,103,103,112,112,103,102,119,121 Typ=96 Len=15: 121,113,113,120,108,106,108,115,99,113,97,105,113,108,105 Typ=96 Len=15: 108,114,119,111,97,121,97,120,121,106,122,103,100,104,121 Typ=96 Len=15: 114,107,113,112,117,106,121,117,112,108,116,109,114,113,98 Typ=96 Len=15: 113,118,121,99,101,112,102,103,116,119,105,112,119,97,116 Typ=96 Len=15: 105,99,99,115,103,118,114,112,102,120,119,108,105,103,113 9 rows selected. 对于英文字符可以实现由zhs16gbk 到 al32utf8的转换。 解释：用UTF-8，UNICODE的2字节字符用变长个（1－3个字节）表示： 1. 对英文，仍然和ASCII一样用1个字节表示，这个字节的值小于128(\x80)； 2. 扩展的ASCII字符（主要是西欧），第一字节用C2 - DF之间的范围，双字节表示。 3.对其他语言，比如亚洲语系，还有各种特殊符号，使用3个字节表示； 因此，在应用中程序处理过程中所有字符都是16位（双字节），但在存取转换成字节流时使用UTF-8格式转换，对于英文字符来说和原来用ASCII方式存取 时相比大小仍然是一样的，而对中文来说和原来的GB2312编码方式相比，大小为：(3字节/2字节)=1.5倍，这也是下面导入数据失败的原因。 ==================================迁移含有汉字 固定字符数据============================= =====================创建表，含有汉字的固定字符数据====================================== yang@ORACL> create table fixed( val1 char(15),val2 char(15) ); 表已创建。 yang@ORACL> insert into fixed select 2 dbms_random.string('l',15) val1, 3 '阿里云算公司' as val2 4 from dual 5 connect by level <500; 已创建499行。 yang@ORACL> select dump('阿里云计算公司') from dual; DUMP('阿里云计算公司') ---------------------------------------------------------------------- Typ=96 Len=14: 176,162,192,239,212,198,188,198,203,227,185,171,203,190 yang@ORACL> commit; 提交完成。 yang@ORACL> select count(*) from fixed; COUNT(*) ---------- 499 yang@ORACL> ========================导出操作=========================================== C:\Users\aaaa>expdp yang/yang tables=fixed directory=dumpdir dumpfile=fixed_zhs16gbk.dmp logfile=fixed.log Export: Release 11.1.0.6.0 - Production on 星期六, 12 3月, 2011 12:50:05 Copyright (c) 2003, 2007, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 启动 "YANG"."SYS_EXPORT_TABLE_01": yang/******** tables=fixed directory=dumpdir dumpfile=fixed_zhs1 6gbk.dmp logfile=fixed.log 正在使用 BLOCKS 方法进行估计... 处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA 使用 BLOCKS 方法的总估计: 64 KB 处理对象类型 TABLE_EXPORT/TABLE/TABLE . . 导出了 "YANG"."FIXED" 22.94 KB 499 行 已成功加载/卸载了主表 "YANG"."SYS_EXPORT_TABLE_01" ****************************************************************************** YANG.SYS_EXPORT_TABLE_01 的转储文件集为: D:\DUMP\FIXED_ZHS16GBK.DMP 作业 "YANG"."SYS_EXPORT_TABLE_01" 已于 12:50:53 成功完成 C:\Users\aaaa> ========================导入操作===================================================================== oracle@rac1:rac1 /tmp/dump>impdp yang/yang tables=fixed directory=dumpdir dumpfile=fixed_zhs16gbk.dmp log=fixed_zhs16gbk_to_al32utf8.log Import: Release 11.2.0.1.0 - Production on Sat Mar 12 12:52:38 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "log=fixed_zhs16gbk_to_al32utf8.log" Location: Command Line, Replaced with: "logfile=fixed_zhs16gbk_to_al32utf8.log" Master table "YANG"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "YANG"."SYS_IMPORT_TABLE_01": yang/******** tables=fixed directory=dumpdir dumpfile=fixed_zhs16gbk.dmp logfile=fixed_zhs16gbk_to_al32utf8.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA ORA-02374: conversion error loading table "YANG"."FIXED" ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15)===> 需要21个字节但是 char(15）规定val2字段长度是15 ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020' ORA-02374: conversion error loading table "YANG"."FIXED" ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15) ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020' ORA-02374: conversion error loading table "YANG"."FIXED" ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15) ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020' ORA-02374: conversion error loading table "YANG"."FIXED" ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15) ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020' ORA-02374: conversion error loading table "YANG"."FIXED" ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15)
五、试验总结 1.当源端字符编码为ZHS16GBK，目标端编码为AL32UTF8，客户端随便为其中的一种编码，迁移数据不会出现乱码，但是会出现列长度不够现象。反过来不行，因为utf8中的部分字符转换到gbk中肯定会不支持 2.设置了源端客户端编码，仅仅是导出来的dmp文件头部有编码字符标示不一样，存储数据还是按照服务端存储 3.打破神话，exp/imp导入要不乱码，导出和导入的客户端编码要一致
五、原因分析,解决建议 在导入过程中，最多会发生三次编码转换: 1、执行exp时，数据库中数据的编码会转换为导出客户端编码 2、执行imp时，dmp文件的编码转换为导入客户端编码 3、导入客户端编码转换为目标端数据库的数据库编码 在exp/imp操作的过程中，经常出现乱码的原因就是编码的相互转换的过程中出现了丢失或者相互不能转换导致。要解决这个问题，最好的办法就是通过NLS_LANG的灵活设置，减少编码转换的次数(如果相邻的转换操作编码一致，那么不会发生编码转换，如试验中的ZHS16GBK编码测试，就没有转换发生)，或者使得相互的转换能够兼容，可以最大程度的减少乱码的出现。 如果已经有了exp导出的dmp文件，然后在导入的过程中，出现乱码，一般的处理建议是nls_lang的编码设置和dmp文件的一致，让转换发生在导入客户端和数据库服务器间(要求:编码可以相互转换)