【转载】ORACLE字符集研究Oracle一体机用户组

喜欢本文请长按上方的二维码订阅Oracle一体机用户组

作者简介

普朝晖，现就职于北京海天起点技术服务股份有限公司，担任数据库技术顾问，获有OCP11g证书，具有多年数据库管理维护经验，服务对象覆盖税务、金融、电力等大型企事业单位。擅长于ORACLE数据库环境搭建及故障诊断，对于OGG搭建及维护也颇有见解。

之前对Oracle字符集的理解一直不够深刻，在很多相关配置方面知其然却不知其所以然，这对我照成很多困扰，下面大家可以先思考几个之前一直困扰我的问题，在介绍完相关知识之后会对其进行解答。

1.在进行查询或exp/imp导出数据时，NLS_LANG参数到底该设置成什么？

2.为什么US7ASCII作为英文字符集却可以存放中文字符？为什么从ZHS16GBK的数据库导出的dmp文件导入AL32UTF8后，中文可以正常显示，而从US7ASCII的数据库中导出的dmp文件导入AL32UTF8后，中文无法正常显示？

字符集

字符集这个名词在ORACLE中其实涵盖了2个信息，一个是它所能表示的所有字符的集合，另一个是它的字符编码方式。

从字符集合的角度来谈论字符集：

例如US7ASCII字符集，它所能表示的所有字符的集合是A-Z、a-z及一些常用标点符号，但由于它是7bit字符集，一共只能表示128个字符。而ZHS16GBK由2字节表示一个字符，所以它除了能表示英文、标点符号外，还能表示几乎所有汉字。而AL32UTF8则可以使用2个以上字节来表示一个字符，所以它几乎涵盖了世界上说有语言字符。

从编码方式的角度来谈论字符集:

计算机只能识别0和1，人只能看懂字符，所谓编码方式就是字符与实际二进制数的对应关系，不同字符集相同字符所对应的二进制数一般都是不一样的，但也有可能出现不同字符集相同字符对应的二进制数相同的情况（例如US7ASCII的英文字母编码与AL32UTF8是相同的）。

超集

在使用”超集”的概念来形容ORACLE数据库字符集时，它用于反映字符集间”字符集合”层面的关系，如果一个字符集能够表示另一个字符集的所有字符，那它就是另一个字符集的超集。

NLS_LANG

格式为：

NLS_LANG=_.  

其中_对数据的存储、查寻及字符转换关系不是很大，这里不做过多讨论。重要的在于第三部分，很多人对该部分存在误解，认为该参数的设置应与要访问的数据库字符集一致，但事实上这是错误的。该参数的真正用途大致可以分为两个：

• 告诉数据库正要传进的二进制数据是用什么字符集编码方式编码的。

• 告诉数据库写出的数据要用什么字符集编码方式存放或递交给其它应用。

现在借助上图来模拟一个用户在远程客户端访问本地数据库的整个数据的传输及转变过程：

1.用户在远程客户端应用程序（sqlplus）上输入一些字符，这些人类可以识别的字符通过客户端应用程序自己的字符集编码为计算机可以识别的二进制代码。

2.通过网络将这些二进制代码原封不动的传到本地服务端。

3.数据库接收到这些二进制编码后不是立马存入数据库，而是先获取远程客户端的NLS_LANG参数。

4.第四步有两种可能：

• 如果远程客户端的NLS_LANG等于数据库字符集，那么数据库就认为传过来的二进制编码是用自己字符集的编码方式编码的，那么这些编码将不经过转码直接写入数据库内！

• 如果远程客户端的NLS_LANG不等于数据库字符集，那么数据库就将会进行一次转码，将原二进制编码转换成符合自己字符集编码方式的编码。

注：数据库字符集与远程服务器应用软件（如sqlplus）字符集可能相同也可能不同，远程服务器应用软件（如sqlplus）的字符集取决于软件本身及所在操作系统。  

上述例子描述了一个用户通过远程客户端应用软件(sqlplus)向服务端数据库写入数据的情形，而查询的步骤除了有以上写入的步骤外还多了一个返给客服端应用程序数据的逆过程，就不作过多说明了，但是不管是查询还是写入，用到的都是客户端的NLS_LANG,服务端的NLS_LANG并未派上用场。

以下两种情况都有可能显示乱码：

1.数据存储正确，但NLS_LANG设置错误:    

因为应用程序的字符集是固定的，如果NLS_LANG与应用程序不一致，写出的二进制代码是由NLS_LANG所指定的字符集编码的，而应用程序还是会将接受到的代码用自己本身字符集的编码方式来译码，这样当然就无法正确显示字符了。例：数据库采用A字符集存储’我’字，编码后的二进制代码为’00’,NLS_LANG设置为B，那么在将数据从数据库里读出时，就会被写成’我’字的B字符集二进制代码，假设为’01’，而应用程序收到该二进制代码时则直接用自己的C字符集进行译码，B字符集编码的’我’字，用C字符集来译码，这当然就得不到’我’字了，除非它们’我’字对应的二进制编码是一样的。

2.数据存储错误，但NLS_LANG设置正确：

如果存储在数据库中的数据编码本身就是错的，那么就算你的NLS_LANG设置正确，查出的数据也可能是乱码，至于这种情况是如何发生的我们下文再谈。

除了以上两种情况外，还有一种情况那就是数据存储也有错误，NLS_LANG设置得也有错误，这种情况看似一定显示不出正确结果了，但其实不然，没准它还就能显示正确呢，同样见下文。

在这里我来提一个问题并进行解答，来方便大家理解：

问：

数据库统一为C机器：v$nls_parameters中NLS_CHARACTERSET为AL32UTF8

