字符集、编码、Oracle

ANSI code pages：你一定见过ANSI，想想另存文本文件时。ANSI code pages实际上是一系列的编码集合，根据操作系统区域设置而**其中一种作为默认ANSI编码。例如公司电脑（英文系统）上的ANSI code page可能是1252，而家里的中文系统则可能是936。所以在家里可以用ANSI存储一个包含中文的文本文件，在公司则不行。可以在注册表键：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage\ACP中查看到当前使用的ANSI code page。 C#可以通过Encoding.Default查看。

OEM code pages： OEM code pages是给控制台应用程序（如SQLPLUS）使用的。除CJK环境（Chinese-Japanese-Korean）外，Windows使用不同的ANSI code page和OEM code page。例如，公司英文系统上使用的是437。可以使用CHCP命令查看当前使用的OEM code page， C#可以通过Console.OutputEncoding查看。

Code page 1252：

cp1252字符集：ASCII字符集+若干西欧字符+若干特殊符号，比如™、‰.

cp1252编码：使用8位表示一个字符。编码范围是[0-255]（即Hex[00-FF]），[0-127]部分与ASCII相同，新增的大部分是西欧的字符，例如一些带上标的字母Â、Ë，以及像这样一类特殊符号）

PS1：现实中两台PC上的code page信息

PC 1：英文版Windows XP，ANSI code page=1252, OEM code page=437

PC 2：中文版Windows 7，ANSI code page=936, OEM code page=936

PS2：cp1252与cp437编码表，早期控制台应用程序常常需要画一些粗糙的表格等等图形，所以可以在437中看到不少不同的横线竖线这一类的特殊符号。

PS3：CP1252、ISO8859-1、ASCII比较，就实际使用的编码范围来说：CP1252>ISO8859-1>ASCII。ASCII是[0-127]，CP1252是[0-255]，ISO8859-1则移除了cp1252中[0-31]及127这些不可见的控制符，同进移除了[128-159]（即Hex[80-9F]）中的特殊符号。

第二部分 Oracle中的编码与字符集

1.为什么需要两个字符集？

Oracle中有两个字符集：

1）数据库字符集

2）国家字符集

为什么要有两个字符集？如果我知道只需要英文，设置数据库字符集=US7ASCII，如果我知道只需要西欧字符，设置数据库字符集=WE8MSWIN1252或者WE8ISO89859P1，或者干脆就用AL32UTF8。你看，我只需要设定“数据库字符集”，那么“国家字符集”有什么必要呢？

其实，考虑到历史遗留问题以及数据库创建者们无法避免的“短视”，很多现有数据库都无法支持UNICODE字符集，例如要在现有的US7ASCII数据库字符集的数据库中存储中文，这个时候“国家字符集”+NVARCHAR2这样的组合就能救你一命了。对于数据类型为NVARCHAR2(以及NCHAR, NCLOB)的字段，它使用是国家字符集，与数据库字符集的设置无关。自9i以后，国家字符集可选的只有AL16UTF16与AL32UTF8，UTF-16与UTF-8都是UNICODE编码标准的实现，因此可以表示世界上几乎所有的文字。

当然，如果数据库字符集本身就使了UNICODE字符集，就没有必要使用NVARCHAR2, NCHAR, NCLOB这些类型了。

2.字符集名称的玄机

Oracle对字符集的命名实际上有一定的规则可寻，例如：

AL32UTF8：

【AL】支持所有语言（All Language）。

【32】每字符最多占用32位（4字节）。

【UTF8】编码为UTF-8。

WE8MSWIN1252：

【WE】支持西欧语言（Western Europe）。

【8】每字符需要占用8位（单字节）。

【MSWIN1252】编码为CP1252。

US7ASCII：

【US】表示美国（United States）。

【7】每字符需要占用7位。

【ASCII】编码为ASCII。

其它如ZHS16GBK，ZHT16BIG5，US8PC437（编码为OEM cp437），都可以类推。

3.例子很重要

3.1.准备两个数据库

上帝说要有例子，于是有了两个相同版本的数据库，A跟B：

SELECT parameter, VALUE
FROM nls_database_parameters
WHERE parameter  IN ( ' NLS_CHARACTERSET ',  ' NLS_NCHAR_CHARACTERSET ')

