NLS_NCHAR_CHARACTERSET 和 NLS_CHARACTERSET for Oracle 的区别

difference between NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET for Oracle

提问by Steven Tang Ti Khoon

i have a quick question here, that i would like to know the difference between NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET setting in oracle ??

我在这里有一个简单的问题，我想知道 oracle 中 NLS_NCHAR_CHARACTERSET 和 NLS_CHARACTERSET 设置之间的区别？

from my understanding NLS_NCHAR_CHARACTERSET is for NVARCHAR data types and for NLS_CHARACTERSET would be for VARCHAR2 data types.

根据我的理解， NLS_NCHAR_CHARACTERSET 用于 NVARCHAR 数据类型，而 NLS_CHARACTERSET 用于 VARCHAR2 数据类型。

i tried to test this on my development server which my current settings for CHARACTERSET is as the following :-

我尝试在我的开发服务器上对此进行测试，我当前的 CHARACTERSET 设置如下：-

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               US7ASCII

Then i inserted some Chinese character values into the database. i inserted the characters into a table called data_ and updated the column for ADDRESS and ADDRESS_2 which are VARCHAR2 columns. By right from my understanding with the current setting for NLS_CHARACTERSET US7ASCII , chinese characters should not be supported but it is still showing in the database ?? does NLS_NCHAR_CHARACTERSET take precedence over this ??

然后我在数据库中插入了一些汉字值。我将字符插入到名为 data_ 的表中，并更新了 ADDRESS 和 ADDRESS_2 列，它们是 VARCHAR2 列。根据我对 NLS_CHARACTERSET US7ASCII 当前设置的理解，不应该支持中文字符，但它仍然显示在数据库中？？NLS_NCHAR_CHARACTERSET 优先于这个吗？？

Thank You.

谢谢你。

采纳答案by Wernfried Domscheit

In general all your points are correct. NLS_NCHAR_CHARACTERSETdefines the character set for NVARCHAR2, et. al. columns whereas NLS_CHARACTERSETis used for VARCHAR2.

总的来说，你的所有观点都是正确的。NLS_NCHAR_CHARACTERSET定义NVARCHAR2, et的字符集。阿尔。列而NLS_CHARACTERSET用于VARCHAR2.

Why is it possible that you see Chinese characters with US7ASCII?

为什么你会看到带有 的汉字US7ASCII？

The reason is, your database character set and your client character set (i.e. see NLS_LANGvalue) are both US7ASCII. Your database uses US7ASCIIand it "thinks" also the client sends data using US7ASCII. Thus it does not make any conversion of the strings, the data are transferred bit-by-bit from client to server and vice versa.

原因是，您的数据库字符集和您的客户端字符集（即见NLS_LANG值）都是US7ASCII. 您的数据库使用US7ASCII并且它“认为”客户端也使用US7ASCII. 因此它不会对字符串进行任何转换，数据从客户端逐位传输到服务器，反之亦然。

Due to that fact you can use characters which are actually not supported by US7ASCII. Be aware, in case your client uses a different character set (e.g. when you use ODP.NET Managed Driver in an Windows application) the data will be rubbish! Also if you would consider a database character set migration you have the same issue.

因此，您可以使用实际上不支持的字符US7ASCII。请注意，如果您的客户端使用不同的字符集（例如，当您在 Windows 应用程序中使用 ODP.NET 托管驱动程序时），数据将是垃圾！此外，如果您考虑进行数据库字符集迁移，您也会遇到同样的问题。

Another note: I don't think you would get the same behavior with other character sets, e.g. if your database and your client both would use WE8ISO8859P1for example. Also be aware that you actually have wrong configuration. Your database uses character set US7ASCII, your NLS_LANGvalue is also US7ASCII(most likely it is not set at all and Oracle defaults it to US7ASCII) but the real character set of SQL*Plus, resp. your cmd.exeterminal is most likely CP950or CP936.

另一个注意事项：我认为您不会使用其他字符集获得相同的行为，例如，如果您的数据库和您的客户端都将使用WE8ISO8859P1例如。另请注意，您实际上有错误的配置。您的数据库使用 character set US7ASCII，您的NLS_LANG值也是US7ASCII（很可能根本没有设置，Oracle 默认为US7ASCII）但是 SQL*Plus 的真实字符集，resp。您的cmd.exe终端很可能是CP950或CP936。

If you like to set everything properly you can either set your environment variable NLS_LANG=.ZHT16MSWIN950(CP936 seems to be not supported by Oracle) or change your codepage before running sqlplus.exewith command chcp 437. With this proper settings you will not see any Chinese characters as you probably would have expected.

如果您想正确设置所有内容，您可以设置环境变量NLS_LANG=.ZHT16MSWIN950（Oracle 似乎不支持 CP936）或在sqlplus.exe使用 command运行之前更改代码页chcp 437。通过这种适当的设置，您将不会看到您可能预期的任何中文字符。