【character】oracle字符集问题概总

【前言】使用oracle的过程中，出现汉字的时候总是出现乱码现象，疑惑好久。今天决心把字符集的问题彻底搞清楚。下面将通过迭代的方法，由字符集的基本概念逐层深入并过渡到oracle问题上。

一. 字符集

1.1 常用的字符集：

单字节编码分为：7位编码和8位编码

US7ASCII：单字节7位编码，定义了128个符号，不支持中文。

WE8ISO8859P1：单字节8位编码字符集。

多字节编码分为固定长度编码和变长编码

ZHS16GBK：双字节字符集ZHS16GBK中，编码长度固定，一个中文汉字是一个字符，一个英文字母也是一个字符，所以他们俩占的存储空间一样大，都是两个字节！ 

GB18030：收录了所有汉字，本标准收录的字符分别以单字节、双字节和四字节编码。

UNICODE:一种集合了所有字符的集合，为变长长度。

UTF-8：UNICODE的一种实现方式，在UNICODE上做了改动，字符分别以1-4个字节编码。

1.2 UTF-8与UNICODE的深入区别

Unicode只是一个符号集，它只规定了符号的二进制代码，却没有规定这个二进制代码应该如何存储。比如，汉字“严”的unicode是十六进制数4E25，转换成二进制数足足有15位(100111000100101)，也就是说这个符号的表示至少需要2个字节。表示其他更大的符号，可能需要3个字节或者4个字节，甚至更多。

这里就有两个严重的问题：

第一个问题：如何才能区别unicode和ascii?计算机怎么知道三个字节表示一个符号，而不是分别表示三个符号呢？

第二个问题：我们已经知道，英文字母只用一个字节表示就够了，如果unicode统一规定，每个符号用三个或四个字节表示，那么每个英文字母前都必然有二到三个字节是0，这对于存储来说是极大的浪费，文本文件的大小会因此大出二三倍，这是无法接受的

为了解决这两个问题，UTF-8就被创造出来了。

UTF-8就是在互联网上使用最广的一种unicode的实现方式。其他实现方式还包括UTF-16和UTF-32，不过在互联网上基本不用。重复一遍，这里的关系是，UTF-8是Unicode的实现方式之一。UTF-8最大的一个特点，就是它是一种变长的编码方式。它可以使用1~4个字节表示一个符号，根据不同的符号而变化字节长度。

UTF-8的编码规则很简单，只有二条：

1)对于单字节的符号，字节的第一位设为0，后面7位为这个符号的unicode码。因此对于英语字母，UTF-8编码和ASCII码是相同的（一个字节编码）。

2)对于n字节的符号(n>1)，第一个字节的前n位都设为1，第n+1位设为0，后面字节的前两位一律设为10。剩下的没有提及的二进制位，全部为这个符号的unicode码。

下表总结了编码规则，字母x表示可用编码的位。

Unicode符号范围| UTF-8编码方式

(十六进制) | (二进制)

--------------------+---------------------------------------------

0000 0000-0000 007F | 0xxxxxxx

0000 0080-0000 07FF | 110xxxxx 10xxxxxx

0000 0800-0000 FFFF | 1110xxxx 10xxxxxx 10xxxxxx

0001 0000-0010 FFFF | 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx

下面，还是以汉字“严”为例，演示如何实现UTF-8编码。

已知“严”的unicode是4E25(100111000100101)，根据上表，可以发现4E25处在第三行的范围内(0000 0800-0000 FFFF)，因此“严”的UTF-8编码需要三个字节，即格式是“1110xxxx 10xxxxxx 10xxxxxx”。然后，从“严”的最后一个二进制位开始，依次从后向前填入格式中的x，多出的位补0。这样就得到了，“严”的UTF-8编码是“11100100 10111000 10100101”，转换成十六进制就是E4B8A5。

1.3 接下来我们直接、真实的来感受一下字符与编码之间的对应关系

在Windows平台下，有一个最简单的转化方法，就是使用内置的记事本小程序Notepad.exe。打开文件后，点击“文件”菜单中的“另存为”命令，会跳出一个对话框，在最底部有一个“编码”的下拉条。

里面有四个选项：ANSI，Unicode，Unicode big endian和UTF-8。

1)ANSI是默认的编码方式。对于英文文件是ASCII编码，对于简体中文文件是GB2312编码(只针对Windows简体中文版，如果是繁体中文版会采用Big5码)。

2)Unicode编码指的是UCS-2编码方式，即直接用两个字节存入字符的Unicode码。这个选项用的little endian格式。

