时间戳

首页 > 解决方案 > Oracle 返回不在 BYTE 中的列定义的正确大小

oracle - Oracle 返回不在 BYTE 中的列定义的正确大小

问题描述

嗨，伙计们，我有一个问题，我有一个有 2 个字段的表

CREATE TABLE TEST( FIELD1 VARCHAR2(2 CHAR), FIELD2 VARCHAR2(3 CHAR) );

如果我运行这个查询（Oracle SQL Developer）

SELECT DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=TEST;

返回，字节中列的长度，但我想要正确的大小，即“2 和 3”而不是字节，我的表作为数据类型都是varchar2.

有没有办法获得正确的价值？

谢谢大家的支持。

标签： oracle

解决方案

正确的大小和数据长度是两个不同的概念，特别是当您的数据库中有 MULTIBYTE 字符集时。

当您在 Oracle 中定义长度为 varchar2(3 byte) 的列时，您是在指示 Oracle 最多存储 3 个字节。但是，如果您的数据库使用多字节字符集作为 UTF8，数据长度和数据大小并不总是相同，并且某些字符需要存储更多字节。

让我用一个例子来说明答案：

SQL> col parameter for a30
SQL> col value for a30
SQL> select parameter,value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8

SQL> create table check_size ( c1 varchar2(2 byte), c2 varchar2(2 char) );

Table created.

SQL> col column_name for a30
SQL> col data_type for a30
SQL> col data_length for 999999
SQL>  SELECT COLUMN_NAME,
  2            DATA_TYPE,
  3            DATA_LENGTH,
  4            CASE
  5              WHEN CHAR_USED = 'C' THEN
  6                'CHAR'
  7              WHEN CHAR_USED = 'B' THEN
  8                'BYTE'
  9            END AS BYTE_OR_CHAR
 10       FROM ALL_TAB_COLS
 11      WHERE OWNER='TEST'
 12*     AND TABLE_NAME = 'CHECK_SIZE'

COLUMN_NAME                    DATA_TYPE                      DATA_LENGTH BYTE
------------------------------ ------------------------------ ----------- ----
C1                             VARCHAR2                                 2 BYTE
C2                             VARCHAR2                                 8 CHAR

SQL>  insert into check_size values ( 'aa' , 'aa' ) ;

1 row created.

SQL> commit;

Commit complete.

SQL>  insert into check_size values ( 'aä' , 'aa' ) ;
 insert into check_size values ( 'aä' , 'aa' )
                                 *
ERROR at line 1:
ORA-12899: value too large for column "TEST"."CHECK_SIZE"."C1" (actual: 7,
maximum: 2)

如您所见，第二次插入失败，因为它实际上需要更多字节来存储这些字符。

SQL>  select dump('ää') as content from dual ;

CONTENT
--------------------------------------------------------------
Typ=96 Len=12: 239,191,189,239,191,189,239,191,189,239,191,189

SQL>  alter table check_size modify c1 varchar2(12 byte) ;

Table altered

SQL>  insert into check_size values ( 'ää' , 'aa' );

1 row created.

SQL> commit;

NCHAR、NVARCHAR2、CLOB 和 NCLOB 列始终是基于字符的。

记录为 SYS 的会话不使用 NLS_LENGTH_SEMANTICS 参数。它们对所有创建的对象使用 BYTE 长度语义，除非被对象定义（SQL DDL 语句）中的显式 BYTE 和 CHAR 限定符覆盖。

我希望它能更好地阐明在表 DDL 中定义列时的数据大小和数据长度的概念以及与数据库的 CHARACTERSET 的关系。

问候

推荐阅读