Oracle的数据类型：char/varchar2

char

http://blog.csdn.net/NinGoo/archive/2006/12/16/1445922.aspx

char类型保存定长字符串，最小长度为1字节(或字符数)，最长2000字节(或字符数)，如果不指定，则默认为1。

SQL> create table t(name char);

表已创建。

SQL> desc t;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME CHAR(1)

SQL> create table t(name char(2001));
create table t(name char(2001))
*
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长

如果插入的字符串长度不足，会以空格填充。如果超过长度，则会报错。

SQL> insert into t values('aa');
insert into t values('aa')
*
第 1 行出现错误:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (实际值: 2, 最大值: 1)

下面是例子展示了关于char类型的插入后续空格值的问题：
SQL> create table t(name char(20));

表已创建。

SQL> insert into t values('a');

已创建 1 行。

SQL> insert into t values('a ');

已创建 1 行。

SQL> select '"'||name||'"' from t where name='a';

'"'||NAME||'"'
----------------------
"a "
"a "

SQL> select '"'||name||'"' from t where name='a ';

'"'||NAME||'"'
----------------------
"a "
"a "

SQL> insert into t values(' a');

已创建 1 行。

SQL> select '"'||name||'"' from t where name='a';

'"'||NAME||'"'
----------------------
"a "
"a "

SQL> select '"'||name||'"' from t where name=' a';

'"'||NAME||'"'
----------------------
" a "

可以看到，如果插入的字符串最后带有空格，在查询的时候是会将右边的空格删除后再来比较的，这一点在开发时需要注意。

varchar2和varchar

varchar2和varchar是同义词，都是指变长字符串类型。但是由于某种原因，varchar可能在后续版本中改变varchar的意思还是怎么的，oracle建议使用varchar2。

在使用varchar2类型时必须指定其长度(1~2000字节或字符数)，不能像char一样省略，虽然concepts文档上说其默认为1。这里不知道oracle为什么会这样实现，我认为这应该算是一个缺陷。

SQL> drop table t;

表已删除。

SQL> create table t(name varchar);
create table t(name varchar)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

