oracle数据库字符集

Oracle字符集的基本原理

 

1. Oracle服务器字符集

oracle以哪种字符编码存储字符，可以通过以下语句查出数据库字符集的设置。

• 方法1

SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

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

• 方法2

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

2. 客户端操作系统字符集

• Windows，可以使用chcp命令获得代码页（code page）

C:\Users\zy>chcp
活动代码页: 936

到微软的官方文档《National Language Support (NLS) API Reference》找到其对应的字符集

• Linux，字符集在/etc/sysconfig/i18n设置：

LANG="zh_CN.GB2312" (指定当前操作系统的字符集)
SUPPORTED="zh_CN.GB2312"(指定当前操作系统支持的字符集)
SYSFONT="lat0-sun16"(指定当前操作系统的字体)

 3.客户端NLS_LANG参数：该参数用于向Oracle指示客户端操作系统字符集

它的格式如下: NLS_LANG = language_territory.charset

它有三个组成部分(语言、地域和字符集)，每个成分控制了NLS子集的特性。

其中:

1. Language： 指定服务器消息的语言， 影响提示信息是中文还是英文
2. Territory： 指定服务器的日期和数字格式，
3. Charset：  指定字符集。

如:

• AMERICAN _ AMERICA. ZHS16GBK
• AMERICAN_AMERICA.AL32UTF8
• SIMPLIFIED CHINESE_CHINA.ZHS16GBK
• SIMPLIFIED CHINESE_CHINA.AL32UTF8

 

4.转换规则

1.设置客户端的NLS_LANG为客户端操作系统的字符集
2.如果数据库字符集等于NLS_LANG，数据库和客户端传输字符时不作任何转换
3.如果它们俩不等，则需要在不同字符集间转换，只有客户端操作系统字符集是数据库字符集子集的基础上才能正确转换，否则会出现乱码。

 

实验

数据库字符集：SIMPLIFIED CHINESE_CHINA.AL32UTF8

创建测试表

SQL>  create table test(id number,var varchar2(30));

• session1,session1的NLS_LANG设为和数据库字符集一样（即AL32UTF8）

C:\Users\zy>set nls_lang=Simplified Chinese_China.AL32UTF8       

C:\Users\zy>set nls_lang=Simplified Chinese_China.AL32UTF8

C:\Users\zy>echo %NLS_LANG%
Simplified Chinese_China.AL32UTF8

C:\Users\zy>sqlplus scott/tiger@zydev

SQL*Plus: Release 11.2.0.3.0 Production on 鏄熸湡鏃?7鏈?3 21:38:10 2016

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


杩炴帴鍒?
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into test values(1,'中国');

宸插垱寤?1 琛屻€
commit；

• session2,session2的NLS_LANG设为和客户端操作系统一样（即ZHS16GBK）：

C:\Users\zy>set nls_lang=Simplified Chinese_China.ZHS16GBK     

C:\Users\zy>sqlplus scott/tiger@zydev

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 3 21:41:33 2016

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc
With the Partitioning, OLAP, Data Mining and Real Application Testing op

SQL> insert into test values(2,'中国');

已创建 1 行。

SQL> commit;

执行查询

在session1查询

SQL> select * from test;

        ID VAR
---------- ------------------------------
         2 涓浗
         1 中国

在session2查询

SQL> select * from test;

        ID VAR
---------- ------------------------------------------------------------
         2 中国
         1 ？??

上面例子看起来很诡异，session1和2都能正常显示自己插入的字符串，又都不能正常显示对方插入的字符串。为了弄清楚，我们首先得知道数据库里对这两个字符串是怎么存储的。我们可以使用dump函数获得字符在数据库的编码:

SQL> select id,dump(var,1016) from test;

        ID
----------
DUMP(VAR,1016)
------------------------------------------------------

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

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

根据AL32UTF8的编码，“中国”两字的正确编码为（都为3个字节）：
中--e4,b8,ad
国--e5,9b,bd
因此session 1插入的字符串在数据库中的编码是错误的，session 2正确。这也是为什么一定要设置NLS_LANG为客户端操作系统的字符集。
但是根据上面实验我们可以知道，数据库中存储正确，并不代表客户端能正常显示；同样地，即时数据库没有正确存储，有时候客户端也能够正常显示，这又是为什么呢？别急，请听我慢慢道来：

