SQL的where子句中的'N'

I saw this query somewhere -

我在某处见过这个查询

SELECT *
FROM HR.Employees
WHERE country <> N'JAP';

What does that "N" mean ? I am not sure if this is valid only for SQL server.

N是什么意思?我不确定这是否只对SQL server有效。

2 个解决方案

#1

The N stands for "National Character" and it means that the content of the string is Unicode.

N代表“国家字符”，意味着字符串的内容是Unicode。

You should be using Unicode (nchar/nvarchar) whenever you might come across proper names or other entities that can contain characters outside of the default ASCII character set. If you don't surround such strings with the N prefix, you will lose data. For example:

您应该使用Unicode (nchar/nvarchar)，当您遇到合适的名称或其他实体时，可以包含默认ASCII字符集以外的字符。例如:

SELECT N'ук ферт хер', 'ук ферт хер';

Results:

结果:

-----------        -----------
ук ферт хер        ?? ???? ???

You should also be sure to use the N prefix in your WHERE or other clauses against n(var)char columns. When you don't use the N prefix, you could suffer serious performance issues due to implicit conversion.

您还应该确保在您的WHERE或其他子句中使用N前缀(var)char列。当不使用N前缀时，由于隐式转换，您可能会遇到严重的性能问题。

#2

Additional information taken from the book - http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059

从这本书中获得的其他信息——http://www.amazon.com/training-kit - example - 70461-microsoft/dp/0735666059

If you write an expression that involves operands of different types, SQL Server will have to apply implicit conversion to align the types. Depending on the circumstances, implicit conversions can sometimes hurt performance. It is important to know the proper form of literals of different types and make sure you use the right ones. A classic example for using incorrect literal types is with Unicode character strings (NVARCHAR and NCHAR types).

如果您编写的表达式涉及不同类型的操作数，那么SQL Server将必须应用隐式转换来对齐类型。根据具体情况，隐式转换有时会影响性能。重要的是要知道不同类型的文字的正确形式，并确保使用正确的形式。使用不正确的文字类型的典型例子是使用Unicode字符字符串(NVARCHAR和NCHAR类型)。

The right form for a Unicode character string literal is to prefix the literal with a capital N and delimit the literal with single quotation marks; for example, N'literal'. For a regular character string literal, you just delimit the literal with single quotation marks; for example, 'literal'. It’s a very typical bad habit to specify a regular character string literal when the filtered column is of a Unicode type, as in the following example.

Unicode字符字符串文字的正确形式是在文字前面加上大写的N，并用单引号分隔文字;例如,N 'literal”。对于常规字符串字面量，只需用单引号分隔文字;例如,“文字”。当被过滤的列是Unicode类型时，指定一个常规字符串文字是一个非常典型的坏习惯，如下例所示。

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = 'Davis';

Because the column and the literal have different types, SQL Server implicitly converts one operand’s type to the other. In this example, fortunately, SQL Server converts the literal’s type to the column’s type, so it can still efficiently rely on indexing. However, there may be cases where implicit conversion hurts performance. It is a best practice to use the proper form, like in the following.

由于列和文字具有不同的类型，SQL Server隐式地将一个操作数类型转换为另一个操作数类型。在这个示例中，幸运的是，SQL Server将文字的类型转换为列的类型，因此它仍然可以有效地依赖于索引。但是，隐式转换可能会损害性能。使用适当的形式是最佳实践，如下所示。

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = N'Davis';

#1