3)Unicode big endian编码与上一个选项相对应。我在下一节会解释little endian和big endian的涵义。

4)UTF-8编码，也就是上一节谈到的编码方法。

选择完”编码方式“后，点击”保存“按钮，文件的编码方式就立刻转换好了。

然后，用文本编辑软件UltraEdit（很容易下载到的一个文本编辑软件）中的”十六进制功能“，观察该文件的内部编码方式。

1)ANSI：文件的编码就是两个字节“D1 CF”，这正是“严”的GB2312编码，这也暗示GB2312是采用大头方式存储的。

2)Unicode：编码是四个字节“FF FE 25 4E”，其中“FF FE”表明是小头方式存储，真正的编码是4E25。

3)Unicode big endian：编码是四个字节“FE FF 4E 25”，其中“FE FF”表明是大头方式存储。

4)UTF-8：编码是六个字节“EF BB BF E4 B8 A5”，前三个字节“EF BB BF”表示这是UTF-8编码，后三个“E4B8A5”就是“严”的具体编码，它的存储顺序与编码顺序是一致的。

后面部分说了这么多，只是想对字符集有一个更立体的认识，对后续内容的也更容易理解。

1.4 乱码问题

将以UTF-8编码的“严”字，转换为US7ASCII编码，因为US7ASCII编码字符集中没有“严”这个字符。于是将“严”转换为目标字符集（US7ASCII）中的一个特殊字符（称为“替换字符”）。比如在这里我们可以将？作为替换字符，所以“严”就转换为了“？”，出现了信息的丢失。这里只是概括的说明一下，后续会针对实际情况进行分析。

二  ORACLE字符集问题

关于oracle应用中的字符集问题，涉及到三个方面：①操作系统的字符集②登陆客户端的字符集③数据库的字符集。下面将依次介绍各自字符集的设定所起到的作用。然后通过实验来进行模拟验证。

2.1 文字介绍

操作系统字符集：当你在应用程序中（例如sql*plus）输入数据，此时数据的编码格式是由操作系统的字符集决定。

数据库字符集：我们在创建数据库时，需要考虑的一个问题就是选择什么字符集与国家字符集（通过create database中的CHARACTER SET与NATIONAL CHARACTER SET子句指定）。考虑这个问题，我们必须要清楚数据库中都需要存储什么数据，如果只需要存储英文信息，那么选择US7ASCII作为字符集就可以；但是如果要存储中文，那么我们就需要选择能够支持中文的字符集（如ZHS16GBK）；如果需要存储多国语言文字，那就要选择UTF8了。数据库字符集的确定，实际上说明这个数据库所能处理的字符的集合及其编码方式，由于字符集选定后再进行更改会有诸多的限制，所以在数据库创建时一定要考虑清楚后再选择。而我们许多朋友在创建数据库时，不考虑清楚，往往选择一个默认的字符集，如WE8ISO8859P1或US7ASCII，而这两个字符集都没有汉字编码，所以用这种字符集存储汉字信息从原则上说就是错误的。虽然在有些时候选用这种字符集好象也能正常使用，但它会给数据库的使用与维护带来一系列的麻烦，在后面的迭代过程中我们将深入分析。

客户端字符集：仍以sql*plus为例，通过export nls_lang(linux)/set nls_lang(WIN)来设置客户端字符集。NLS_LANG由以下部分组成：NLS_LANG=<Language>_<Territory>.<Clients Characterset>，其中第三部分<Clients Characterset>的本意就是用来指明客户端操作系统缺省使用的字符集。所以按正规的用法，NLS_LANG应该按照客户端机器的实际情况进行配置，尤其对于字符集一项更是如此，这样Oracle就能够在最大程度上实现数据库字符集与客户端字符集的自动转换。也就是说客户端NLS_LANG在正确配置的情况下，反映了输入字符的编码格式。如果此时，NLS_LANG设置的字符集与oracle字符集不一致，那么数据库再有客户端向oracle server传输过程中会按照oracle字符集进行转换。如果一致，就不需要转换，直接传输到oracle存储起来。从数据库读取数据时是逆过程。

