Oracle的NLS_NCHAR_CHARACTERSET和NLS_CHARACTERSET之间的区别 [英] difference between NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET for Oracle

问题描述

我在这里有一个快速的问题，我想知道两者之间的区别 oracle中的NLS_NCHAR_CHARACTERSET和NLS_CHARACTERSET设置??

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

NLS_NCHAR_CHARACTERSET用于NVARCHAR数据类型 对于NLS_CHARACTERSET，将用于VARCHAR2数据类型.

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

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

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

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

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

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 ??

谢谢.

推荐答案

通常，您的所有观点都是正确的. NLS_NCHAR_CHARACTERSET定义NVARCHAR2等的字符集.等列，而NLS_CHARACTERSET用于VARCHAR2.

In general all your points are correct. NLS_NCHAR_CHARACTERSET defines the character set for NVARCHAR2, et. al. columns whereas NLS_CHARACTERSET is used for VARCHAR2.

为什么用US7ASCII可以看到中文字符?

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

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

The reason is, your database character set and your client character set (i.e. see NLS_LANG value) are both US7ASCII. Your database uses US7ASCII and 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.

由于这个事实，您可以使用US7ASCII实际不支持的字符.请注意，如果您的客户端使用其他字符集(例如，当您在Windows应用程序中使用ODP.NET托管驱动程序时)，数据将是垃圾！另外，如果您考虑迁移数据库字符集，则会遇到相同的问题.

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.

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

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 WE8ISO8859P1 for example. Also be aware that you actually have wrong configuration. Your database uses character set US7ASCII, your NLS_LANG value 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.exe terminal is most likely CP950 or CP936.

如果要正确设置所有内容，则可以设置环境变量NLS_LANG=.ZHT16MSWIN950(Oracle似乎不支持CP936)，也可以在使用命令chcp 437运行sqlplus.exe之前更改代码页.使用此适当的设置，您将不会看到您可能期望的任何中文字符.

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.exe with command chcp 437. With this proper settings you will not see any Chinese characters as you probably would have expected.

这篇关于Oracle的NLS_NCHAR_CHARACTERSET和NLS_CHARACTERSET之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！