oracle VARCHAR2(10 CHAR) 和 NVARCHAR2(10) 的区别

Difference between VARCHAR2(10 CHAR) and NVARCHAR2(10)

提问by álvaro González

I've installed Oracle Database 10g Express Edition (Universal) with the default settings:

我已经使用默认设置安装了 Oracle 数据库 10g 快捷版（通用）：

SELECT * FROM NLS_DATABASE_PARAMETERS;

NLS_CHARACTERSET               AL32UTF8                                 
NLS_NCHAR_CHARACTERSET         AL16UTF16                                

Given that both CHARand NCHARdata types seem to accept multi-byte strings, what is the exact difference between these two column definitions?

鉴于CHAR和NCHAR数据类型似乎都接受多字节字符串，这两个列定义之间的确切区别是什么？

VARCHAR2(10 CHAR)
NVARCHAR2(10)

回答by Vincent Malgrat

The NVARCHAR2datatype was introduced by Oracle for databases that want to use Unicode for some columns while keeping another character set for the rest of the database (which uses VARCHAR2). The NVARCHAR2 is a Unicode-only datatype.

该NVARCHAR2数据类型被甲骨文介绍了该要使用Unicode某些列，同时保持其他字符集的数据库的其余部分（使用VARCHAR2）数据库。NVARCHAR2 是仅限 Unicode 的数据类型。

One reason you may want to use NVARCHAR2 might be that your DB uses a non-Unicode character set and you still want to be able to store Unicode data for some columns without changing the primary character set. Another reason might be that you want to use two Unicode character set (AL32UTF8 for data that comes mostly from western Europe, AL16UTF16 for data that comes mostly from Asia for example) because different character sets won't store the same data equally efficiently.

您可能想要使用 NVARCHAR2 的一个原因可能是您的数据库使用非 Unicode 字符集，并且您仍然希望能够在不更改主要字符集的情况下存储某些列的 Unicode 数据。另一个原因可能是您想要使用两个 Unicode 字符集（AL32UTF8 用于主要来自西欧的数据，AL16UTF16 用于主要来自亚洲的数据），因为不同的字符集不会同样有效地存储相同的数据。

Both columns in your example (Unicode VARCHAR2(10 CHAR)and NVARCHAR2(10)) would be able to store the same data, however the byte storage will be different. Some strings may be stored more efficiently in one or the other.

示例中的两列（UnicodeVARCHAR2(10 CHAR)和NVARCHAR2(10)）都能够存储相同的数据，但是字节存储会有所不同。某些字符串可以更有效地存储在其中一个中。

Note also that some features won't work with NVARCHAR2, see this SO question:

另请注意，某些功能不适用于 NVARCHAR2，请参阅此 SO 问题：

• Oracle Text will not work with NVARCHAR2. What else might be unavailable?

• Oracle Text 不适用于 NVARCHAR2。还有什么可能不可用？

回答by Wernfried Domscheit

I don't think answer from Vincent Malgrat is correct. When NVARCHAR2was introducedlong time ago nobody was even talking about Unicode.

我认为 Vincent Malgrat 的回答不正确。当NVARCHAR2被介绍很久以前没有人甚至在谈论统一。

Initially Oracle provided VARCHAR2and NVARCHAR2to support localization. Common data (include PL/SQL) was hold in VARCHAR2, most likely US7ASCIIthese days. Then you could apply NLS_NCHAR_CHARACTERSETindividually (e.g. WE8ISO8859P1) for each of your customer in any country without touching the common part of your application.

最初 Oracle 提供VARCHAR2并NVARCHAR2支持本地化。公共数据（包括 PL/SQL）保存在 中VARCHAR2，最近很可能US7ASCII。然后，您可以NLS_NCHAR_CHARACTERSET单独（例如WE8ISO8859P1）为您在任何国家/地区的每个客户申请，而无需触及您申请的公共部分。

Nowadays character set AL32UTF8is the default which fully supports Unicode. In my opinion today there is no reason anymore to use NLS_NCHAR_CHARACTERSET, i.e. NVARCHAR2, NCHAR2, NCLOB. Note, there are more and more Oracle native functions which do not support NVARCHAR2, so you should really avoid it. Maybe the only reason is when you have to support mainly Asian characters where AL16UTF16consumes less storage compared to AL32UTF8.