那有意思的事情就来了，如果客户端的NLS_LANG设置不能正确的反映操作系统的字符集，那会怎么办，你说会怎么办？假如WINDOWS的字符集UTF-8，客户端的NLS_LANG设置的字符集为US7ASCII,数据库的字符集是US7ASCII。那在SQL*PLUS里输入的数据按照UTF-8编码，数据库童鞋一看SQL*PLUS里的NLS_LANG设置的字符集和自己的字符集一样，数据库就愚蠢的认为客户端SQL*PLUS与ORACLE之间不需要数据的编码转换，并认为操作系统的字符集也是US7ASCII。于是，数据库便将UTF-8编码的数据存储起来，并以为存的是US7ACII编码的数据。读取数据时进行逆过程。当然，如果在当前这样的环境中，虽然US7ASCII编码不支持汉字，但依然没有问题，数据的存入取出始终没有真正涉及到编码的转换，所以不会出现乱码。

如果，此时我们将客户端SQL*PLUS的NLS_LANG设置的字符集转为UTF-8。在读取数据时，ORACLE看到客户端设置的字符集与自身字符集不一致，并进行转码。此时，数据库会将存储的UTF-8编码当做是US7ASCII编码，然后按照算饭去转换为UTF-8编码。你说能不出错吗！有点绕，如果没看懂，没关系，看下面的实验。

2.2 实验模拟

实验结果分析一

设置客户端字符集为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

----------

东北

这一部分的实验数据的存取与显示都正确，好象没什么问题，但实际上却隐藏着很大的隐患。

首先，要将汉字存入数据库，而将数据库字符集设置为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”的情况，他得出的结论是“如果的是在Oracle9I中，我们发现对于这种情况，不论怎样处理，这个导出文件都无法正确导入到Oracle9i数据库中”、“对于这种情况，我们可以通过使用Oracle8i的导出工具，设置导出字符集为US7ASCII，导出后修改第二、三字符，修改0001为0354,这样就可以将US7ASCII字符集的数据正确导入到ZHS16GBK的数据库中”。我想对于这些结论，这样理解可能更合适一些：由于ZHS16GBK字符集是US7ASCII的超级，所以如果按正常操作，这种转换应该没有问题；但出现问题的本质是我们让本应只存储英文字符的US7ASCII数据库，非常规地存储了中文信息，那么在转化过程中出现错误或麻烦就没什么奇怪的了，不出麻烦倒是有些奇怪了。

所以说要避免这种情况，就是要在建立数据库时选择合适的字符集，不让标签（数据库的字符集设置）与实际（数据库中实际存储的信息）不符的情况发生。

 

实验结果分析二

更改客户端字符集为ZHS16GBK

D:\>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

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”。

 

实验结果分析三

用ZHS16GBK插入数据

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

1 row created.

SQL> SELECT * FROM TEST;

R1

--------------------

6+11

??

当客户端字符集设置为ZHS16GBK后向数据库插入“东北”，Oracle检查发现数据库设置的字符集为US7ASCII与客户端不一致，需要进行转换，但字符集ZHS16GBK中的“东北”两字在US7ASCII中没有对应的字符，所以Oracle用统一的“替换字符”插入数据库，在这里为“？”，编码为63（00111111），这时，输入的信息实际上已经丢失，不管字符集设置如何改变（如下面引用的实验结果），第二行SELECT出来的结果也都是两个“？”号（注意是2个，而不是4个）。

更改客户端字符集为US7ASCII

D:\>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII

D:\>SQLPLUS "/ AS SYSDBA"

SQL> SELECT * FROM TEST;

R1

----------

东北

??

无法显示用ZHS16GBK插入的字符集，但可以显示用US7ASCII插入的字符集

更改服务器字符集为ZHS16GBK

SQL> update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';

1 row updated.

更改客户端字符集为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';”来修改数据库字符集是非常规作法，很可能引起问题.

如果次方法不成功，可以参照这篇文章的修改方法:

http://space.itpub.net/27425054/viewspace-767861

 

实验结果分析四

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

1 row created.

SQL> SELECT * FROM TEST;

R1

--------------------

东北

??

东北

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

更改客户端字符集为US7ASCII[/COLOR]

D:\>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII

D:\>SQLPLUS "/ AS SYSDBA"

SQL> SELECT * FROM TEST;

R1

----------

??

??

??

无法显示数据

疑问：第一行数据是用US7ASCII环境插入的，为何无法正常显示？

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

 

实验结果分析五

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

1 row created.

更改客户端字符集为ZHS16GBK

D:\>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK[/COLOR]

D:\>SQLPLUS "/ AS SYSDBA"

SQL> SELECT * FROM TEST;

R1

--------------------

东北

??

东北

6+11

无法显示用US7ASCII插入的字符集，但可以显示用ZHS16GBK插入的字符

疑问：US7ASCII为ZHS16GBK的子集，为何在US7ASCII环境下插入的数据无法显示？

