Oracle中的Char与Varchar的区别和实例

1. CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串abc，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符),在数据库中它是以空格占位的，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于

　　1. CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc"，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符),在数据库中它是以空格占位的，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。

　　2.CHAR的效率比VARCHAR2的效率稍高。看来cscm_number应该设成 char(19)

　　3.目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是Oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空字符串的特性改为存储NULL值。假如你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。

　　何时该用CHAR，何时该用varchar2?

　　CHAR与VARCHAR2是一对矛盾的统一体，两者是互补的关系. VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。 VARCHAR2虽然比CHAR节省空间，但是假如一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。

　　关于char和varchar2的比较

　　char类型与char型或字符常量的比较，在比较时使用补齐空格的方式进行比较。

　　varchar2类型与varchar2类型，char型和字符常量的比较，在比较时不补充空格，直接比较。

　　create table tt(A1 CHAR(2) , A2 VARCHAR2(2)) ;

　　INSERT INTO TT VALUES('A','A') ;

　　insert into tt values('A','A ') ;

　　COMMIT ;

　　--CHAR型与字符常量的比较，字符常量作为char型处理

　　--与'A'比较，返回2行，也就是在比较时自动将常量'A'右补齐空格后比较

　　select * from tt where a1='A' ;

　　A1 A2

　　-- --

　　A A

　　A A

　　--与'A '比较，返回2行，也就是在比较时自动将常量'A'右补齐空格后比较

　　select * from tt where a1='A ' ;

　　A1 A2

　　-- --

　　A A

　　A A

　　--VARCHAR2与常量的比较，字符常量作为varchar2型处理

　　--与'A'比较，返回1行，也就是在比较时对'A'不做处理，直接比较

　　select * from TT WHERE A2='A';

　　A1 A2

　　-- --

　　A A

　　--与'A '比较，返回1行，也就是在比较时对'A'不做处理，直接比较

　　select * from TT WHERE A2='A ';

　　A1 A2

　　-- --

　　A A

　　--当CHAR类型和VARCHAR2类型比较时，比较时对字段值是不作处理，直接比较的

　　--让A1和A2直接比较，此时是直接比较，有一条记录的A1和A2相同

　　select * from tt where a1=a2 ;

　　A1 A2

　　-- --

　　A A

　　但是当和decode函数配合使用时，出现不同的情况

　　使用A1字段

　　select decode(a1,'A','AAAA','BBBB') FROM TT ;

　　DECODE(A1,'A','AAAA','BBBB')

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

　　BBBB

　　BBBB

　　虽然A1字段为char(2)，但是比较时并没有将常量'A'补空格再与字段A1做比较，而是直接进行比较，也就是将两个比较字段按照varchar2类型处理的

　　因此比较时认为字段A1不等于常量'A'，出现两条结果为'BBB'的记录。

　　进一步验证，

　　select decode(a1,'A ','AAAA','BBBB') FROM TT ; --此处是两个空格

　　DECODE(A1,'A','AAAA','BBBB')

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

　　BBBB

　　BBBB

　　还是返回两条'BBB'的记录，说明比较的不是按照char型的比较规则处理的。

　　使用A2字段

　　select decode(a2,'A','AAAA','BBBB') FROM TT ;

　　DECODE(A2,'A','AAAA','BBBB')

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

　　AAAA

　　BBBB

　　此时是正常的VARCHAR2类型之间的比较，第一条记录的A2字段等于'A'，返回'AAA',第二行记录的A2字段为’A '，比较时不等，返回'BBB'

　　当使用case表达式处理A1字段时，出现了与decode函数不同的处理结果

　　使用字段A1

　　select case a1 when 'A' then 'AAA' else 'BBB' end from tt ;

　　CASEA1WHEN'A'THEN'AAA'ELSE'BBB

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

　　AAA

　　AAA

　　在使用case语句中使用A1字段与常量'A'比较时，两个比较值按照char型的比较规则处理，在右补空格之后进行比较，因此返回两条记录

　　进一步验证，如下，

　　select case a1 when 'A ' then 'AAA' else 'BBB' end from tt ;

　　CASEA1WHEN'A'THEN'AAA'ELSE'BBB

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

　　AAA

　　AAA

　　此时将常量改为'A '，比较时仍视为char类型之间的比较，将字段A1补齐空格后与常量比较。

　　使用A2字段

　　select case a2 when 'A' then 'AAA' else 'BBB' end from tt ;

　　CASEA2WHEN'A'THEN'AAA'ELSE'BBB

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

　　AAA

　　BBB

　　此时是正常的VARCHAR2类型之间的比较，，第一条记录的A2字段等于'A'，返回'AAA',第二行记录的A2字段为’A '，比较时不等，返回'BBB'

　　总结，

　　在使用decode函数对char字段做比较时，需要注意即使比较的两个字段都是char类型，但是decode函数是将其转化varchar2类型 进行处理，不遵循char型的比较规则

　　对于char数据，在集合操作中，按照char的实际数据进行比较，而不是按照char型数据的比较规则进行的。

　　下面是一个简单例子

　　create table t1(name char(10)) ;

　　create table t2(name char(20)) ;

　　begin

　　for i in 1..5 loop

　　insert into t1 values(to_char(i)) ;

　　insert into t1 values(to_char(i)) ;

　　end loop ;

　　commit;

　　end ;

　　select name from t1 minus select name from t2 ;

　　NAME

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

　　1

　　2

　　3

　　4

　　5

　　如果按照char型的比较规则，则不应该有返回值。

　　select anem from t1 intersect select name from t2 ;

　　NAME

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

　　无返回值，也说明集合操作时比较没有按照char型的比较规则。

　　===============================评论2============================

　　对于decode和case对于char类型的差别，原因是由于case是表达式，而decode是函数。

　　函数中输入参数和返回值一般都定义为varchar2类型。

　　多谢指点，一时没有想到这里，看了一下decode函数的定义，如你所述，在使用decode的函数对char型数据进行类型转换。

　　下面是decode的函数的参数定义。

　　SQL> desc sys.standard.decode

　　Parameter Type Mode Default?

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

　　(RESULT) NUMBER

　　EXPR NUMBER IN

　　PAT NUMBER IN

　　RES NUMBER IN

　　(RESULT) VARCHAR2

　　EXPR NUMBER IN

　　PAT NUMBER IN

　　RES VARCHAR2 IN

　　(RESULT) DATE

　　EXPR NUMBER IN

　　PAT NUMBER IN

　　RES DATE IN

　　(RESULT) NUMBER

　　EXPR VARCHAR2 IN

　　PAT VARCHAR2 IN

　　RES NUMBER IN

　　(RESULT) VARCHAR2

　　EXPR VARCHAR2 IN

　　PAT VARCHAR2 IN

　　RES VARCHAR2 IN

　　(RESULT) DATE

　　EXPR VARCHAR2 IN

　　PAT VARCHAR2 IN

　　RES DATE IN

　　(RESULT) NUMBER

　　EXPR DATE IN

　　PAT DATE IN

　　RES NUMBER IN

　　(RESULT) VARCHAR2

　　EXPR DATE IN

　　PAT DATE IN

　　RES VARCHAR2 IN

　　(RESULT) DATE

　　EXPR DATE IN

　　PAT DATE IN

　　RES DATE IN

　　(RESULT) SYS.STANDARD.

　　EXPR SYS.STANDARD. IN

　　PAT SYS.STANDARD. IN

　　RES SYS.STANDARD. IN

　　(RESULT) SYS.STANDARD.

　　EXPR SYS.STANDARD. IN

　　PAT SYS.STANDARD. IN

　　RES SYS.STANDARD. IN