Oracle定义字符串类型VARCHAR2和CHAR指定长度的用法如下:
varchar2(<SIZE> <BYTE|CHAR>) <SIZE>是介于1~4000之间的一个数,表示最多占用4000字节的存储空间。
char(<SIZE> <BYTE|CHAR>) <SIZE>是介于1~2000之间的一个数,表示最多占用2000字节的存储空间。
那其中的BYTE和CHAR有什么区别呢
BYTE,用字节指定:VARCHAR2(10 BYTE)。这能支持最多10字节的数据,在一个多字节字符集中,这可能只是两个字符。采用多字节字符集时,字节与字符并不相同。
CHAR,用字符指定:VARCHAR2(10 CHAR)。这将支持最多10字符数据,可能是多达40字节的信息。另外,VARCHAR2(4000 CHAR)理论上支持最多4000个字符的数据,不过由于Oracle中字符串数据类型限制为4000字节,所以可能无法得到全部4000个字符。
使用UTF8之类的多字节字符集时,建议你在VARCHAR2/CHAR定义中使用CHAR修饰会,也就是说,使用VARCHAR2(30 CHAR),而不是VARCHAR2(30),因为你的本意很可能是定义一个实际上能存储30字符数据的列。还可以使用会话参数或系统参数NLS_LENGTH_SEMANTICS来修改默认行为,即把默认设置BYTE改为CHAR。不建议在系统级修改这个设置,而应该使用ALTER SESSION修改会话级。还有重要的一点,VARCHAR2中存储的字节数上界是4000。不过,即使你指定了VARCHAR(4000 CHAR),可能并不能在这个字段中放下4000个字符实际上,采用你选择的字符集时,如果所有字符都要用4个字节来表示,那么这个字段中就只能放下1000个字符!
下面使用一个小例子展示BYTE和CHAR之间的区别,并显示出上界的作用。
测试环境11.2.0.4,是在多字节字符集数据库上完成的,在此使用了字符集AL32UTF8,这个字符集支持最新版本的Unicode标准,采用一种变长方式对每个字符使用1~4个字节进行编码
zx@ORCL>colvaluefora30 zx@ORCL>colparameterfora30 zx@ORCL>select*fromnls_database_parameterswhereparameter='NLS_CHARACTERSET'; PARAMETER VALUE ------------------------------------------------------------ NLS_CHARACTERSET AL32UTF8 zx@ORCL>showparameternls_leng NAME TYPE VALUE --------------------------------------------------------------------------------------------------- nls_length_semantics string BYTE
创建测试表
zx@ORCL>createtablet(avarchar2(1),bvarchar2(1char),cvarchar2(4000char)); Tablecreated.
现在,这个表中插入一个UTF字符unistr('\00d6'),这个字符长度为2个字节,可以观察到以下结果:
zx@ORCL>selectlength(unistr('\00d6')),lengthb(unistr('\00d6'))fromdual; LENGTH(UNISTR('\00D6'))LENGTHB(UNISTR('\00D6')) ----------------------------------------------- 1 2 zx@ORCL>insertintot(a)values(unistr('\00d6')); insertintot(a)values(unistr('\00d6')) * ERRORatline1: ORA-12899:valuetoolargeforcolumn"ZX"."T"."A"(actual:2,maximum:1)
这说明:VARCHAR(1)的单位是字节而不是字符。这里确实只有一个Unicode字符,但是它在一个字节中放不下;将应用从单字节定宽字符集移植到一个多字节字符集时,可能会发现原来在字段中能放下的文本现在却无法放下。第二点的原因是:在一个单字节字符集中,包含20个字符的字符串长度就是20字节,完全可以在VARCHAR2(20)中放下。不过在一个多字节字符集中,20个字符的长度可以达到80字节(如果每个字符用4个字节表示),这样一杰,20个Unicode字符很可能无法在20个字节中放下。你可能会考虑将DDL修改为VARCHAR2(20 CHAR),或在运行DDL创建表时使用前面提到的NLS_LENGTH_SEMENTICS会话参数。
插入包含一个字符的字段时观察到以下结果:
zx@ORCL>insertintot(b)values(unistr('\00d6')); 1rowcreated. zx@ORCL>coldumpfora30 zx@ORCL>selectlength(b),lengthb(b),dump(b)dumpfromt; LENGTH(B)LENGTHB(B)DUMP -------------------------------------------------- 1 2Typ=1Len=2:195,150
这个INSERT成功了,而且可以看到,所有插入数据的长度(LENGTH)就是一个字符,所有字符串函数都以字符为单位工作。LENGTHB函数(字节长度)显示出这个字段占用了2字节的存储空间,另外DUMP函数显示了这些字节到底是什么。这个例子展示了VARCHAR2(N)并不一定存储N个字符,而只是存储N个字节。
下面测试VARCHAR2(4000)可能存储不了4000个字符
zx@ORCL>declare 2l_datevarchar2(4000char); 3l_chvarchar2(1char):=unistr('\00d6'); 4begin 5l_date:=rpad(l_ch,4000,l_ch); 6insertintot(c)values(l_date); 7end; 8/ declare * ERRORatline1: ORA-01461:canbindaLONGvalueonlyforinsertintoaLONGcolumn ORA-06512:atline6
在此显示出,一个4000字符的实际上长度为8000字节,这样一个字符串无法永久地存储在一个VARCHAR(4000 char)字段中,这个字符串能放在PL/sql变量中,因为在PL/sql中VARCHAR2最大可以达到32K。不过,存储在表中,VARCHAR2则被硬性限制为最多只能存放4000字节。我们可以成功地存储其中2000个字符:
zx@ORCL>declare 2l_datevarchar2(4000char); 3l_chvarchar2(1char):=unistr('\00d6'); 4begin 5l_date:=rpad(l_ch,2000,l_ch); 6insertintot(c)values(l_date); 7end; 8/ PL/sqlproceduresuccessfullycompleted. zx@ORCL> zx@ORCL>selectlength(c),lengthb(c)fromtwherecisnotnull; LENGTH(C)LENGTHB(C) -------------------- 20004000
输出可见,c占用了4000个字节的存储空间。
参考:《9I10G11G编程艺术 深入数据库体系结构》《Oracle Database Globalization Support Guide》