ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle数据库字符集问题解析(转)

Oracle数据库字符集问题解析(转)

原创 Linux操作系统 作者:jcszjswkzhou 时间:2019-06-24 10:12:05 0 删除 编辑
经常看到一些朋友问ORACLE字符集方面的问题我想以迭代的方式来介绍一下

第一次迭代:掌握字符集方面的基本概念
有些朋友可能会认为这是多此一举但实际上正是由于对相关基本概念把握不清才导致了诸多问题和疑问
首先是字符集的概念
我们知道电子计算机最初是用来进行科学计算的(所以叫做“计算机”)但随着技术的发展还需要计算机进行其它方面的应用处理这就要求计算机不仅能处理数值还能处理诸如文字、特殊符号等其它信息而计算机本身能直接处理的只有数值信息所以就要求对这些文字、符号信息进行数值编码最初的字符集是我们都非常熟悉的ASCII它是用7个二进制位来表示128个字符而后来随着不同国家、组织的需要出现了许许多多的字符集如表示西欧字符的ISO8859系列的字符集表示汉字的GB2312-80、GBK等字符集
字符集的实质就是对一组特定的符号分别赋予不同的数值编码以便于计算机的处理
字符集之间的转换字符集多了就会带来一个问题比如一个字符在某一字符集中被编码为一个数值而在另一个字符集中被编码为另一个数值比如我来创造两个字符集demo_charset1与demo_charset2在demo_charset1中我规定了三个符号的编码为:A(0001)B(0010)?(1111);而在demo_charset2中我也规定了三个符号的编码为:A(1001)C(1011)?(1111)这时我接到一个任务要编写一个程序负责在demo_charset1与demo_charset2之间进行转换由于知道两个字符集的编码规则对于demo_charset1中的0001在转换为demo_charset2时要将其编码改为1001;对于demo_charset1中的1111转换为demo_charset2时其数值不变;而对于demo_charset1中的0010其对应的字符为B但在demo_charset2没有对应的字符所以从理论上无法转换对于所有这类无法转换的情况我们可以将它们统一转换为目标字符集中的一个特殊字符(称为“替换字符”)比如在这里我们可以将?作为替换字符所以B就转换为了?出现了信息的丢失;同样道理将demo_charset2的C字符转换到demo_charset1时也会出现信息丢失
所以说在字符集转换过程中如果源字符集中的某个字符在目标字符集中没有定义将会出现信息丢失
数据库字符集的选择
我们在创建数据库时需要考虑的一个问题就是选择什么字符集与国家字符集(通过create database中的CHARACTER SET与NATIONAL CHARACTER SET子句指定)考虑这个问题我们必须要清楚数据库中都需要存储什么数据如果只需要存储英文信息那么选择US7ASCII作为字符集就可以;但是如果要存储中文那么我们就需要选择能够支持中文的字符集(如ZHS16GBK);如果需要存储多国语言文字那就要选择UTF8了
数据库字符集的确定实际上说明这个数据库所能处理的字符的集合及其编码方式由于字符集选定后再进行更改会有诸多的限制所以在数据库创建时一定要考虑清楚后再选择
而我们许多朋友在创建数据库时不考虑清楚往往选择一个默认的字符集如WE8ISO8859P1或US7ASCII而这两个字符集都没有汉字编码所以用这种字符集存储汉字信息从原则上说就是错误的虽然在有些时候选用这种字符集好象也能正常使用但它会给数据库的使用与维护带来一系列的麻烦在后面的迭代过程中我们将深入分析
客户端的字符集
有过一些Oracle使用经验的朋友大多会知道通过NLS_LANG来设置客户端的情况NLS_LANG由以下部分组成:NLS_LANG=_.其中第三部分的本意就是用来指明客户端操作系统缺省使用的字符集所以按正规的用法NLS_LANG应该按照客户端机器的实际情况进行配置尤其对于字符集一项更是如此这样Oracle就能够在最大程度上实现数据库字符集与客户端字符集的自动转换(当然是如果需要转换的话)
总结一下第一次迭代的重点:
字符集:将特定的符号集编码为计算机能够处理的数值;
字符集间的转换:对于在源字符集与目标字符集都存在的符号理论上转换将不会产生信息丢失;而对于在源字符集中存在而在目标字符集中不存在的符号理论上转换将会产生信息丢失;
数据库字符集:选择能够包含所有将要存储的信息符号的字符集;
客户端字符集设置:指明客户端操作系统缺省使用的字符集



