Oracle字符集转换

http://xiangqinghu1988.blog.163.com/blog/static/58822991201222231517193/

http://www.orafaq.com/wiki/NLS_LANG

 

 这几天在工作中碰到一个字符乱码的问题，发现在cmd窗口的sqlplus中直接update一个中文和使用@调用一个文件作同样更新的时候，存储的结果竟不一样。一时比较迷惑，对Oracle如何处理各个字符集的问题不是很清楚。特此通过一些资料和实验总结，系统学习一下Oracle中字符集的相关知识。

一. 字符集的基础知识：
在网络上已有不少网友对字符集进行了研究，个人觉得有几个不错的网站可以参考
http://blog.csdn.net/tianlesoftware/article/details/4915223
http://www.itstreets.com/post/34.html
http://www.oraclefans.cn/forum/showblog.jsp?rootid=3303

二. Oracle字符集的转换

以下用几个实验来学习一下Oracle对各种字符集的处理
此处涉及3个概念：数据库服务端的字符集，客户端的字符集和操作系统的字符集
实验环境中：数据库服务端的字符集是AL32UTF8

1. 客户端和服务器端的字符集一致的时候
实验一：客户端字符集也是AL32UTF8, 操作系统的字符集是GB2312
CTBLAZER@106orcl>update c_language set languagename='中国' where id = 'zh-CN';

1 row updated.

CTBLAZER@106orcl>commit ;

Commit complete.

CTBLAZER@106orcl>select dump(languagename, 1016) from c_language where id = 'zh-CN';

DUMP(LANGUAGENAME,1016)
-----------------------------------------------------------------------------------------

Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa

CTBLAZER@106orcl>

使用dump函数以16进制查看在数据库中的存储，可以看出“中国”这2个字，在数据库中的存储编码是：d6,d0,b9,fa

先建一个文件t1.txt，写上“中国”这2个字，以GB2312（codepage 为936）的格式保存。
在cmd窗口使用type命令查看，可以看到此时在cmd窗口能正确显示文本。

V:\>chcp
Active code page: 936

V:\>type e:\t1.txt
中国
V:\>

再用winhex工具查看t1.txt文本，可以看到其16进制编码为：D6，D0，B9，FA

实验二：在sqlplus环境中使用@来调用一个sql文件，文件的内容是和实验一同样的一句sql，文件格式为UTF8
服务器和客户端的字符集依然是AL32UTF8, 操作系统的codepage为936

CTBLAZER@106orcl>select dump(languagename, 1016) from c_language where id = 'zh-CN';

DUMP(LANGUAGENAME,1016)
---------------------------------------------------------------------------------------

Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd

CTBLAZER@106orcl>

同样建一个文本文件t2.txt，以UTF8的格式保存。再使用WinHex查看， 可以看到其16进制编码为：E4, B8, AD, E5, 9B, BD

通过这2个实验，可以清楚的看出，在客户端和数据库服务端的字符集一致的时候，Oracle并不进行存储转换。如在cmd窗口之中，
以操作系统默认的GB2312编码的“中国”，和以文件方式按UFT8编码格式的“中国”，都会直接存储到Oracle服务器上。

但是我们会发现，按UTF8格式编码的存储，在cmd里面查询的结果会是乱码。如下：

CTBLAZER@106orcl>host chcp
Active code page: 936

CTBLAZER@106orcl>col languagename format A30
CTBLAZER@106orcl>select * from c_language where id='zh-CN';

ID                   LANGUAGENAME
-------------------- ------------------------------
zh-CN                涓浗
CTBLAZER@106orcl>

这是因为我机器cmd窗口的codepage 默认是936，而客户端和服务器端的字符集都一样，Oracle会不做任何转换的把保存的字符编码
直接返回回来，但是数据库里面的编码是按UTF8的存储的，而cmd窗口的字符编码为GB2312，故而是显示的乱码。

我们可以将cmd窗口的codepage先改为UTF8（codepage为65001）格式的，则可以看到正常的显示“中国”这2个字了。如下：
（如何修改codepage，可以参考：http://xiangqinghu1988.blog.163.com/blog/static/58822991201222232456746/）

Active code page: 65001

CTBLAZER@106orcl>select * from c_language where id = 'zh-CN';

ID                   LANGUAGENAME
-------------------- ------------------------------
zh-CN                中国

CTBLAZER@106orcl>

因此，在Oracle没有施行字符转换的时候，即（客户端和服务器端的字符集是一致的时候），如果出现乱码，那么则是表明客户端的环境编码（如cmd窗口的编码）和Oracle数据库存储的字符编码（cmd窗口直接用sqlplus更新和调用一个UTF8文件格式的更新）不一致。如果要查看，需要修改客户端的环境编码。比如修改cmd的codepage。

2. 客户端和服务器端的字符集不一致的时候
实验三：将客户端的字符集设置为ZHS16GBK，在sqlplus里面直接更新记录如下：

V:\>set NLS_LANG=American_America.ZHS16GBK

V:\>sqlplus ctblazer@106orcl

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 15:29:43 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CTBLAZER@106orcl>Update c_language set languagename='中国' where id = 'zh-CN';

1 row updated.

CTBLAZER@106orcl>commit;

Commit complete.

CTBLAZER@106orcl>col languagename format A30
CTBLAZER@106orcl>select * from c_language where id = 'zh-CN';

ID                                       LANGUAGENAME
---------------------------------------- ------------------------------
zh-CN                                    中国

CTBLAZER@106orcl>select dump(languagename, 1016) from c_language where id = 'zh-CN';

DUMP(LANGUAGENAME,1016)
-----------------------------------------------------------------------------------------

Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd

CTBLAZER@106orcl>

结果很有意思，数据库中存储的不再是像实验一中的d6,d0,b9,fa， 而是很巧的变成了UTF8格式的存储了。如果我们开另外一个
sqlplus窗口，其客户端字符集为AL32UTF8，这时来查看一下这条记录如下，发现乱码了

V:\>sqlplus ctblazer@106orcl

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 15:36:56 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CTBLAZER@106orcl>col languagename format A30
CTBLAZER@106orcl>select * from c_language where id = 'zh-CN';

ID                   LANGUAGENAME
-------------------- ------------------------------
zh-CN                涓浗

CTBLAZER@106orcl>

如果我们把cmd的codepage设置为UTF8格式的，我们就可以看到是正常的显示了（这时还是要求客户端与服务器端的字符集一致）

Active code page: 65001
CTBLAZER@106orcl>select * from c_language where id = 'zh-CN';

ID                   LANGUAGENAME
-------------------- ------------------------------
zh-CN                中国

CTBLAZER@106orcl>

注意，这个实验的结果比较巧合，如果将sqlplus客户端的字符集不是设置ZHS16GBK，而是设为其他的字符集，那么Oracle中存储的就不一定是UTF8的编码了。
实际上，如果客户端和服务端的字符集不一致，Oracle底层会对字符编码进行转换，具体如何转换我也还不清楚，望有高手深入研究。

总的来说，在实际中如果碰到乱码情况，需要知晓Oracle客户端字符集，服务器端字符集以及操作系统字符集这几个之间的关系。
1. 如果客户端和服务器端的字符集一致，Oracle不会进行编码转换。直接按存储操作系统相关的编码格式存储。在查询的时候，也不会转换，而是直接返回数据库中存储的编码
2. 如果客户端和服务器端的字符集不一致，Oracle会在底层对存储的字符进行编码转换。在查询返回的时候，同样会转换一次。