NVARCHAR2和VARCHAR2的比较

 

Oralce官方文档有对NVARCHAR2和VARCHAR2的描述如下:

NVARCHAR2 Datatype

The NVARCHAR2 datatype is a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold.  subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.

The maximum length of the column is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. Please refer to Oracle Database Globalization Support Guide for information on Unicode datatype support.

VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.

You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.

To ensure proper data conversion between databases with different character sets, you must ensure that VARCHAR2 data consists of well-formed strings. See Oracle Database Globalization Support Guide for more information on character set support.

 

我们在选择表字段类型的时候经常用到的是varchar2,很少用到nvarchar2,主要是因为nvarchar2类型是跟数据库的字符集有关的,比如是在ZHS16GBK中文字符集的数据下,nvarchar2(10)会存储10个汉字,而varchar2(10)只能存储5个汉字(汉字占两个字节).

 

SQL> create table tb_varchar_test
  2  (
  3    varchar2_type varchar2(10),
  4    nvarchar2_type nvarchar2(10)
  5  );

Table created.

SQL> insert into tb_varchar_test(varchar2_type,nvarchar2_type)
  2  values('一二三四五六','一二三四五六七八九十');
values('一二三四五六','一二三四五六七八九十')
       *
ERROR at line 2:
ORA-12899: value too large for column "HXL"."TB_VARCHAR_TEST"."VARCHAR2_TYPE"
(actual: 12, maximum: 10)

SQL> insert into tb_varchar_test(varchar2_type,nvarchar2_type)
  2  values('一二三四五','一二三四五六七八九十');

1 row created.

 

-- The End --