ORACLE VARCHAR2最大长度问题[作者:潇湘隐者]

2014-07-09 15:53 by 潇湘隐者,

作者: 潇湘隐者
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.

VARCHAR2数据类型的最大长度问题,是一个让人迷惑的问题,因为VARCHAR2既分PL/SQL Data Types中的变量类型,也分Oracle Database中的字段类型。简单的说,要看你在什么应用场景下,否则难以回答VARCHAR2数据类型的最大长度问题。

oracle数据库字段类型

关于Oracle Database中的字段的VARCHAR2类型的最大长度,我们先看下面的例子:

SQL> create table test ( name varchar2(4001) );
create table test ( name varchar2(4001) )
                                  *
ERROR at line 1:
ORA-00910: specified length too long for its datatype
 
 
SQL> create table test ( name varchar2(4000) ); 
 
Table created.

clip_image001

如上所示,在Oracle Database中,VARCHAR2字段类型,最大值为4000,SQL参考手册中也明确指出VARCHAR2的最大大小为4000,注意此处的最大长度是指字节长度,而不是指字符个数。这个跟参数NLS_LENGTH_SEMANTICS有一定关系,如下所示,当参数NLS_LENGTH_SEMANTICS为字节时,定义的变量长度为字节长度

如下所示,本数据库NLS_CHARACTERSET值为AL32UTF8,一个汉字占三个字节

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE; 
 
Session altered 
 
SQL> DROP TABLE TEST PURGE; 
 
Table dropped 
 
SQL> CREATE TABLE TEST ( NAME VARCHAR2(7)); 
 
Table created 
 
SQL> INSERT INTO TEST VALUES ('字'); 
 
1 row inserted 
 
SQL> COMMIT; 
 
Commit complete 
 
SQL> INSERT INTO TEST VALUES('字字字'); 
 
INSERT INTO TEST VALUES('字字字') 
 
ORA-12899: value too large for column "SYSTEM"."TEST"."NAME" (actual: 9, maximum: 7) 
 
SQL> SELECT LENGTH(NAME), LENGTHB(NAME) FROM TEST; 
 
LENGTH(NAME) LENGTHB(NAME) 
 
------------ ------------- 
 
1 3 
 

如果将参数NLS_LENGTH_SEMANTICS,则定义VARCHAR2(7)表示

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR; 
 
Session altered 
 
SQL> DROP TABLE TEST; 
 
Table dropped 
 
SQL> CREATE TABLE TEST ( NAME VARCHAR2(7)); 
 
Table created 
 
SQL> INSERT INTO TEST VALUES ('字'); 
 
1 row inserted 
 
SQL> COMMIT; 
 
Commit complete 
 
SQL> INSERT INTO TEST VALUES('字字字'); 
 
1 row inserted 
 
SQL> COMMIT; 
 
Commit complete 
 
SQL> SELECT LENGTH(NAME), LENGTHB(NAME) FROM TEST; 
 
LENGTH(NAME) LENGTHB(NAME) 
 
------------ ------------- 
 
1                 3 
 
3                 9 
 

不管参数NLS_LENGTH_SEMANTICS取值为字符或字节,其所能容纳的字符串的字节数都不能超过4000.


oracle中有三种比较常用的类型:varchar2(byte)varchar2(char)nvarchar2()

那么这三种类型到底有什么区别呢?

首先,我们要时刻记清:无论是varchar2还是nvarchar2最大字节数都是4000

ALTER SESSION SET NLS_LENGTH_SEMANTICS= byte | char;

varchar2(byte):就是默认的表示方式,比如我们写成:varchar2(100),就相当于varchar2(100 byte),表示最大字节数是100,该字段最多能容纳100个字节,强调空间大小。

由于我们描述的是字节,因此,保存汉字等字符时,就要小心了。如果你的数据库用的是GBK编码,那么一个汉字将占用2个字节,最多能存50个汉字,如果你的数据库用的是UTF8编码,那么一个汉字将占用3个字节,最多能存33个汉字。

varchar2(char):表示最大字符数是100,该字段最多能容纳100个字符,强调个数。假设我们写成varchar2(100char),那么无论是数字、字母、汉字,都看成一个字符,最多写100个,当然,汉字越多,占用的空间越大,同样遵循上边的数据库编码原则。例如:存入一个汉字,底层占23个字节,存入一个字母,占1个字节,绝对不是某些文章所说1个字母或数字也占23个字节!

nvarchar2():没有bytechar之分,类似于varchar2(char),只不过nvarchar2()屏蔽了数据库编码,无论是何种编码,nvarchar2()中一个汉字都占两个字节。

一般的教程,也就到这了,可是如果再多一步思考,会发现一个致命问题。

实际应用中,很可能会出现这种写法:varchar2(1400char),我们主观的认为,这个字段最长不能超过1400个字符,这意味着我们可能会存入1399个字符,貌似很正确的样子。

但是,如果这1399个字符都是汉字,字符长度并没有超过1400,看起来一切正常,但实际上我们损失了一部分数据,为什么?

因为1399个汉字,按UTF8编码来说(99%的项目都是UTF8编码吧。。),需要占用1399*3=4197个字节,而文章开篇就说,无论是什么char,最大长度就是4000字节,一个也不能多,因此多出来的197个字节,都会抹去,而整个过程中,无任何错误提示,你的数据就这样蒸发了!

所以,对于GBK编码的数据库而言,安全的写法为:varchar2(2000 char)nvarchar2(2000),对于UTF8编码的数据库而言,安全的写法为:varchar2(1333char)nvarchar2(2000)





PL/SQL变量类型:

接下来我们看看PL/SQL中VARCHAR2变量类型,如下官方文档所示,它的最大字节长度为32767,所能容纳的字符个数取决于字符集。

Declaring Variables for Multibyte Characters

The maximum size of a CHAR or VARCHAR2 variable is 32,767 bytes, whether you specify the maximum size in characters or bytes. The maximum number of characters in the variable depends on the character set type and sometimes on the characters themselves:

Character Set Type

Maximum Number of Characters

Single-byte character set

32,767

n-byte fixed-width multibyte character set (for example, AL16UTF16)

FLOOR(32,767/n)

n-byte variable-width multibyte character set with character widths between 1 and n bytes (for example, JA16SJIS or AL32UTF8)

Depends on characters themselves—can be anything from 32,767 (for a string containing only 1-byte characters) through FLOOR(32,767/n) (for a string containing only n-byte characters).

When declaring a CHAR or VARCHAR2 variable, to ensure that it can always hold n characters in anymultibyte character set, declare its length in characters—that is, CHAR(n CHAR) or VARCHAR2(n CHAR), where n does not exceed FLOOR(32767/4) = 8191.

可以通过下面一个PL/SQL代码来验证一下,如下所示,可以定义一个VARCHAR2类型的变量,给其赋值6000个字符串。

 
DECLARE
    V_OUT VARCHAR2(32767);
BEGIN
     V_OUT := RPAD('T', 6000, 'M');
     DBMS_OUTPUT.PUT_LINE(LENGTH(V_OUT));
END;

如果给VARCHAR2类型变量赋值超过23767,就会报PLS-00215: String length constraints must be in range (1 .. 32767)错误。

DECLARE
    V_OUT VARCHAR2(32768);
BEGIN
     V_OUT := RPAD('T', 5000, 'M');
     DBMS_OUTPUT.PUT_LINE(LENGTH(V_OUT));
END;

clip_image002

  • 4
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值