VARCHAR2(10 CHAR) 和 NVARCHAR2(10) 之间的区别
我已经使用默认设置安装了 Oracle Database 10g Express Edition（通用）：
SELECT * FROM NLS_DATABASE_PARAMETERS;
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
鉴于 CHAR 和 NCHAR 数据类型似乎都接受多字节字符串，那么什么是这两个列定义之间的确切区别？
VARCHAR2(10 CHAR)
NVARCHAR2(10)
I've installed Oracle Database 10g Express Edition (Universal) with the default settings:
SELECT * FROM NLS_DATABASE_PARAMETERS;
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
Given that both CHAR and NCHAR data types seem to accept multi-byte strings, what is the exact difference between these two column definitions?
VARCHAR2(10 CHAR)
NVARCHAR2(10)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
NVARCHAR2 数据类型由 Oracle 引入适用于想要对某些列使用 Unicode，同时为数据库的其余部分保留另一个字符集（使用 VARCHAR2）的数据库。 NVARCHAR2 是仅限 Unicode 的数据类型。
您可能想要使用 NVARCHAR2 的原因之一可能是您的数据库使用非 Unicode 字符集，并且您仍然希望能够在不更改主字符集的情况下存储某些列的 Unicode 数据。另一个原因可能是您想要使用两个 Unicode 字符集（例如，对于主要来自西欧的数据使用 AL32UTF8，对于主要来自亚洲的数据使用 AL16UTF16），因为不同的字符集无法同等有效地存储相同的数据。
示例中的两列（Unicode
VARCHAR2(10 CHAR)和NVARCHAR2(10)）将能够存储相同的数据，但字节存储将不同。某些字符串在其中之一中存储可能会更有效。另请注意，某些功能不适用于 NVARCHAR2，请参阅此问题：
The NVARCHAR2 datatype 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.
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.
Both columns in your example (Unicode
VARCHAR2(10 CHAR)andNVARCHAR2(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.Note also that some features won't work with NVARCHAR2, see this SO question:
我认为文森特·马尔格拉特的回答不正确。很久以前，当
NVARCHAR2被引入时，甚至没有人谈论 Unicode。最初，Oracle 提供了
VARCHAR2和NVARCHAR2来支持本地化。通用数据（包括 PL/SQL）保存在VARCHAR2中，现在很可能是US7ASCII。然后，您可以为任何国家/地区的每个客户单独应用NLS_NCHAR_CHARACTERSET（例如WE8ISO8859P1），而无需触及应用程序的公共部分。现在字符集
AL32UTF8是默认的，完全支持Unicode。在我看来，现在没有理由再使用NLS_NCHAR_CHARACTERSET，即NVARCHAR2、NCHAR2、NCLOB。请注意，越来越多的 Oracle 本机函数不支持 NVARCHAR2，因此您应该真正避免它。也许唯一的原因是当您必须主要支持亚洲字符时，与AL32UTF8相比，AL16UTF16消耗的存储空间更少。I don't think answer from Vincent Malgrat is correct. When
NVARCHAR2was introduced long time ago nobody was even talking about Unicode.Initially Oracle provided
VARCHAR2andNVARCHAR2to support localization. Common data (include PL/SQL) was hold inVARCHAR2, most likelyUS7ASCIIthese days. Then you could applyNLS_NCHAR_CHARACTERSETindividually (e.g.WE8ISO8859P1) for each of your customer in any country without touching the common part of your application.Nowadays character set
AL32UTF8is the default which fully supports Unicode. In my opinion today there is no reason anymore to useNLS_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 whereAL16UTF16consumes less storage compared toAL32UTF8.NVARCHAR2存储可变长度字符数据。当你创建一个包含
NVARCHAR2列的表，最大大小始终为在字符长度语义中，这也是默认且唯一的
NVARCHAR2数据类型的长度语义。NVARCHAR2数据类型使用AL16UTF16字符集，该字符集以UTF-16编码对Unicode数据进行编码。AL16UTF16使用2个字节来存储一个字符。此外，NVARCHAR2的最大字节长度取决于配置的国家字符集。VARCHAR2VARCHAR2的最大大小可以是字节或字符。它的列只能存储默认字符中的字符设置，而
NVARCHAR2几乎可以存储任何字符。单个字符最多可能需要4 个字节。通过将字段定义为：
VARCHAR2(10 CHAR)，您可以告诉 Oracle 它可以使用足够的空间来存储 10字符，无论存储每个字符需要多少字节。单个字符最多可能需要
4 个字节。NVARCHAR2(10)您告诉 Oracle 它可以存储 10 个字符，每个字符2 个字节总结：
VARCHAR2(10 CHAR)最多可存储10 个字符，最多40 个字节（取决于配置的国家字符集）。NVARCHAR2(10)最多可存储10 个字符和最多20 个字节（取决于配置的国家字符集）。< /p>注意：字符集可以是
UTF-8、UTF-16、....请查看本教程了解更多详细信息。
祝你有美好的一天！
The
NVARCHAR2stores variable-length character data. When youcreate a table with the
NVARCHAR2column, the maximum size is alwaysin character length semantics, which is also the default and only
length semantics for the
NVARCHAR2data type.The
NVARCHAR2data type usesAL16UTF16character set which encodes Unicode data in theUTF-16encoding. TheAL16UTF16use2 bytesto store a character. In addition, the maximum byte length of anNVARCHAR2depends on the configured national character set.VARCHAR2The maximum size ofVARCHAR2can be in either bytes or characters. Its column only can store characters in the default characterset while the
NVARCHAR2can store virtually any characters. A single character may require up to4 bytes.By defining the field as:
VARCHAR2(10 CHAR)you tell Oracle it can use enough space to store 10characters, 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 with2 bytesper characterIn Summary:
VARCHAR2(10 CHAR)can store maximum of10 charactersand maximum of40 bytes(depends on the configured national character set).NVARCHAR2(10)can store maximum of10 charactersand maximum of20 bytes(depends on the configured national character set).Note: Character set can be
UTF-8,UTF-16,....Please have a look at this tutorial for more detail.
Have a good day!
nVarchar2 是一种仅支持 Unicode 的存储。
尽管这两种数据类型都是可变长度字符串数据类型，但您可以注意到它们存储值的方式有所不同。
每个字符都以字节存储。我们知道，并非所有语言都有相同长度的字母表，例如英文字母表每个字符需要 1 个字节，但是像日语或中文这样的语言需要超过 1 个字节来存储一个字符。
当您指定varchar2(10)时，您是在告诉数据库仅存储10字节的数据。但是，当您说nVarchar2(10)时，这意味着将存储10个字符。在这种情况下，您不必担心每个字符占用的字节数。
nVarchar2 is a Unicode-only storage.
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.
When you specify varchar2(10), you are telling the DB that only 10 bytes of data will be stored. But, when you say nVarchar2(10), it means 10 characters will be stored. In this case, you don't have to worry about the number of bytes each character takes.