-- 数据库A：
PARAMETER                      VALUE
-- ---------------------------- -------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16

-- 数据库B：
PARAMETER                      VALUE
-- ---------------------------- ----------------- 
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CHARACTERSET         AL16UTF16

-- 在A和B中分别创建一张表。
CREATE  TABLE charset_test 
(id  NUMBER( 4)  PRIMARY  KEY,
vc  VARCHAR2( 20),
nvc NVARCHAR2( 20));

3.2.工具很重要

在测试之前，为避免工具本身的特性给人造成的困惑，介绍一下几个客户端工具对UNICODE 的支持情况：

SQLPLUS：不支持UNICODE字符集。是否支持中文取决于当前的OEM code page，如果是cp437，无论输入还是显示中文都是不可能的。但如果是cp936，则可以支持中文输入输出。

PLSQL Developer：7.0版本的查询结果窗口支持UNICODE字符集，但是编辑窗口(即输入SQL语句的窗口)不支持。8.0版完全支持UNICODE。

Oracle SQL Developer：查询结果窗口与编辑窗口都支持UNICODE字符集。

3.3.出现乱码了

这里使用Oracle SQL Developer，分别在A、B中插入并查询中文：

INSERT  INTO charset_test  VALUES( 1, ' 中 ', ' 中 ');
COMMIT;

-- A库
SELECT  *  FROM charset_test;
1     ¿     ¿

-- B库
SELECT  *  FROM charset_test;
1     中    中

暂时先跳过VARCHAR2字段，先来关注NVARCHAR2字段，为什么在A库不能正常显示？无非有这几种可能：

1. 客户端操作系统不支持显示中文。

2. Oracle客户端工具（这里是Oracle SQL Developer）不支持显示中文。

3. Oracle客户端有相关设置（比如NLS_LANG）不正确。

4. 存储在数据库中的数据已经是不正确的数据。

第一点，客户端操作系统是否支持中文对运行于其上的应用程序有影响吗？应该有两种情况，一种是应用程序依赖于操作系统的中文支持；另一种是有一些软件自己带有语言包及字体（比如Adobe的一些产品，.NET程序在编译的时候也可以选择将字体文件打包进去），那么它应该不依赖于操作系统。

我猜测Oracle SQL Developer应该是属于前一种，同时我检查了操作系统，确定其已经支持东亚语言（Control panel—Regional and language options—Language tab—Supplemental languages support—Install files for East Asian languages，如果checkbox已经选中，说明已经安装东亚语言包）。

第二点，无论查询结果窗口还是编辑窗口都支持UNICODE字符集。

第三点，由于不依赖于Oracle client的OCI，客户端注册表中的NLS_LANG设置对像Oracle SQL Developer没有影响。

第四点，我们借助DUMP()函数来确定NVARCHAR2字段中具体的内容。

DUMP()的语法：DUMP(<value>[,<format>[,<offset>[,<length>]]])

其中的format参数：如果是8则表示结果使用8进制表示，如果是16则表示16进制，如果是0到16间的其它数则都使用10进制。如果是大于16的数，则分几种情况：如果是可见的ASCII字符则直接打印此字符，如果是控制字符则打印成“^x”，其它情况则把结果按16进制显示。为format加上1000则表示除了输出结果之外，还会附带输出所使用的字符集信息。

这里我们使用：

SELECT  DUMP(nvc, 1016)  FROM charset_test;
-- A库
Typ = 1  Len = 2 CharacterSet =AL16UTF16:  0,bf

-- B库
Typ = 1  Len = 2 CharacterSet =AL16UTF16: 4e,2d

我们知道“中”字的UTF-16编码是4E2D，显然在A库中存储的数据已经是不对的，00BF实际上就是一个倒的问号字符，其存储在数据库中的原始数据已经不对了，更何况是客户端的显示。

3.4.找不同

那么为什么两个库会不一样呢？嫌疑很快就落在了数据库字符集上，因为A和B的区别只在数据库字符集上，一个是WE8MSWIN1252，另一个是AL32UTF8。经过测试，结论是：

