SQL国家字符(NCHAR)数据类型的真正用途是什么? [英] What's the SQL national character (NCHAR) datatype really for?

问题描述

以及CHAR (CHARACTER)和VARCHAR (CHARACTER VARYING)，SQL提供了NCHAR (NATIONAL CHARACTER)和NVARCHAR (NATIONAL CHARACTER VARYING)类型.在某些数据库中，这是用于字符(非二进制)字符串的更好的数据类型:

As well as CHAR (CHARACTER) and VARCHAR (CHARACTER VARYING), SQL offers an NCHAR (NATIONAL CHARACTER) and NVARCHAR (NATIONAL CHARACTER VARYING) type. In some databases, this is the better datatype to use for character (non-binary) strings:

• 在SQL Server中，NCHAR存储为UTF-16LE，并且是可靠存储非ASCII字符的唯一方法，CHAR仅是单字节代码页；

• In SQL Server, NCHAR is stored as UTF-16LE and is the only way to reliably store non-ASCII characters, CHAR being a single-byte codepage only;

在Oracle中，NVARCHAR可能存储为UTF-16或UTF-8而不是单字节排序规则；

In Oracle, NVARCHAR may be stored as UTF-16 or UTF-8 rather than a single-byte collation;

但是在MySQL中，NVARCHAR是VARCHAR，因此没有区别，任何一种类型都可以使用UTF-8或任何其他归类存储.

But in MySQL, NVARCHAR is VARCHAR, so it makes no difference, either type can be stored with UTF-8 or any other collation.

那么，NATIONAL在概念上实际上意味着什么(如果有的话)?供应商的文档仅告诉您他们自己的DBMS使用什么字符集，而不是实际的依据.同时，SQL92标准对该功能的解释甚至没有多大帮助，仅指出NATIONAL CHARACTER存储在实现定义的字符集中.与仅CHARACTER相对，后者存储在实现定义的字符集中.这可能是不同的实现定义的字符集.是否.

So, what does NATIONAL actually conceptually mean, if anything? The vendors' docs only tell you about what character sets their own DBMSs use, rather than the actual rationale. Meanwhile the SQL92 standard explains the feature even less helpfully, stating only that NATIONAL CHARACTER is stored in an implementation-defined character set. As opposed to a mere CHARACTER, which is stored in an implementation-defined character set. Which might be a different implementation-defined character set. Or not.

谢谢，ANSI.坦西.

Thanks, ANSI. Thansi.

是否应该将NVARCHAR用于所有字符(非二进制)存储目的?当前流行的DBMS是否会在其中做一些不良的事情，或者只是无法识别关键字(或N''文字)?

Should one use NVARCHAR for all character (non-binary) storage purposes? Are there currently-popular DBMSs in which it will do something undesirable, or which just don't recognise the keyword (or N'' literals)?

推荐答案

在这种情况下，"NATIONAL"表示特定于不同国籍的字符.远东语言尤其具有太多的字符，以至于一个字节不足以将所有字符区分开.因此，如果您只有英文(ascii)应用程序或只有英文字段，则可以使用较旧的CHAR和VARCHAR类型，每个字符只允许一个字节.

"NATIONAL" in this case means characters specific to different nationalities. Far east languages especially have so many characters that one byte is not enough space to distinguish them all. So if you have an english(ascii)-only app or an english-only field, you can get away using the older CHAR and VARCHAR types, which only allow one byte per character.

也就是说，大多数时候您应该使用NCHAR/NVARCHAR.即使您认为不需要在数据中支持(或潜在地支持)多种语言，即使是仅英语的应用程序也需要能够明智地使用外语字符来处理安全攻击.

That said, most of the time you should use NCHAR/NVARCHAR. Even if you don't think you need to support (or potentially support) multiple languages in your data, even english-only apps need to be able to sensibly handle security attacks using foreign-language characters.

我认为，唯一仍然首选较旧的CHAR/VARCHAR类型的唯一地方是在支持区分的Sql Server之类的平台上，频繁引用的纯ascii内部代码和数据.在客户端语言(如C ++或C#)中相当于enum的数据.

In my opinion, about the only place where the older CHAR/VARCHAR types are still preferred is for frequently-referenced ascii-only internal codes and data on platforms like Sql Server that support the distinction — data that would be the equivalent of an enum in a client language like C++ or C#.

这篇关于SQL国家字符(NCHAR)数据类型的真正用途是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！