第二次迭代:通过实例加深对基本概念的理解

下面我将引用网友tellin在ITPUB上发表的“CHARACTER SET研究及疑问”帖子该朋友在帖子中列举了他做的相关实验并对实验结果提出了一些疑问我将对他的实验结果进行分析并回答他的疑问
实验结果分析一
quote: 最初由 tellin 发布
设置客户端字符集为US7ASCII
D:>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII
查看服务器字符集为US7ASCII
SQL> 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: [ 更改客户端字符集为ZHS16GBK
D:>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

D:>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:
更改客户端字符集为US7ASCII
D:>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII

D:>SQLPLUS "/ AS SYSDBA"

无法显示用ZHS16GBK插入的字符集但可以显示用US7ASCII插入的字符集
SQL> SELECT * FROM TEST;

R1
----------
东北
??


更改服务器字符集为ZHS16GBK
SQL> update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';

1 row updated.

SQL> COMMIT;

更改客户端字符集为ZHS16GBK
D:>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

D:>SQLPLUS "/ AS SYSDBA"

可以显示以前US7ASCII的字符集但无法显示用ZHS16GBK插入的数据说明用ZHS16GBK插入的数据为乱码

SQL> SELECT * FROM TEST;

R1
--------------------
东北
??


需要指出的是通过“update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';”来修改数据库字符集是非常规作法很可能引起问题在这里只是原文引用网友的实验结果




实验结果分析四

quote:
SQL> INSERT INTO TEST VALUES('东北');

1 row created.

SQL> SELECT * FROM TEST;

R1
--------------------
东北
??
东北

SQL> EXIT


由于此时数据库与客户端的字符集设置均为ZHS16GBK所以不会发生字符集的转换第一行与第三行数据显示正确而第二行由于存储的数据就是63(00111111)所以显示的是“?”号

quote:
更改客户端字符集为US7ASCII

D:>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII

D:>SQLPLUS "/ AS SYSDBA"

无法显示数据

SQL> SELECT * FROM TEST;

R1
----------
??
??
??

疑问2:第一行数据是用US7ASCII环境插入的为何无法正常显示?


将客户端字符集设置改为US7ASCII后进行SELECTOracle检查发现数据库设置的字符集为ZHS16GBK数据需要进行字符集转换而第一行与第三行的汉字“东”与“北”在客户端字符集US7ASCII中没有对应字符所以转换为“替换字符”(“?”)而第二行数据在数据库中存的本来就是两个“?”号所以虽然在客户端显示的三行都是两个“?”号但在数据库中存储的内容却是不同的

实验结果分析五

quote:

SQL> INSERT INTO TEST VALUES('东北');

1 row created.

SQL> EXIT
更改客户端字符集为ZHS16GBK
D:>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

D:>SQLPLUS "/ AS SYSDBA"

无法显示用US7ASCII插入的字符集但可以显示用ZHS16GBK插入的字符集
SQL> SELECT * FROM TEST;

R1
--------------------
东北
??
东北
6+11

SQL>
疑问3:US7ASCII为ZHS16GBK的子集为何在US7ASCII环境下插入的数据无法显示? [/B]

在客户端字符集设置为US7ASCII时向字符集为ZHS16GBK的数据库中插入“东北”需要进行字符转换“东北”的ZHS16GBK编码为182(10110110)、171(10101011)与177(10110001)、177(10110001)由于US7ASCII为7bit编码Oracle将这两个汉字当作四个字符并忽略各字节的最高位从而存入数据库的编码就变成了54(00110110)、43(00101011)与49(00110001)、49(00110001)也就是“6+11”原始信息被改变了这时将客户端字符集设置为ZHS16GBK再进行SELECT数据库中的信息不需要改变传到客户端第一、三行由于存入的信息没有改变能显示“东北”而第二、四行由于插入数据时信息改变所以不能显示原有信息了



分析了这么多的内容但实际上总结起来也很简单要想在字符集方面少些错误与麻烦需要坚持两条基本原则:
在数据库端:选择需要的字符集(通过create database中的CHARACTER SET与NATIONAL CHARACTER SET子句指定);
在客户端:设置操作系统实际使用的字符集(通过环境变量NLS_LANG设置)


例如:
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10294527/viewspace-122378/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-08-29

  • 博文量
    3488
  • 访问量
    2621060