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

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个字节进行编码

<code>zx@ORCL&gt;col value </code><code>for</code> <code>a30</code>

<code>zx@ORCL&gt;col parameter </code><code>for</code> <code>a30</code>

<code>zx@ORCL&gt;</code><code>select</code> <code>* </code><code>from</code> <code>nls_database_parameters </code><code>where</code> <code>parameter=</code><code>'NLS_CHARACTERSET'</code><code>;</code>

<code>PARAMETER              VALUE</code>

<code>------------------------------ ------------------------------</code>

<code>NLS_CHARACTERSET           AL32UTF8</code>

<code>zx@ORCL&gt;show parameter nls_leng</code>

<code>NAME</code>                     <code>TYPE                 VALUE</code>

<code>------------------------------------ --------------------------------- ------------------------------</code>

<code>nls_length_semantics             string                   BYTE</code>

创建测试表

<code>zx@ORCL&gt;</code><code>create</code> <code>table</code> <code>t (a varchar2(1),b varchar2(1 </code><code>char</code><code>),c varchar2(4000 </code><code>char</code><code>));</code>

<code>Table</code> <code>created.</code>

现在，这个表中插入一个UTF字符unistr('\00d6')，这个字符长度为2个字节，可以观察到以下结果：

<code>zx@ORCL&gt;</code><code>select</code> <code>length(unistr(</code><code>'\00d6'</code><code>)),lengthb(unistr(</code><code>'\00d6'</code><code>)) </code><code>from</code> <code>dual;</code>

<code>LENGTH(UNISTR(</code><code>'\00D6'</code><code>)) LENGTHB(UNISTR(</code><code>'\00D6'</code><code>))</code>

<code>----------------------- ------------------------</code>

<code>              </code><code>1             2</code>

<code>zx@ORCL&gt;</code><code>insert</code> <code>into</code> <code>t (a) </code><code>values</code> <code>(unistr(</code><code>'\00d6'</code><code>));</code>

<code>insert</code> <code>into</code> <code>t (a) </code><code>values</code> <code>(unistr(</code><code>'\00d6'</code><code>))</code>

<code>                          </code><code>*</code>

<code>ERROR </code><code>at</code> <code>line 1:</code>

<code>ORA-12899: value too large </code><code>for</code> <code>column</code> <code>"ZX"</code><code>.</code><code>"T"</code><code>.</code><code>"A"</code> <code>(actual: 2, maximum: 1)</code>

这说明：VARCHAR(1)的单位是字节而不是字符。这里确实只有一个Unicode字符，但是它在一个字节中放不下；将应用从单字节定宽字符集移植到一个多字节字符集时，可能会发现原来在字段中能放下的文本现在却无法放下。第二点的原因是：在一个单字节字符集中，包含20个字符的字符串长度就是20字节，完全可以在VARCHAR2(20)中放下。不过在一个多字节字符集中，20个字符的长度可以达到80字节(如果每个字符用4个字节表示)，这样一杰，20个Unicode字符很可能无法在20个字节中放下。你可能会考虑将DDL修改为VARCHAR2(20 CHAR)，或在运行DDL创建表时使用前面提到的NLS_LENGTH_SEMENTICS会话参数。

插入包含一个字符的字段时观察到以下结果：

<code>zx@ORCL&gt;</code><code>insert</code> <code>into</code> <code>t (b) </code><code>values</code> <code>(unistr(</code><code>'\00d6'</code><code>));</code>

<code>1 row created.</code>

<code>zx@ORCL&gt;col dump </code><code>for</code> <code>a30</code>

<code>zx@ORCL&gt;</code><code>select</code> <code>length(b),lengthb(b),dump(b) dump </code><code>from</code> <code>t;</code>

<code> </code><code>LENGTH(B) LENGTHB(B) DUMP</code>

<code>---------- ---------- ------------------------------</code>

<code>     </code><code>1     2 Typ=1 Len=2: 195,150</code>

这个INSERT成功了，而且可以看到，所有插入数据的长度(LENGTH)就是一个字符，所有字符串函数都以字符为单位工作。LENGTHB函数(字节长度)显示出这个字段占用了2字节的存储空间，另外DUMP函数显示了这些字节到底是什么。这个例子展示了VARCHAR2(N)并不一定存储N个字符，而只是存储N个字节。

下面测试VARCHAR2(4000)可能存储不了4000个字符

<code>zx@ORCL&gt;</code><code>declare</code>

<code>  </code><code>2  l_date varchar2(4000 </code><code>char</code><code>);</code>

<code>  </code><code>3  l_ch   varchar2(1 </code><code>char</code><code>) := unistr(</code><code>'\00d6'</code><code>);</code>

<code>  </code><code>4  </code><code>begin</code>

<code>  </code><code>5  l_date:=rpad(l_ch,4000,l_ch);</code>

<code>  </code><code>6  </code><code>insert</code> <code>into</code> <code>t(c) </code><code>values</code><code>(l_date);</code>

<code>  </code><code>7  </code><code>end</code><code>;</code>

<code>  </code><code>8  /</code>

<code>declare</code>

<code>*</code>

<code>ORA-01461: can bind a LONG value </code><code>only</code> <code>for</code> <code>insert</code> <code>into</code> <code>a LONG </code><code>column</code>

<code>ORA-06512: </code><code>at</code> <code>line 6</code>

在此显示出，一个4000字符的实际上长度为8000字节，这样一个字符串无法永久地存储在一个VARCHAR(4000 char)字段中，这个字符串能放在PL/SQL变量中，因为在PL/SQL中VARCHAR2最大可以达到32K。不过，存储在表中，VARCHAR2则被硬性限制为最多只能存放4000字节。我们可以成功地存储其中2000个字符：

<code>  </code><code>5  l_date:=rpad(l_ch,2000,l_ch);</code>

<code>PL/SQL </code><code>procedure</code> <code>successfully completed.</code>

<code>zx@ORCL&gt;</code>

<code>zx@ORCL&gt;</code><code>select</code> <code>length(c),lengthb(c) </code><code>from</code> <code>t </code><code>where</code> <code>c </code><code>is</code> <code>not</code> <code>null</code><code>;</code>

<code> </code><code>LENGTH(C) LENGTHB(C)</code>

<code>---------- ----------</code>

<code>      </code><code>2000       4000</code>

输出可见，c占用了4000个字节的存储空间。

参考：《9I10G11G编程艺术  深入数据库体系结构》《Oracle Database Globalization Support Guide》

     本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1893768，如需转载请自行联系原作者