在客户端字符集设置为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设置）。

上面的实验操作，客户端在windows上安装。基本已经将问题说清楚。下面我又在linux上的sqlplu从另一个角度说明了上述的观点。如果已经理解字符集的问题，下面的内便不需要看了。

===========================================================================================================================

接下来，我们以汉字”郭”进行分析。

实验结果分析一

系统字符集

[oracle@ballontt~]$ echo $LANG

zh_CN.UTF-8

数据库字符集

SYS@EMREP> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.US7ASCII

SQL*PLUS字符集

Sql*plus与数据库装在同一台机器上，初始安装后，其默认NLS_LANG设置的字符集与ORACLE相同，所以同为US7ASCII（我这里就是初始安装，后续没有对oracle字符集进行修改）。

查询

SYS@EMREP> select dump('郭',1010) from dual;

DUMP('郭',1010)

-----------------------------------------------

Typ=96 Len=3 CharacterSet=US7ASCII: 233,131,173

【dump函数解析】dump函数将存储在数据库中的编码显示出来。

TYPE=96表示“郭“为字符

Len=3表示”郭”字存储时占用3个字节

CharacterSet=US7ASCII以US7ASCII编码存储（实际这里是用了UTF-8的编码存储）

233,131,173：表示数据库下面的硬盘上每个字节上实际存储的值（10进制）

（Dump函数内的参数是数字时，研究起来就是另一个问题了

http://www.eygle.com/archives/2005/12/how_oracle_stor.html）

 

为了验证233,131,173是UTF-8字符集中“郭“字的编码，而不是US7ASCII字符集中的编码。我使用了上述描述的方法，在记事本中输入”郭”,然后另存为-----UTF-8格式保存----用ULtraEdit查看十六进制编码为：

EF BB BF E9 83 AD,其中EF BB BF表示为UTF-8编码，实际存储的编码为E9 83 AD，转换为10进制，刚好是233 131 173。很遗憾，此时数据库却以为自己存储的是US7ASCII编码。

 

使用ascii函数来查看

SYS@ballontt> select ascii('郭') from dual;

ASCII('郭')

------------

  233

因为在US7ASCII的字符集是单字节编码，ascii函数再去“郭”字的编码时，只取用了第一个字节的内容：233

如果在可以支持中文的字符集上进行如上操作，则会完全显示出“郭”字的编码。

 

实验结果分析二

将Linux操作系统字符集改为GB18030(一种支持中文的双字节字符集)

[root@ballontt ~]# vi /etc/sysconfig/i18n

LANG="zh_CN.GB18030"

[root@ballontt~]#REBOOT

数据库字符集

SYS@ballontt> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.US7ASCII

查询

SYS@ballontt> select dump('郭',1010) from dual;

DUMP('郭',1010)

-----------------------------------------------

Typ=96 Len=2 CharacterSet=US7ASCII: 185,249

可以看到存储的编码变了，我们有理由猜测185,249是GB18030字符集中”郭”字的编码。

由于win上的记事本不支持GB18030编码保存，所以我不得不利用了另一个工具来查看“郭”字在GB18030字符集的编码，却好也是185,249。

【工具连接如下：】

http://space.itpub.net/?uid-27425054-action-viewspace-itemid-768034

=============================================================================================================

有关字符集的几个视图：

查看oracle数据库的编码

SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

PARAMETER            VALUE
------------------------------           ------------
NLS_CHARACTERSET     AL32UTF8

这其来源于props$，这是表示数据库的字符集。

oracle客户端编码
SQL> select * from nls_instance_parameters where parameter='NLS_LANGUAGE';（NLS_LANGUAGE,此参数是用来确定语言的显示风格的，和字符集没有关系）
PARAMETER                     VALUE
--------------------           ------------------
NLS_LANGUAGE        SIMPLIFIED CHINESE
其来源于v$parameter，表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表会话字符集环境

select * from nls_session_parameters where parameter='NLS_LANGUAGE';

其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
如果export NLS_LANG='SIMPLIFIED CHINESE'，查询该视图，则NLS_LANGUAGE显示为SIMPLIFIED CHINESE

 

参考文献

[1]ITPUB论坛上的实验http://www.itpub.net/thread-276524-1-1.html

[2]其它网络资料查找

同时，这里贴出关于DAVE的精彩解读：http://blog.csdn.net/tianlesoftware/article/details/4915223

ballontt

2013/8/8

---The End---

如需转载，请标明出处和链接，谢谢！