Oracle SQL Developer忽略NLS_LANG，字符串直接以照数据库字符集进行编码后由客户端传输到服务器端。由于A库数据库字符集不支持汉字，很不幸地被替换成了默认的BF并最终被存储到数据库中，永远地错下去。B库则相反，中文在传输的过程中“存活”下来并成功到达服务器端，最终自动转换成NVARCHAR2所用的编码并存储到库中。

3.5.如何让NVARCHAR2字段工作

看起来似乎A库中的NVARCHAR2字段永远也无法正常使用了，并非这样，对于Oracle SQL Developer，通过一些设置，就可以让NVARCHAR2可以正常地插入、查询。

找到{ORACLE_HOME}\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf（依赖于你的Oracle SQL Developer安装路径），添加一行配置：

AddVMOption -Doracle.jdbc.convertNcharLiterals=true

同时在中文字符串前添加“N”前缀：

INSERT  INTO charset_test  VALUES( 2, ' 中 ',N ' 中 ');
-- NVARCHAR2列中的中文不再是乱码了
SELECT  *  FROM charset_test  WHERE id = 2;
2     ¿     中

这个配置起到的作用是这样的：在INSERT语句从客户端传输到服务器端之前，Oracle SQL Developer检测（实际上是JDBC检测）语句，如果发现“N”前缀，则事先将这部分的字符串按UTF-16进行编码得到16进制串。也就是相当于执行了这个命令：

INSERT INTO charset_test VALUES(2,’中’,UNISTR('\4e2d'));

C#不需要做特殊的配置来让NVARCHAR2正常工作，只需要在执行INSERT时使用参数并选择正确的参数类型：

cmd.CommandText =  " insert into charset_test values(3,:vc,:nvc) ";
OracleParameter p1 =  new OracleParameter( " vc ", OracleDbType.Varchar2);
OracleParameter p2 =  new OracleParameter( " nvc ", OracleDbType.NVarchar2);
p1.Value =  " 中 ";
p2.Value =  " 中 ";
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();

4.客户端的NLS_LANG设置及编码转换（完全没看懂，先放着）

前面我说过Oracle SQL Developer忽略客户端NLS_LANG设置，那么对于其它的工具呢？（这里我们主要关注字符集及编码，不讨论NLS_LANG对日期格式、排序方式、数字显示格式等等的影响）

SQLPLUS，插入与查询都依赖于客户端NLS_LANG设置。通常，客户端NLS_LANG设置要与当前的OEM Codepage一致，比如US8PC437。

PL/SQL Developer，插入与查询都依赖于客户端NLS_LANG设置。通常，客户端NLS_LANG设置要与数据库字符集一致。

使用SQLPLUS可以清晰地看到Oracle编码转换的过程：

1) 在Oracle客户端向服务器端提交SQL语句时，Oracle客户端根据NLS_LANG和数据库字符集，对从应用程序接传送过来的字符串编码进行转换处理。如果NLS_LANG与数据库字符集相同，不作转换，否则要转换成数据库字符集并传送到服务器。服务器在接收到字符串编码之后，对于普通的CHAR或VARCHAR2类型，直接存储；对于NCHAR或NVARCHAR2类型，服务器端将其转换为国家字符集再存储。

2) 在查询数据时，服务器端原样返回存储在库中的数据，由客户端根据返回的元数据中的字符集信息与NLS_LANG和NLS_NCHAR的设置进行比较（如果NLS_NCHAR没有设置，则其默认值为NLS_LANG中的字符集设置），如果元数据中的字符集信息与客户端设置一致，不进行转换，否则要进行转换。国家字符集的转换根据NLS_NCHAR设置进行转换。

这里我也举几个使用SQLPLUS的测试例子，分别在A、B两库执行相同的语句，然后通过网络抓包查看从Oracle client传输到服务器的具体内容。

例1 客户端NLS_LANG：WE8MSWIN1252

SQL命令：insert into charset_test values(1,'æ',null);

网络抓包(A库，数据库字符集为WE8MSWIN1252)：91

解释：由于应用程序(即SQLPLUS)使用的编码是Codepage437，所以æ的编码是91。当91被传给Oracle client后，Oracle client根据NLS_LANG误判其使用的编码是Codepage1252，又由于NLS_LANG设置与数据库字符集一致，于是Oracle client不进行编码转换，91被直接传给服务器并存储，考虑到数据库字符集是Codepage1252，很显然91是错误的数据(字符[æ]在Codepage1252下的编码是E6，而非91)。