场景1：session 1插入，session 1查询，在数据库中存储错误，但显示正确。
插入过程：
”中国“两字在客户端操作系统字符集ZHS16GBK中的编码是”d6,d0,b9,fa"，由于NLS_LANG和数据库字符集相同，数据库端对客户端传过来的字符编码不进行任何转换直接存入数据库，因此数据库中存储的编码也是“d6,d0,b9,fa”，
读取过程：
数据库端读取的编码是“d6,d0,b9,fa”，由于NLS_LANG和数据库字符集相同，客户端对数据库端传过来的字符编码不进行任何转换直接显示，编码”d6,d0,b9,fa“在客户端操作系统字符集ZHS16GBK对应的汉字为“中国”。

从以上分析可知，虽然读取时正确的，但那是因为负负得正，实际上数据库中存储是错误的，因此要特别小心这种情况，在生成库中要避免。其实只要对它进行length操作就能轻易揭开它的假面具：

复制代码 代码如下:

Session_1>select length(var) from test where id=1;
LENGTH(VAR)
-----------
          3

得出的长度居然为3！实际的长度只是2，这会带来很多麻烦。

场景2：session 1插入，session 2查询，在数据库中存储错误，显示也错误。
插入过程和场景1一样，这里就不再累述。
读取过程：
数 据库端读取的编码是“d6,d0,b9,fa”，由于NLS_LANG和数据库字符集不同，客户端对数据库端传过来的字符编码进行转换，数据库端字符集 AL32UTF8里编为“d6,d0,b9,fa”无法在客户端操作系统字符集ZHS16GBK里找到对应的编码，所以只好用?代替。

场景3：session 2插入，session 1查询，在数据库中存储正确，但显示错误。
插入过程：
” 中国“两字在客户端操作系统字符集ZHS16GBK中的编码是”d6,d0,b9,fa"，由于NLS_LANG和数据库字符集不同，Oracle会进行 字符编码转换，也就是将字符集ZHS16GBK里“中国”的编码“d6,d0,b9,fa"转换为字符集"AL32UTF8"里”中国“的编 码”e4,b8,ad,e5,9b,bd“。
读取过程：
数据库端读取的编码 是”e4,b8,ad,e5,9b,bd“，由于NLS_LANG和数据库字符集相同，客户端对数据库端传过来的字符编码不进行任何转换直接显示，编 码”e4,b8,ad,e5,9b,bd“在客户端操作系统字符集ZHS16GBK对应的汉字为“涓   浗”（原本2个字符，现在变成了3个字符，因为ZHS16GBK的汉字以2个字节编码）。

场景4：session 2插入，session 2查询，在数据库中存储正确，显示也正确。
插入过程和场景3类似。
读取过程：
数 据库端读取的编码是”e4,b8,ad,e5,9b,bd“，由于NLS_LANG和数据库字符集不同，客户端对数据库端传过来的字符编码进行转换，数据 库端字符集AL32UTF8里”中国“两字的编码”e4,b8,ad,e5,9b,bd“转换成客户端操作系统字符集ZHS16GBK里“中国”两字的编 码“d6,d0,b9,fa"，并正常显示。
这种情况虽然经过了两次转换，都确实最正确、最推荐的方式。

结论：NLS_LANG只和客户端操作系统的字符集相关，如果客户端操作系统的字符集和数据库字符集间无法正确转换，则应该首先改变客户端终端的字符集，而不是简单地把NLS_LANG设为和数据库字符集一样。

 

 常见乱码解决：

登录数据库显示乱码

原因是数据库的客户端和服务端的字符集不同

查出服务端的字符集

SQL> select userenv('language') from dual;
 
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8

客户端字符集的构成与设定。客户端的字符集是由当前用户的环境变量NLS_LANG设定的。

故在环境变量中设定与服务器端相同的字符集即可解决乱码

vim ~/.bash_profile
增加：

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"

source ~/.bash_profile

乱码得到解决

如果是win系统就要修改注册表：

修改路径如下

开始---运行---regedit---确定:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExx\NLS_LANG

原来的设置为SIMPLIFIED CHINESE_CHINA.ZHS16GBK

修改为AMERICAN_AMERICA.UTF8

修改后直接保存即可

如果修改成 SIMPLIFIED CHINESE_CHINA.AL32UTF8，我试验的时候DOS上还是中文乱码。