SQL> create table t(name varchar2);
create table t(name varchar2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

由于varchar2不会在短于指定长度的字符串后面补齐空格，所以char类型中的后续空格问题不会在varchar2类型中发生。

SQL> create table t(name varchar2(20));

表已创建。

SQL> insert into t values('a');

已创建 1 行。

SQL> insert into t values('a ');

已创建 1 行。

SQL> select '"'||name||'"' from t where name='a';

'"'||NAME||'"'
----------------------
"a"

SQL> select '"'||name||'"' from t where name='a ';

'"'||NAME||'"'
----------------------
"a "

关于字符型数据的长度问题

前面在说到char/varchar2类型时，指定其长度可能是1字节或者1个字符数。在不同的编码方式中，一个字符可能占1字节，2字节，3字节甚至4字节。

SQL> select name,value$ from props$ where name='NLS_CHARACTERSET';

NAME VALUE$
------------------------------ ------------------------------
NLS_CHARACTERSET ZHS16GBK

SQL> create table t(name char(1 byte));

表已创建。

SQL> insert into t values('测');
insert into t values('测')
*
第 1 行出现错误:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (实际值: 2, 最大值: 1)

SQL> create table t(name char(1 char));

表已创建。

SQL> insert into t values('测');

已创建 1 行。

SQL> create table t(name char(1));

表已创建。

SQL> insert into t values('测');
insert into t values('测')
*
第 1 行出现错误:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (实际值: 2, 最大值: 1)

可以看到，由于一个汉字占两个byte，如果char/varchar2的长度是按byte计算的，那么就无法插入，如果是按字符数计算的，则可以插入。而在默认情况下，oracle是按byte计算长度的，这是由于参数nls_length_semantics的默认值是byte。

SQL> show parameter nls_length

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE

在数据字典中，oracle还是会将按字符数计算的长度，按照系统当前的字符集选择，转换成按字节计算的长度。由于我目前的测试系统中使用的ZHS16GBK字符集是定长字符集，每个字符占两个字节，所以这里data length为2。在一些变长字符集中，比如UTF8，则是4（UTF8中一个字符最长占4个字节）
SQL> drop table t;

表已删除。

SQL> create table t(name char(1 char));

表已创建。

SQL> select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where

table_name='T' a
nd wner=user;

TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH
---------- ---------- ---------- -----------
T NAME CHAR 2

但是，oracle在判断插入的时候不是以dba_tab_columns中记录的data_length来做为限制条件的。看看UTF8下的试验结果：

SQL> select name,value$ from props$ where name='NLS_CHARACTERSET';

NAME VALUE$
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8

SQL> drop table t;

表已删除。

SQL> create table t(name char(1 char));

表已创建。

SQL> select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where
2 table_name='T' and wner=user;

TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH
---------- ---------- ---------- -----------
T NAME CHAR 4

SQL> insert into t values('aa');
insert into t values('aa')
*
第 1 行出现错误:
ORA-12899: 列 "SYS"."T"."NAME" 的值太大 (实际值: 2, 最大值: 1)

SQL> insert into t values('a');

已创建 1 行。

SQL> insert into t values('测');

已创建 1 行。

SQL> select dump(name) from t;

DUMP(NAME)
--------------------------------------------------------------------------------
Typ=96 Len=1: 97
Typ=96 Len=3: 230,181,139

可以看到，一个英文字符占一个字节，那么'aa'只占两个字节，却无法插入。'测'占3个字节，却能插入。所以在判断插入的数据是否符合长度要求时，oracle会根据定义的数据类型是按字节还是按字符数，然后参考系统当前的字符集设置来做出正确的判断。

实际上，oracle提供的求字符串长度的函数，返回的也是字符个数，而非字节数：
SQL> select length('a') from dual;

LENGTH('A')
-----------
1

SQL> select length('测') from dual;

LENGTH('测')
------------
1

而lengthb返回的才是字节数：

SQL> select lengthb('a') from dual;

LENGTHB('A')
------------
1

SQL> select lengthb('测') from dual;

LENGTHB('测')
-------------
3

Oracle里Varchar、varchar2的参数(byte和char)的区别测试

Oracle里Varchar、varchar2的参数(byte和char)的区别测试 

1 看一下语法图 

2 下面进行sqlplus的测试，使用scott/tiger帐号。 

SQL> create table test(name varchar(5 byte),namec varchar(5 char));表已创建。 SQL> insert into test(name) values('12345'); 

已创建 1 行。 

SQL> insert into test(name) values('123456'); 

insert into test(name) values('123456') 

第 1 行出现错误: 

ORA-12899: 列 "SCOTT"."TEST"."NAME" 的值太大 (实际值: 6, 最大值: 5) 

SQL> insert into test(name) values('天津'); 

已创建 1 行。 

SQL> insert into test(name) values('天津市'); 

insert into test(name) values('天津市') 

第 1 行出现错误: 

ORA-12899: 列 "SCOTT"."TEST"."NAME" 的值太大 (实际值: 6, 最大值: 5) 

SQL> insert into test(namec) values('12345'); 

已创建 1 行。 

SQL> insert into test(namec) values('123456'); 

insert into test(namec) values('123456') 

第 1 行出现错误: 

ORA-12899: 列 "SCOTT"."TEST"."NAMEC" 的值太大 (实际值: 6, 最大值: 5) 

SQL> insert into test(namec) values('天津'); 

已创建 1 行。 

SQL> insert into test(namec) values('天津市'); 

已创建 1 行。 

SQL> insert into test(namec) values('天津市北京'); 

已创建 1 行。 

SQL> insert into test(namec) values('天津市北京市'); 

insert into test(namec) values('天津市北京市') 

第 1 行出现错误: 

ORA-12899: 列 "SCOTT"."TEST"."NAMEC" 的值太大 (实际值: 6, 最大值: 5) 

SQL> select * from test; 

NAME  NAMEC 

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

12345 

天津 

      12345 

     天津 

      天津市 

      天津市北京 

已选择6行。 

SQL> 

3 分析 

由于一个汉字在我的GBK系统里占用2个字节，所以byte的只能插入2个汉字，而char的可以插入5个汉字。 

所以byte 就是字节数，对于汉字，GBK占用2个字节，如果是UTF-8则占用3个字节。 

为了充分利用4000个的上限，给自己减少麻烦，建议存储含有中文文字的字段时，采用char类型。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14710393/viewspace-754870/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14710393/viewspace-754870/