这个错误导致了一个有趣的现象，那就是在同一个客户端使用SQLPLUS查询居然可以看到正确字符[æ]，这是由于SELECT的时候91也被直接返回，并且在Oracle client也不进行编码转换而是直接传给了应用程序，恰巧应用程序根据自己使用的编码可以正确解析91。但是换一个客户端机器，或者换一个客户端工具都可能得到不一样的查询结果。

网络抓包(B库，数据库字符集为AL32UTF8)：E2 80 98

解释：由于应用程序(即SQLPLUS)使用的编码是Codepage437，所以æ的编码是91。当91被传给Oracle client后，Oracle client根据NLS_LANG误判其使用的编码是Codepage1252，而91在Codepage1252中对应的是字符[‘]，根据Codepage1252到数据字符集UTF8的转换，最终转换成了E2 80 98，即UTF8下的[‘]。

例2 客户端NLS_LANG：US7ASCII

SQL命令：insert into charset_test values(1,'æ',null);

网络抓包(A库)：BF

解释：由于应用程序(即SQLPLUS)使用的编码是Codepage437，所以æ的编码是91。当91被传给Oracle client后，Oracle client根据NLS_LANG误判其使用的编码是ASCII，而91在ASCII中是无效编码，根据ASCII到数据字符集Codepage1252的转换，最终转换成了BF，BF是Codepage1252遇到无效编码时使用的默认替换编码。

网络抓包(B库)： EF BF BD

解释：由于应用程序(即SQLPLUS)使用的编码是Codepage437，所以æ的编码是91。当91被传给Oracle client后，Oracle client根据NLS_LANG误判其使用的编码是ASCII，而91在ASCII中是无效编码，根据ASCII到数据字符集UTF8的转换，最终转换成了EF BF BD，EF BF BD是UTF8遇到无效编码时使用的默认替换编码。

例3 客户端NLS_LANG：US8PC437

SQL命令：insert into charset_test values(1,'æ',null);

网络抓包(A库)：E6

解释：E6是字符[æ]的正确的Codepage1252编码，此次由于应用程序(即SQLPLUS)使用的是Codepage437，Oracle client从NLS_LANG获得的编码信息也是Codepage437，于是进行了正确的编码转换。

网络抓包(B库)：C3 A6

解释：C3 A6是字符[æ]的正确的UTF8编码，此次由于应用程序(即SQLPLUS)使用的是Codepage437，Oracle client从NLS_LANG获得的编码信息也是Codepage437，于是进行了正确的编码转换。

我觉得，只有SQLPLUS的日子总是那么美好，一切看起来既合理又可解释。当其它工具出现之后，世界就变得一团乱麻了，Oracle SQL Developer完全忽略客户端NLS_LANG设置倒是让事情变得简单，不过PL/SQL Developer则是另一回事，我花了4天时间企图搞明白其中的编码转换过程，最终只证明它就是个不可理喻的玩意儿，唯一目前看起来还正确的结论是：如果要用PL/SQL Developer，只好还是把NLS_LANG设置得跟数据库字符集一致。其它就只能自求多福了。

5.NLS_LANG对ODP.NET的影响（额也没看懂）

cmd.CommandText =  " select * from charset_test where id=6 ";
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
    OracleString c1 = odr.GetOracleString( 1); // varchar2 field, value ‘æ’
    OracleString c2 = odr.GetOracleString( 2); // nvarchar2 field, value ‘æ’
     byte[] bs1 = c1.GetNonUnicodeBytes(); // 230(using WE8MSWIN1252);63(using US7ASCII)
     byte[] bs3 = c2.GetNonUnicodeBytes(); // 230(using WE8MSWIN1252);63(using US7ASCII)
}

唯一受客户端NLS_LANG影响的是OracleString的GetNonUnicodeBytes()方法，此方法依赖于客户端本地设置的字符集，例如我们把NLS_LANG从AMERICAN_AMERICA.WE8MSWIN1252改成AMERICAN_AMERICA.US7ASCII

