Oracle数据库字符集问题解析 zz

经常看到一些朋友问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=

  1<language>_<territory>.<clients characterset="">,其中第三部分<clients characterset="">的本意就是用来指明客户端操作系统缺省使用的字符集。所以按正规的用法,NLS_LANG应该按照客户端机器的实际情况进行配置,尤其对于字符集一项更是如此,这样Oracle就能够在最大程度上实现数据库字符集与客户端字符集的自动转换(当然是如果需要转换的话)。   
  2**总结一下第一次迭代的重点:**   
  3**字符集:** 将特定的符号集编码为计算机能够处理的数值;   
  4**字符集间的转换:** 对于在源字符集与目标字符集都存在的符号,理论上转换将不会产生信息丢失;而对于在源字符集中存在而在目标字符集中不存在的符号,理论上转换将会产生信息丢失;   
  5**数据库字符集:** 选择能够包含所有将要存储的信息符号的字符集;   
  6**客户端字符集设置:** 指明客户端操作系统缺省使用的字符集。 
  7
  8第二次迭代:通过实例加深对基本概念的理解 
  9
 10下面我将引用网友tellin在ITPUB上发表的“CHARACTER SET研究及疑问”帖子,该朋友在帖子中列举了他做的相关实验,并对实验结果提出了一些疑问,我将对他的实验结果进行分析,并回答他的疑问。   
 11**实验结果分析一**   
 12
 13
 14&gt; quote: 
 15&gt; 
 16&gt; * * *
 17&gt; 
 18&gt; _最初由 tellin 发布_   
 19&gt;  设置客户端字符集为US7ASCII    
 20&gt;  D:\&gt;SET NLS_LANG=AMERICAN_AMERICA.US7ASCII   
 21&gt;  查看服务器字符集为US7ASCII    
 22&gt;  SQL&gt; SELECT * FROM NLS_DATABASE_PARAMETERS;   
 23&gt;  PARAMETER VALUE   
 24&gt;  \------------------------------ ----------------------------------------   
 25&gt;  NLS_CHARACTERSET  US7ASCII    
 26&gt;    
 27&gt;  建立测试表    
 28&gt;  SQL&gt; CREATE TABLE TEST (R1 VARCHAR2(10));   
 29&gt;    
 30&gt;  Table created.   
 31&gt;    
 32&gt;  插入数据    
 33&gt;  SQL&gt; INSERT INTO TEST VALUES('东北');   
 34&gt;    
 35&gt;  1 row created.   
 36&gt;    
 37&gt;  SQL&gt; SELECT * FROM TEST;   
 38&gt;    
 39&gt;  R1   
 40&gt;  \----------   
 41&gt;  东北   
 42&gt;    
 43&gt;  SQL&gt; EXIT   
 44&gt;    
 45&gt; 
 46&gt; 
 47&gt; * * *
 48
 49  
 50  
 51这一部分的实验数据的存取与显示都正确,好象没什么问题,但实际上却隐藏着很大的隐患。   
 52首先,要将汉字存入数据库,而将数据库字符集设置为US7ASCII是不合适的。US7ASCII字符集只定义了128个符号,并不支持汉字。另外,由于在SQL*PLUS中能够输入中文,操作系统缺省应该是支持中文的,但在NLS_LANG中的字符集设置为US7ASCII,显然也是不正确的,它没有反映客户端的实际情况。   
 53但实际显示却是正确的,这主要是因为Oracle检查数据库与客户端的字符集设置是同样的,那么数据在客户与数据库之间的存取过程中将不发生任何转换。具体地说,在客户端输入“东北”,“东”的汉字的编码为182(10110110)、171(10101011),“北”汉字的编码为177(10110001)、177(10110001),它们将不做任何变化的存入数据库中,但是这实际上导致了 **数据库标识的字符集与实际存入的内容是不相符的,** 从某种意义上讲,这也是一种不一致性,也是一种错误。而在SELECT的过程中,Oracle同样检查发现数据库与客户端的字符集设置是相同的,所以它也将存入的内容原封不动地传送到客户端,而客户端操作系统识别出这是汉字编码所以能够正确显示。   
 54在这个例子中,数据库与客户端的设置都有问题,但却好象起到了“负负得正”的效果,从应用的角度看倒好象没问题。但这里面却存在着极大的隐患,比如在应用length或substr等字符串函数时,就可能得到意外的结果。另外,如果遇到导入/导出(import /export)将会遇到更大的麻烦。有些朋友在这方面做了大量的测试,如eygle研究了“源数据库字符集为US7ASCII,导出文件字符集为US7ASCII或ZHS16GBK,目标数据库字符集为ZHS16GBK”的情况,他得出的结论是 “如果的是在Oracle92中,我们发现对于这种情况,不论怎样处理,这个导出文件都无法正确导入到Oracle9i数据库中”、“对于这种情况,我们可以通过使用Oracle8i的导出工具,设置导出字符集为US7ASCII,导出后修改第二、三字符,修改 0001 为0354,这样就可以将US7ASCII字符集的数据正确导入到ZHS16GBK的数据库中”。我想对于这些结论,这样理解可能更合适一些: **由于ZHS16GBK字符集是US7ASCII的超级,所以如果按正常操作,这种转换应该没有问题;但出现问题的本质是我们让本应只存储英文字符的US7ASCII数据库,非常规地存储了中文信息,那么在转化过程中出现错误或麻烦就没什么奇怪的了,不出麻烦倒是有些奇怪了。**   
 55所以说要避免这种情况,就是要在建立数据库时选择合适的字符集,不让标签(数据库的字符集设置)与实际(数据库中实际存储的信息)不符的情况发生。 
 56
 57实验结果分析二 
 58
 59&gt; quote: 
 60&gt; 
 61&gt; * * *
 62&gt; 
 63&gt; [  更改客户端字符集为ZHS16GBK    
 64&gt;  D:\&gt;SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK   
 65&gt;    
 66&gt;  D:\&gt;SQLPLUS "/ AS SYSDBA"   
 67&gt;    
 68&gt;  无法正常显示数据    
 69&gt;    
 70&gt;  SQL&gt; SELECT * FROM TEST;   
 71&gt;    
 72&gt;  R1   
 73&gt;  \--------------------   
 74&gt;  6+11   
 75&gt;    
 76&gt;  疑问1:ZHS16GBK为US7ASCII的超集,为什么在ZHS16GBK环境下无法正常显示    
 77&gt;    
 78&gt; 
 79&gt; 
 80&gt; * * *
 81
 82  
 83  
 84这主要是因为Oracle检查发现数据库设置的字符集与客户端配置字符集不同,它将对数据进行字符集的转换。数据库中实际存放的数据为182(10110110)、171(10101011)、177(10110001)、177(10110001),由于数据库字符集设置为US7ASCII,它是一个7bit的字符集,存储在8bit的字节中,则Oracle忽略各字节的最高bit,则182(10110110)就变成了54(0110110),在ZHS16GBK中代表数字符号“6”(当然在其它字符集中也是“6”),同样过程也发生在其它3个字节,这样“东北”就变成了“6+11”。   
 85  
 86实验结果分析三 
 87
 88&gt; quote: 
 89&gt; 
 90&gt; * * *
 91&gt; 
 92&gt; _最初由 tellin 发布_   
 93&gt;  用ZHS16GBK插入数据    
 94&gt;  SQL&gt; INSERT INTO TEST VALUES('东北');   
 95&gt;    
 96&gt;  1 row created.   
 97&gt;    
 98&gt;  SQL&gt; SELECT * FROM TEST;   
 99&gt;    
100&gt;  R1   
101&gt;  \--------------------   
102&gt;  6+11   
103&gt;  ??   
104&gt;    
105&gt;  SQL&gt; EXIT   
106&gt; 
107&gt; 
108&gt; * * *
109
110  
111当客户端字符集设置为ZHS16GBK后向数据库插入“东北”,Oracle检查发现数据库设置的字符集为US7ASCII与客户端不一致,需要进行转换,但字符集ZHS16GBK中的“东北”两字在US7ASCII中没有对应的字符,所以Oracle用统一的“替换字符”插入数据库,在这里为“?”,编码为63(00111111),这时,输入的信息实际上已经丢失,不管字符集设置如何改变(如下面引用的实验结果),第二行SELECT出来的结果也都是两个“?”号(注意是2个,而不是4个)。   
112
113
114&gt; quote: 
115&gt; 
116&gt; * * *
117&gt; 
118&gt;   
119&gt;  更改客户端字符集为US7ASCII    
120&gt;  D:\&gt;SET NLS_LANG=AMERICAN_AMERICA.US7ASCII   
121&gt;    
122&gt;  D:\&gt;SQLPLUS "/ AS SYSDBA"   
123&gt;    
124&gt;  无法显示用ZHS16GBK插入的字符集,但可以显示用US7ASCII插入的字符集    
125&gt;  SQL&gt; SELECT * FROM TEST;   
126&gt;    
127&gt;  R1   
128&gt;  \----------   
129&gt;  东北   
130&gt;  ??   
131&gt;    
132&gt;    
133&gt;  更改服务器字符集为ZHS16GBK    
134&gt;  SQL&gt; update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';   
135&gt;    
136&gt;  1 row updated.   
137&gt;    
138&gt;  SQL&gt; COMMIT;   
139&gt;    
140&gt;  更改客户端字符集为ZHS16GBK    
141&gt;  D:\&gt;SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK   
142&gt;    
143&gt;  D:\&gt;SQLPLUS "/ AS SYSDBA"   
144&gt;    
145&gt;  可以显示以前US7ASCII的字符集,但无法显示用ZHS16GBK插入的数据,说明用ZHS16GBK插入的数据为乱码。    
146&gt;    
147&gt;  SQL&gt; SELECT * FROM TEST;   
148&gt;    
149&gt;  R1   
150&gt;  \--------------------   
151&gt;  东北   
152&gt;  ??   
153&gt; 
154&gt; 
155&gt; * * *
156
157  
158需要指出的是,通过“update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';”来修改数据库字符集是非常规作法,很可能引起问题,在这里只是原文引用网友的实验结果。 
159
160  
161
162
163实验结果分析四 
164
165&gt; quote: 
166&gt; 
167&gt; * * *
168&gt; 
169&gt;   
170&gt;  SQL&gt; INSERT INTO TEST VALUES('东北');   
171&gt;    
172&gt;  1 row created.   
173&gt;    
174&gt;  SQL&gt; SELECT * FROM TEST;   
175&gt;    
176&gt;  R1   
177&gt;  \--------------------   
178&gt;  东北   
179&gt;  ??   
180&gt;  东北   
181&gt;    
182&gt;  SQL&gt; EXIT   
183&gt; 
184&gt; 
185&gt; * * *
186
187  
188由于此时数据库与客户端的字符集设置均为ZHS16GBK,所以不会发生字符集的转换,第一行与第三行数据显示正确,而第二行由于存储的数据就是63(00111111),所以显示的是“?”号。   
189
190
191&gt; quote: 
192&gt; 
193&gt; * * *
194&gt; 
195&gt;   
196&gt;  更改客户端字符集为US7ASCII    
197&gt;    
198&gt;  D:\&gt;SET NLS_LANG=AMERICAN_AMERICA.US7ASCII   
199&gt;    
200&gt;  D:\&gt;SQLPLUS "/ AS SYSDBA"   
201&gt;    
202&gt;  无法显示数据    
203&gt;    
204&gt;  SQL&gt; SELECT * FROM TEST;   
205&gt;    
206&gt;  R1   
207&gt;  \----------   
208&gt;  ??   
209&gt;  ??   
210&gt;  ??   
211&gt;    
212&gt;  疑问2:第一行数据是用US7ASCII环境插入的,为何无法正常显示?    
213&gt; 
214&gt; 
215&gt; * * *
216
217  
218将客户端字符集设置改为US7ASCII后进行SELECT,Oracle检查发现数据库设置的字符集为ZHS16GBK,数据需要进行字符集转换,而第一行与第三行的汉字“东”与“北”在客户端字符集US7ASCII中没有对应字符,所以转换为“替换字符”(“?”),而第二行数据在数据库中存的本来就是两个“?”号,所以虽然在客户端显示的三行都是两个“?”号,但在数据库中存储的内容却是不同的。   
219  
220
221
222实验结果分析五 
223
224&gt; quote: 
225&gt; 
226&gt; * * *
227&gt; 
228&gt;   
229&gt;    
230&gt;  SQL&gt; INSERT INTO TEST VALUES('东北');   
231&gt;    
232&gt;  1 row created.   
233&gt;    
234&gt;  SQL&gt; EXIT   
235&gt;  更改客户端字符集为ZHS16GBK   
236&gt;  D:\&gt;SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK    
237&gt;    
238&gt;  D:\&gt;SQLPLUS "/ AS SYSDBA"   
239&gt;    
240&gt;  无法显示用US7ASCII插入的字符集,但可以显示用ZHS16GBK插入的字符集    
241&gt;  SQL&gt; SELECT * FROM TEST;   
242&gt;    
243&gt;  R1   
244&gt;  \--------------------   
245&gt;  东北   
246&gt;  ??   
247&gt;  东北   
248&gt;  6+11   
249&gt;    
250&gt;  SQL&gt;   
251&gt;  疑问3:US7ASCII为ZHS16GBK的子集,为何在US7ASCII环境下插入的数据无法显示?  [/B] 
252&gt; 
253&gt; * * *
254
255  
256在客户端字符集设置为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,数据库中的信息不需要改变传到客户端,第一、三行由于存入的信息没有改变能显示“东北”,而第二、四行由于插入数据时信息改变,所以不能显示原有信息了。   
257  
258
259
260分析了这么多的内容,但实际上总结起来也很简单,要想在字符集方面少些错误与麻烦,需要坚持两条基本原则:   
261**在数据库端:选择需要的字符集** (通过create database中的CHARACTER SET与NATIONAL CHARACTER SET子句指定);   
262**在客户端:设置操作系统实际使用的字符集** (通过环境变量NLS_LANG设置)。   
263
264
265**例如:  
266CHARACTER SET ZHS16GBK   
267NATIONAL CHARACTER SET AL16UTF16 **</clients></clients></territory></language>
Published At
Categories with 数据库类
Tagged with
comments powered by Disqus