Oracle varchar2或char类型的byte和char的区别

227次阅读

共计 3533 个字符，预计需要花费 9 分钟才能阅读完成。

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 个字节进行编码
linuxidc@ORCL>col value for a30
linuxidc@ORCL>col parameter for a30
linuxidc@ORCL>select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’;

PARAMETER VALUE
—————————— ——————————
NLS_CHARACTERSET AL32UTF8
linuxidc@ORCL>show parameter nls_leng

NAME TYPE VALUE
———————————— ——————————— ——————————
nls_length_semantics string BYTE

创建测试表

linuxidc@ORCL>create table t (a varchar2(1),b varchar2(1 char),c varchar2(4000 char));

Table created.

现在，这个表中插入一个 UTF 字符 unistr(‘\00d6’)，这个字符长度为 2 个字节，可以观察到以下结果：
linuxidc@ORCL>select length(unistr(‘\00d6’)),lengthb(unistr(‘\00d6’)) from dual;

LENGTH(UNISTR(‘\00D6’)) LENGTHB(UNISTR(‘\00D6’))
———————– ————————
1 2

linuxidc@ORCL>insert into t (a) values (unistr(‘\00d6’));
insert into t (a) values (unistr(‘\00d6’))
*
ERROR at line 1:
ORA-12899: value too large for column “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 会话参数。
插入包含一个字符的字段时观察到以下结果：
linuxidc@ORCL>insert into t (b) values (unistr(‘\00d6’));

1 row created.

linuxidc@ORCL>col dump for a30
linuxidc@ORCL>select length(b),lengthb(b),dump(b) dump from t;

LENGTH(B) LENGTHB(B) DUMP
———- ———- ——————————
1 2 Typ=1 Len=2: 195,150

这个 INSERT 成功了，而且可以看到，所有插入数据的长度 (LENGTH) 就是一个字符，所有字符串函数都以字符为单位工作。LENGTHB 函数 (字节长度) 显示出这个字段占用了 2 字节的存储空间，另外 DUMP 函数显示了这些字节到底是什么。这个例子展示了 VARCHAR2(N)并不一定存储 N 个字符，而只是存储 N 个字节。
下面测试 VARCHAR2(4000)可能存储不了 4000 个字符
linuxidc@ORCL>declare
2 l_date varchar2(4000 char);
3 l_ch varchar2(1 char) := unistr(‘\00d6’);
4 begin
5 l_date:=rpad(l_ch,4000,l_ch);
6 insert into t(c) values(l_date);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 6

在此显示出，一个 4000 字符的实际上长度为 8000 字节，这样一个字符串无法永久地存储在一个 VARCHAR(4000 char)字段中，这个字符串能放在 PL/SQL 变量中，因为在 PL/SQL 中 VARCHAR2 最大可以达到 32K。不过，存储在表中，VARCHAR2 则被硬性限制为最多只能存放 4000 字节。我们可以成功地存储其中 2000 个字符：
linuxidc@ORCL>declare
2 l_date varchar2(4000 char);
3 l_ch varchar2(1 char) := unistr(‘\00d6’);
4 begin
5 l_date:=rpad(l_ch,2000,l_ch);
6 insert into t(c) values(l_date);
7 end;
8 /

PL/SQL procedure successfully completed.

linuxidc@ORCL>
zx@ORCL>select length(c),lengthb(c) from t where c is not null;

LENGTH(C) LENGTHB(C)
———- ———-
2000 4000