其中230（即HexE6）正是字符‘æ’的编码，而63（即Hex3F）是ASCII中的问号（由于ASCII字符集中没有‘æ’，故用问号代替）。

6.关于VARCHAR2, NVARCHAR2的其它问题（没看懂+1）

NVARCHAR2(N)，其中的N是指字符数，不是字节数。不过其最大长度是以字节为单位，即4000字节。

VARCHAR2(N)，其中的N可能是指字符数，也可能是指字节数。你可以显式地在声明的时候指定，比如VARCHAR2(10 BYTE)或者VARCHAR2(10 CHAR)，未显式指明时，则由参数NLS_LENGTH_SEMANTICS决定。需要注意的是你能成功声明VARCHAR2(4000 CHAR)并不能保证你能真的存储4000个字符，如果超过4000字节，该报错Oracle还是会报错。

7. EF BF BD

utf-8是一种变长（1 byte ~ 6 bytes）的unicode字符集编码方案。所谓编码方案即讲字符集到码点（code point）的映射方式。

在众多的utf-8码点值中，除了ascii，你还应该记住「EF BF BD」，因为它是很多编程语言以及库中的备胎，即无效的码点值在编码的时候会默认用这个码点值进行替换，即utf-8中的超级「备胎」(REPLACEMENT CHARACTER)。

1. 为什么会有无效的码点值？

无图

从上图可以知道，utf-8编码 并非连续的 。很多人会忽略这个细节。

2. 什么时候会遇到无效的utf-8码点？

当你试图把一个无效的码点值作为utf-8码点处理时，就会产生无效的码点。此时，无效的码点会被替换为「EF BF BD」，然后进行后续处理，以避免无效码点可能引起的异常。很多语言对这种处理是 自动进行 的，比如golang:

now := time.Now().Unix() // 一个无效的码点值

str := string(now) // golang是utf-8编码，会对无效码点进行替换

fmt.Printf("%X", []byte(str)) // EFBFBD，即字符「�」

3. 为什么要记住这个码点

在「字符集敏感」的环境中，如果你的数据中出现了「EF BF BD」就应该警惕了，因为你传输的数据中途很可能经过了自动替换，收到的数据未必是原始的数据。这对于你排查一些奇怪的数据交换不一致问题是很有用的，很多时候，可能是你的最后一颗救命稻草。

例如，使用utf-8编码的xml文档进行数据交换，如果看见了「�」，毫无疑问数据源有非法码点值。网页中出现了「�」，那么肯定是html文档的编码不是合规的utf-8编码文档。文本编辑器中出现了「�」，那一定是你打开这个文档的姿势不对——又选错编码了。总之，当你看见超级备胎「�」的时候，不要觉得大事不妙，不要像遇到一般乱码那样惊慌失措，你应该轻轻弹一下鼠标上的灰尘，将之打回原形。

4.utf-8 神秘符号 efbf bdef bfbd

很多时候我们在打开utf-8的时候会有 � 等很多像问号的编码出现
这时候我们打开这个文件的二进制就会发现问号对应的十六进制编码就是
efbf bdef bfbd。这个编码到底是什么呢？为什么会有这种编码呢。
这就要从各种编码格式的转换说起。而且大部分出现这种问题的就是用gbk编码的文件
用utf-8编码格式打开。当编码格式中出现utf-8无法解析的字节，那么这个字节就会被
替换成 efbf bdef bfbd 这时我们会发现文件大小也发生了改变，因为未知字节全部
变成三个未知字节。
而这时候再将其转换为gbk
锟 (0xEFBF)，斤（0xBDEF），拷（0xBFBD）
这就是锟斤拷
那么我是在什么时候发现这个问题：
python 的 print 我们一般都会把python代码设置成全局utf-8编码。
这时候如果你收到的是一个gbk的编码，但是直接打出来。就会发现出现很多问号。
这个时候直接复制粘贴再看二进制就不是真正接受的数据。因为print自动将收到的
数据进行了utf-8解码。这样无法解析的编码就变成efbf bdef bfbd。
所以在遇到乱码的时候就需要在数据的原始接受处，及第一次转码发生之前将数据用二进制写入文件留存

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

编码