Oracle字符集与字符类型存储空间占用

淩亂°似流年 2022-07-24 06:04 285阅读 0赞

> 数据库的全球化特性是数据库发展的必然结果，位于不同地区、不同国家、不用语言而使用同一数据库越来越普遍。对于不同国家或地区数据库通常会使用不同的字符集，而全球性企业也会选择使用统一编码的数据库字符集。Oracle数据库统一字符集为AL32UTF8，可以参考：[Oracle 全球化特性与字符集][Oracle]。对于不同的字符集容易出现转换乱码，同时不同字符集也影响存储空间的占用。 如本文下面的描述。

## 一、字符集环境变量对数据库的影响 ##

[oracle@java_1 ~]$ env |grep LANG
    LANG=zh_CN.UTF-8    ###OS环境变量
    
    SQL> select * from v$version where rownum<2;  --当前数据库版本
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    
    SQL> col value format a40
    SQL> select * from nls_database_parameters where parameter like '%CHARACT%';
    
    PARAMETER                      VALUE  
    ------------------------------ ----------------------------------------
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               AL32UTF8    ---数据库字符集
    NLS_NCHAR_CHARACTERSET         AL16UTF16   ---国家字符集
    
    SQL> select dump('云') from dual;          ---当前dump出来使用了9个字节
    
    DUMP('???')
    -------------------------------------------------
    Typ=96 Len=9: 239,191,189,239,191,189,239,191,189
    
    [oracle@java_1 ~]$ env |grep LANG
    LANG=en_US.UTF-8        ###将OS环境修改为英语与美国地区 
    
    SQL> select dump('云') from dual;  
    
    DUMP('???')   ---当前dump出来依旧使用了9个字节，也即是证明了UTF-8为统一编码，与语言地域无关
    -------------------------------------------------
    Typ=96 Len=9: 239,191,189,239,191,189,239,191,189
    
    ###下面设置环境变量NLS_LANG，如下
    [oracle@java_1 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
    [oracle@java_1 ~]$ env |grep LANG
    NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8
    LANG=zh_CN.UTF-8
    
    SQL> select dump('云') from dual;  ---当前dump出来使用了3个字节
    
    DUMP('云')            
    -------------------------
    Typ=96 Len=3: 228,186,145
    
    ###下面设置环境变量NLS_LANG，字符集使用ZHS16GBK
    [oracle@java_1 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
    [oracle@java_1 ~]$ env |grep LANG
    NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
    LANG=zh_CN.UTF-8
    
    SQL> select dump('云') from dual;  --此时报错
    ERROR:
    ORA-01756: ?
    
    ###下面将其设置为繁体字符集，报ORA-12705，如下：
    [oracle@java_2 ~]$ export NLS_LANG="CHINESE_TAIWAN.ZHT16MSWIN950"
    [oracle@java_2 ~]$ sqlplus robin/xxx
    
    SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 10:21:37 2016
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    ERROR:
    ORA-12705: Cannot access NLS data files or invalid environment specified

## 二、字符集对字符类型存储空间的影响 ##

### 1、数据库字符集为AL32UTF8的情形 ###

[oracle@java_1 ~]$ unset NLS_LANG   ###不设置环境变量NLS_LANG
    [oracle@java_1 ~]$ env|grep LANG
    LANG=zh_CN.UTF-8
    
    SQL> create table tb_length(id int,col1 varchar2(20), col2 nvarchar2(20));
    
    SQL> insert into tb_length values(1,'云创','云创');
    
    SQL> commit;
    
    SQL> select vsize(col1),vsize(col2) from tb_length;
    
    VSIZE(COL1) VSIZE(COL2)   --如结果，对于varchar2，一个汉字使用了9个字节
    ----------- -----------   --对于nvarchar2，一个汉字使用了6个字节
             18          12
    
    ###下面设置NLS_LANG
    [oracle@java_1 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
    [oracle@java_1 ~]$ env|grep LANG
    NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8
    LANG=zh_CN.UTF-8
    
    SQL> insert into tb_length values(2,'数据','数据');
    
    SQL> commit;
    
    SQL> select vsize(col1),vsize(col2) from tb_length where id=2;
    
    VSIZE(COL1) VSIZE(COL2)           --如结果，对于varchar2，一个汉字使用了3个字节  
    ----------- -----------           --对于nvarchar2，一个汉字使用了2个字节
              6           4
    
    SQL> select vsize(col1),vsize(col2) from tb_length;
    
    VSIZE(COL1) VSIZE(COL2)
    ----------- -----------
             18          12
              6           4

### 2、数据库字符集为ZHS16GBK的情形(以下为不同环境及版本的数据库) ###

SQL> select * from v$version where rownum<2;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    
    SQL> select userenv('language') from dual;
    
    USERENV('LANGUAGE')
    ----------------------------------------------------
    AMERICAN_AMERICA.ZHS16GBK
    
    SQL> select * from nls_database_parameters where parameter like '%CHARACT%';
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               ZHS16GBK
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    
    SQL> ho env|grep LANG
    LANG=zh_CN.UTF-8
    
    SQL> create table tb_length(id int,col1 varchar2(20), col2 nvarchar2(20));
    
    SQL> insert into tb_length values(1,'云创','云创');
    
    SQL> select vsize(col1),vsize(col2) from tb_length;
    
    VSIZE(COL1) VSIZE(COL2)         --如结果，对于varchar2，一个汉字使用了3个字节
    ----------- -----------         --对于nvarchar2，一个汉字使用了6个字节
              6          12
    
    ###下面设置NLS_LANG
    [oracle@java_2 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
    [oracle@java_2 ~]$ env |grep env
    _=/bin/env
    [oracle@java_2 ~]$ env |grep LANG
    NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
    LANG=zh_CN.UTF-8
    
    SQL> insert into tb_length values(2,'数据','数据');
    
    SQL> commit;
    
    SQL> select vsize(col1),vsize(col2) from tb_length;
    
    VSIZE(COL1) VSIZE(COL2)
    ----------- -----------
              6          12   --Author : Leshami  Blog: http//blog.csdn.net/leshami
              6           6   --设定NLS_LANG之后，varchar2与nvarchar2占用空间一致

### 3、不同varchar2长度定义对存储空间的影响 ###

SQL> create table tb_length2(col1 varchar2(20),col2 varchar2(30));
    
    SQL> insert into tb_length2 values('云创数据','云创数据');
    
    SQL> select vsize(col1),vsize(col2) from tb_length2;
    
    VSIZE(COL1) VSIZE(COL2)
    ----------- -----------     --存储空间都为12个字节，而不论定义长度的多少
             12          12

## 三、常用的NLS\_LANG设置 ##

Commonly Used Values for NLS_LANG 
    Table C-1 lists commonly used NLS_LANG values for various operating system locales: 
    Table C-1 NLS_LANG Parameter Values 
    Operating System Locale          NLS_LANG Value 
    ---------------------------    -----------------------------------------
    Chinese (PRC)                    SIMPLIFIED CHINESE_CHINA.ZHS16GBK
    Chinese (Taiwan)                 TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
    English (United Kingdom)       ENGLISH_UNITED KINGDOM.WE8MSWIN1252
    English (United States)        AMERICAN_AMERICA.WE8MSWIN1252
    French  (Canada)               CANADIAN FRENCH_CANADA.WE8MSWIN1252
    French  (France)                 FRENCH_FRANCE.WE8MSWIN1252
    German  (Germany)                GERMAN_GERMANY.WE8MSWIN1252
    Japanese                         JAPANESE_JAPAN.JA16SJIS
    Korean                         KOREAN_KOREA.KO16MSWIN949
    Russian                        RUSSIAN_CIS.CL8MSWIN1251

## 四、小结 ##

> 1、注意LANG与NLS\_LANG，前者是设置操作系统级别环境语言及字符集变量，后者是针对数据库级别  
> 2、在未设置NLS\_LANG的情形下，导致被存储的字符数量尺寸过大  
> 3、NLS\_CHARACTERSET为数据库字符集，NLS\_NCHAR\_CHARACTERSET为国家字符集  
> 4、当数据库字符集为AL32UTF8的情形下，nvarchar2存储尺寸小于varchar2存储尺寸，建议使用nvarchar2存储国家字符集。  
> 5、当数据库字符集为ZHS16GBK的情形下，varchar2与nvarchar2占用空间一致。用那种类型都无所谓。  
> 6、varchar2(20)与varchar2(50)在存储相同内容时，所占用的空间一样。仅仅用于限制列长度。  
> 7、参考：[Oracle 全球化特性与字符集][Oracle] 264157.1

[Oracle]: http://blog.csdn.net/leshami/article/details/6030398