现在字符集AL32UTF8是完全支持 Unicode 的默认字符集。在我看来，今天没有理由再使用NLS_NCHAR_CHARACTERSET，即NVARCHAR2，NCHAR2，NCLOB。请注意，越来越多的 Oracle 本机函数不支持 NVARCHAR2，因此您应该避免使用它。也许唯一的原因是当您必须主要支持亚洲字符时，AL16UTF16与AL32UTF8.

回答by Chivorn

• The NVARCHAR2stores variable-length character data. When you create a table with the NVARCHAR2column, the maximum size is always in character length semantics, which is also the default and only length semantics for the NVARCHAR2data type.

  The NVARCHAR2data type uses AL16UTF16character set which encodes Unicode data in the UTF-16encoding. The AL16UTF16use 2 bytesto store a character. In addition, the maximum byte length of an NVARCHAR2depends on the configured national character set.

• VARCHAR2The maximum size of VARCHAR2can be in either bytes or characters. Its column only can store characters in the default character set while the NVARCHAR2can store virtually any characters. A single character may require up to 4 bytes.

• 的NVARCHAR2存储可变长度的字符数据。使用NVARCHAR2列创建表时，最大大小始终采用字符长度语义，这也是NVARCHAR2数据类型的默认且唯一的长度语义。

  的NVARCHAR2数据类型使用AL16UTF16的字符集，其在编码的Unicode数据UTF-16编码。在AL16UTF16使用2 bytes存储一个字符。此外，a 的最大字节长度NVARCHAR2取决于配置的国家字符集。

• VARCHAR2的最大大小VARCHAR2可以是字节或字符。它的列只能存储默认字符集中的字符，而NVARCHAR2实际上可以存储任何字符。单个字符最多可能需要4 bytes.

By defining the field as:

通过将字段定义为：

• VARCHAR2(10 CHAR)you tell Oracle it can use enough space to store 10 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes.
• NVARCHAR2(10)you tell Oracle it can store 10 characters with 2 bytesper character

• VARCHAR2(10 CHAR)您告诉 Oracle 它可以使用足够的空间来存储 10 个字符，无论存储每个字符需要多少字节。单个字符最多可能需要4 bytes.
• NVARCHAR2(10)你告诉 Oracle2 bytes每个字符可以存储 10个字符

In Summary:

总之：

• VARCHAR2(10 CHAR)can store maximum of 10 charactersand maximum of 40 bytes(depends on the configured national character set).

• NVARCHAR2(10)can store maximum of 10 charactersand maximum of 20 bytes(depends on the configured national character set).

• VARCHAR2(10 CHAR)可以存储最大值10 characters和最大值40 bytes（取决于配置的国家字符集）。

• NVARCHAR2(10)可以存储最大值10 characters和最大值20 bytes（取决于配置的国家字符集）。

Note:Character set can be UTF-8, UTF-16,....

注意：字符集可以是UTF-8, UTF-16,....

Please have a look at this tutorialfor more detail.

请查看本教程以获取更多详细信息。

Have a good day!

祝你有美好的一天！

回答by Pooja

nVarchar2is a Unicode-onlystorage.

nVarchar2是仅Unicode存储。

Though both data types are variable length String datatypes, you can notice the difference in how they store values. Each character is stored in bytes. As we know, not all languages have alphabets with same length, eg, English alphabet needs 1 byte per character, however, languages like Japanese or Chinese need more than 1 byte for storing a character.

尽管这两种数据类型都是可变长度字符串数据类型，但您可以注意到它们存储值的方式不同。每个字符以字节存储。众所周知，并非所有语言都有相同长度的字母，例如，英文字母每个字符需要1个字节，而日语或中文等语言则需要超过1个字节来存储一个字符。

When you specify varchar2(10), you are telling the DB that only 10 bytesof data will be stored. But, when you say nVarchar2(10), it means 10 characterswill be stored. In this case, you don't have to worry about the number of bytes each character takes.

当您指定varchar2(10) 时，您是在告诉数据库将只存储10 个字节的数据。但是，当您说nVarchar2(10) 时，这意味着将存储10 个字符。在这种情况下，您不必担心每个字符占用的字节数。