oracle查询本身字符集,Oracle字符集问题总结

有过一些Oracle使用经验的朋友，大多会知道通过NLS_LANG来设置客户端的情况，NLS_LANG由以下部分组成：NLS_LANG=_.，其中第三部分的本意就是用来指明客户端操作系统缺省使用的字符集。所以按正规的用法，NLS_LANG应该按照客户端机器的实际情况进行配置，尤其对于字符集一项更是如此，这样Oracle就能够在最大程度上实现数据库字符集与客户端字符集的自动转换(当然是如果需要转换的话)。总结一下第一次迭代的重点：字符集：将特定的符号集编码为计算机能够处理的数值；字符集间的转换：对于在源字符集与目标字符集都存在的符号，理论上转换将不会产生信息丢失；而对于在源字符集中存在而在目标字符集中不存在的符号，理论上转换将会产生信息丢失；数据库字符集：选择能够包含所有将要存储的信息符号的字符集；客户端字符集设置：指明客户端操作系统缺省使用的字符集。第二次迭代：通过实例加深对基本概念的理解下面我将引用网友tellin在ITPUB上发表的“CHARACTER SET研究及疑问”帖子，该朋友在帖子中列举了他做的相关实验，并对实验结果提出了一些疑问，我将对他的实验结果进行分析，并回答他的疑问。实验结果分析一quote:--------------------------------------------------------------------------------最初由 tellin 发布设置客户端字符集为US7ASCIID:\>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII查看服务器字符集为US7ASCIISQL> SELECT * FROM NLS_DATABASE_PARAMETERS;PARAMETER VALUE------------------------------ ----------------------------------------NLS_CHARACTERSET US7ASCII建立测试表SQL> CREATE TABLE TEST (R1 VARCHAR2(10));Table created.插入数据SQL> INSERT INTO TEST VALUES('东北');1 row created.SQL> SELECT * FROM TEST;R1----------东北SQL> EXIT--------------------------------------------------------------------------------这一部分的实验数据的存取与显示都正确，好象没什么问题，但实际上却隐藏着很大的隐患。首先，要将汉字存入数据库，而将数据库字符集设置为US7ASCII是不合适的。US7ASCII字符集只定义了128个符号，并不支持汉字。另外，由于在SQL*PLUS中能够输入中文，操作系统缺省应该是支持中文的，但在NLS_LANG中的字符集设置为US7ASCII，显然也是不正确的，它没有反映客户端的实际情况。但实际显示却是正确的，这主要是因为Oracle检查数据库与客户端的字符集设置是同样的，那么数据在客户与数据库之间的存取过程中将不发生任何转换。具体地说，在客户端输入“东北”，“东”的汉字的编码为182(10110110)、171(10101011)，“北”汉字的编码为177(10110001)、177(10110001)，它们将不做任何变化的存入数据库中，但是这实际上导致了数据库标识的字符集与实际存入的内容是不相符的，从某种意义上讲，这也是一种不一致性，也是一种错误。而在SELECT的过程中，Oracle同样检查发现数据库与客户端的字符集设置是相同的，所以它也将存入的内容原封不动地传送到客户端，而客户端操作系统识别出这是汉字编码所以能够正确显示。在这个例子中，数据库与客户端的设置都有问题，但却好象起到了“负负得正”的效果，从应用的角度看倒好象没问题。但这里面却存在着极大的隐患，比如在应用length或substr等字符串函数时，就可能得到意外的结果。另外，如果遇到导入/导出(import /export)将会遇到更大的麻烦。有些朋友在这方面做了大量的测试，如eygle研究了“源数据库字符集为US7ASCII，导出文件字符集为US7ASCII或ZHS16GBK，目标数据库字符集为ZHS16GBK”的情况，他得出的结论是 “如果的是在Oracle92中，我们发现对于这种情况，不论怎样处理，这个导出文件都无法正确导入到Oracle9i数据库中”、“对于这种情况，我们可以通过使用Oracle8i的导出工具，设置导出字符集为US7ASCII，导出后修改第二、三字符，修改 0001 为0354,这样就可以将US7ASCII字符集的数据正确导入到ZHS16GBK的数据库中”。我想对于这些结论，这样理解可能更合适一些：由于ZHS16GBK字符集是US7ASCII的超级，所以如果按正常操作，这种转换应该没有问题；但出现问题的本质是我们让本应只存储英文字符的US7ASCII数据库，非常规地存储了中文信息，那么在转化过程中出现错误或麻烦就没什么奇怪的了，不出麻烦倒是有些奇怪了。所以说要避免这种情况，就是要在建立数据库时选择合适的字符集，不让标签(数据库的字符集设置)与实际(数据库中实际存储的信息)不符的情况发生。实验结果分析二quote:--------------------------------------------------------------------------------[ 更改客户端字符集为ZHS16GBKD:\>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBKD:\>SQLPLUS "/ AS SYSDBA"无法正常显示数据SQL> SELECT * FROM TEST;R1--------------------6+11疑问1：ZHS16GBK为US7ASCII的超集，为什么在ZHS16GBK环境下无法正常显示--------------------------------------------------------------------------------这主要是因为Oracle检查发现数据库设置的字符集与客户端配置字符集不同，它将对数据进行字符集的转换。数据库中实际存放的数据为182(10110110)、171(10101011)、177(10110001)、177(10110001)，由于数据库字符集设置为US7ASCII，它是一个7bit的字符集，存储在8bit的字节中，则Oracle忽略各字节的最高bit，则182(10110110)就变成了54(0110110)，在ZHS16GBK中代表数字符号“6”(当然在其它字符集中也是“6”)，同样过程也发生在其它3个字节，这样“东北”就变成了“6+11”。实验结果分析三quote:--------------------------------------------------------------------------------最初由 tellin 发布用ZHS16GBK插入数据SQL> INSERT INTO TEST VALUES('东北');1 row created.SQL> SELECT * FROM TEST;R1--------------------6+11??SQL> EXIT--------------------------------------------------------------------------------当客户端字符集设置为ZHS16GBK后向数据库插入“东北”，Oracle检查发现数据库设置的字符集为US7ASCII与客户端不一致，需要进行转换，但字符集ZHS16GBK中的“东北”两字在US7ASCII中没有对应的字符，所以Oracle用统一的“替换字符”插入数据库，在这里为“？”，编码为63(00111111)，这时，输入的信息实际上已经丢失，不管字符集设置如何改变(如下面引用的实验结果)，第二行SELECT出来的结果也都是两个“？”号(注意是2个，而不是4个)。quote:--------------------------------------------------------------------------------更改客户端字符集为US7ASCIID:\>SET NLS_LANG=AMERICAN_AMERICA.US7ASCIID:\>SQLPLUS "/ AS SYSDBA"无法显示用ZHS16GBK插入的字符集，但可以显示用US7ASCII插入的字符集SQL> SELECT * FROM TEST;R1----------东北??更改服务器字符集为ZHS16GBKSQL> update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';1 row updated.SQL> COMMIT;更改客户端字符集为ZHS16GBKD:\>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBKD:\>SQLPLUS "/ AS SYSDBA"可以显示以前US7ASCII的字符集，但无法显示用ZHS16GBK插入的数据，说明用ZHS16GBK插入的数据为乱码。SQL> SELECT * FROM TEST;R1--------------------东北??--------------------------------------------------------------------------------需要指出的是，通过“update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';”来修改数据库字符集是非常规作法，很可能引起问题，在这里只是原文引用网友的实验结果。