A机器：Linux，设置NLS_LANG为AMERICAN_AMERICA.AL32UTF8，在终端上使用sqlplus插入汉字”中”

B机器：Windows，设置NLS_LANG为AMERICAN_AMERICA.AL32UTF8，在命令行上通过sqlplus可以正确显示’中’字吗？

答：不能

首先A机器为Linux：一般我们现在装的Linux都是UTF8编码，可以通过locale查看。所以，在Linux终端上输入的’中’字，在Linux操作系统层编码为：E4B8AD。因为客户端Linux上设置的NLS_LANG和数据库服务器的字符集一致，都是AL32UTF8，不需要经过转码，就直接将E4B8AD这个编码写入数据库。然后B机器想要查看，B机器的NLS_LANG和数据库服务器字符集一致，不转码，所以数据库服务器直接将E4B8AD扔给B机器。但是B机器是windows机器，默认sqlplus编码为GBK。E4B8AD这个编码在GBK上是’涓’，所以B机器sqlplus查出的是涓字。

导入导出的机制与上述的增删改成略有不同，但其实是相通的，这里借用一下MOS文档的流程图：

可以很清楚的看到，在本地执行exp导出dump文件传输到远程执行导入最多会发生三次字符编码转换！

第一次：如果本地NLS_LANG与数据库字符集不同，那么数据将在转码后写入dmp文件，如果相同则直接写入，并且该dmp文件是用什么字符集编码的将被记录在文件头中。

第二次：在dmp文件被传到目标服务器后，如果目标服务器NLS_LANG与dmp文件头中记录的符集不同，那么又将发生一次转码，如果相同则不发生。

第三次：如果目标服务器NLS_LANG与目标库字符集不同，那么将会发生第三次转码才能将数据写入目标库。

（知道这个机制，就可以看出，只有一次转码是必要的，那就是原库字符集转换成目标库字符集）

对于最开始的两个问题，在了解了字符集转换机制后，是不是很容易作答了。

1.在进行查询或exp/imp导出数据时，NLS_LANG参数到底该设置成什么？

回答这个问题是要分情况的：

如果是要通过sqlplus之类的应用程序来对数据库进行增删改查，那么客户端的NLS_LANG得设置得和应用程序（sqlplus）字符集一致。

如果是exp/imp数据迁移的话，最好把本地和远程的NLS_LANG都设置成原库的字符集，这样可以减少转码次数，也就减少了出问题的可能。

2.为什么US7ASCII作为英文字符集却可以存放中文字符？为什么从ZHS16GBK的数据库导出的dmp文件导入AL32UTF8后，中文可以正常显示，而从US7ASCII的数据库中导出的dmp文件导入AL32UTF8后，中文无法正常显示？  

按道理来说，子集向超集的转换是完全没问题的，所以ZHS16GBK转到AL32UTF8中文正常显示是理所当然的，但US7ASCII同样作为AL32UTF8的子集，为什么中文转换后会变成乱码呢？  

US7ASCII可以表示中文字符吗？显然是不行的，就像前面介绍的，该字符集只能表示英文字母和一些常用标点符号。  

但是确实有用US7ASCII字符集的库存放中文的情况，而且存进去了查出来的也是正确的中文字符，无乱码。这种情况发生纯属巧合，它是一种前面提到的数据存储也有错误，NLS_LANG设置得也有错误，而产生的神奇情况。当客户端NLS_LANG与数据库字符集均为US7ASCII时，由客户端应用程序编码的二进制代码被不经转换的直接写入了数据库中。在这种情况下中文便被存入了数据库中，而数据则当且仅当按原路返回时，才能正常被显示出来。何为原路返回？就是NLS_LANG和数据写入时的NLS_LANG一样等于数据库字符集US7ASCII,而读取用的应用程序字符集与写入用的应用程序字符集一致。

那么第二问也就可以顺理成章的给出答案了，按道理来说dmp文件在导入目标数据库时是会自动转换字符集的，为什么存在US7ASCII中的中文不论NLS_LANG如何设置，导入到UTF8库中就成了乱码？原因很简单，那就是存放在数据库里的二进制代码并不是US7ASCII编码的，而在进行任何字符集转换时，这些二进制编码都被误当成是US7ASCII编码的了，这样已转换就理所当然会发生错误。

附：

 如何将US7ASCII中存放的中文导入AL32UTF8数据库：

1.原库服务器NLS_LANG设置成AMERICAN_AMERICA.US7ASCII

2.目标库服务器NLS_LANG设置成写入数据到原库的应用程序的字符集

3.修改dump文件头,将字符集标识位设置成写入数据到原库的应用程序的字符集

在dmp文件头中一共有三处记录了字符集，我们只能修改第三处！（仅适用于11gexp,其它版本未测）

如何ogg同步US7ASCII中存放的中文导入AL32UTF8数据库：

1.抽取进程中设置NLS_LANG为US7ASCII。

2.复制进程中设置NLS_LANG为写入数据到原库的应用程序的字符集。

Character Sets, Code Pages,Fonts and the NLS_LANG Value (文档 ID137127.1)

NLS_LANG Explained (How doesClient-Server Character Conversion Work?) (文档ID 158577.1)

The Correct NLS_LANG Settingin Unix Environments (文档 ID 264157.1)

The Correct NLS_LANG in aMicrosoft Windows Environment (文档 ID179133.1)

Old Exp/Imp (not datapump)and NLS Considerations (文档 ID15095.1)

NLS considerations inImport/Export – Frequently Asked Questions (文档ID 227332.1)

原创文章，版权归本文作者所有，如需转载请注明出处