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

数据库的全球化特性是数据库发展的必然结果,位于不同地区、不同国家、不用语言而使用同一数据库越来越普遍。对于不同国家或地区数据库通常会使用不同的字符集,而全球性企业也会选择使用统一编码的数据库字符集。Oracle数据库统一字符集为AL32UTF8,可以参考: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 全球化特性与字符集 264157.1

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏GreenLeaves

Oracle 多行、多列子查询

本文使用到的是oracle数据库scott方案所带的表,scott是oracle数据库自带的方案,使用前请确保其解锁 ? 一、多行子查询 多行子查询子查询是嵌入...

22670
来自专栏Linux运维学习之路

MySQL索引

索引管理 索引是什么? 索引就好比一本书的目录,它会让你更快的找到内容; 让获取的数据更有目的性,从而提高数据库检索数据的性能; 索引建立在表的列上(字段)。 ...

48250
来自专栏个人分享

Spark工程开发常用函数与方法(Scala语言)

import org.apache.spark.{SparkContext, SparkConf} import org.apache.spark.sql.{S...

17720
来自专栏逸鹏说道

快速对表的某字段赋递增的数值

假如有这张一张表,当时创建时没有用来存放递增的数值的int型字段。在使用的过程中,有这样的需求。 USE AdventureWorks2008R2;GOIF O...

22160
来自专栏大数据和云计算技术

Flink StreamSQL 原理介绍

引言 前面群里面同学说对flink感兴趣,特别邀请资深流专家张如聪给大家深入分析下Flink里面最重要部分:Flink SQL。 本文对Flink SQL深入浅...

1.5K40
来自专栏数据和云

案例分析:倾斜值传入导致 SQL 资源消耗升高

作者 | 邓秋爽:云和恩墨技术工程师,有超过七年超大型数据库专业服务经验,擅长 Oracle 数据库优化、SQL 优化和 Troubleshooting。

13540
来自专栏沃趣科技

语句效率统计视图 | 全方位认识 sys 系统库

在上一篇《统计信息查询视图|全方位认识 sys 系统库》中,我们介绍了利用sys 系统库的查询统计信息的快捷视图,本期将为大家介绍语句查询效率语句统计信息相关的...

26750
来自专栏转载gongluck的CSDN博客

第8章 基本UDP套接字编程

客户端: #include "../Gnet.h" void do_client(int udpfd, struct sockaddr* pserver_ad...

387110
来自专栏数据库新发现

字符集问题的初步探讨(三)

http://www.eygle.com/special/NLS_CHARACTER_SET_03.htm

8520
来自专栏乐沙弥的世界

PL/SQL --> 存储过程

存储过程子程序的一种类型,能够完成一些任务,作为schema对象存储于数据库。是一个有名字的PL/SQL代码块,支持接收或不接受参数

9030

扫码关注云+社区

领取腾讯云代金券