了解SQL Server密钥和比较

In my SQL Server 2014, I have a table with a varchar/nvarchar (tested both, the outcome is the same!) column that is the primary key. In fact, the column values have to be unique and are used for about 50% of the selections, so it seems to make sense to make it the PK. The column is right now set to Collation <database default>, which should be Latin1_General_AS, not sure whether collation is the issue here.

在我的SQL Server 2014中，我有一个表，其中包含varchar / nvarchar（已测试两者，结果相同！）列是主键。实际上，列值必须是唯一的，并且用于大约50％的选择，因此将其作为PK似乎是有意义的。该列现在设置为Collation ，它应该是Latin1_General_AS，不确定排序规则是否是此处的问题。

Into that varchar column I insert a value with special character, in this example, the Polish(?) Osioł (but I want to support all characters which are allowed in LDAP CN Names in the future):

进入该varchar列我插入一个具有特殊字符的值，在本例中，波兰语（？）Osioł（但我想支持将来在LDAP CN名称中允许的所有字符）：

INSERT INTO Names (Name, Mail) VALUE('Osioł', 'osiol@test.local');

When I now try to update that value:

当我现在尝试更新该值时：

UPDATE Names SET Mail='osiol2@test.local' WHERE Name='Osioł';

it returns zero changed rows. Which, for me, up to now meant that the key is not yet in use, and that I am able to insert. Which I subsequently try, and fail with the error

它返回零更改的行。对我来说，到目前为止意味着密钥尚未使用，并且我能够插入。我随后尝试了，并且错误地失败了

Violation of PRIMARY KEY constraint 'PK_Names'. Cannot insert duplicate key in object 'dbo.Names'. The duplicate key value is Osioł.

违反PRIMARY KEY约束'PK_Names'。无法在对象'dbo.Names'中插入重复键。重复的键值是Osioł。

The same goes for selects:

选择同样如此：

SELECT * FROM Names WHERE Name='Osioł';

won't return the row.

不会返回该行。

How can I select/update that row using the PK with special chars?

如何使用带有特殊字符的PK选择/更新该行？

2 个解决方案

#1

Adding to Sean Lange's answer your insert should also be:

除了Sean Lange的回答，您的插入也应该是：

INSERT INTO Names (Name, Mail) VALUES(N'Osioł', 'osiol@test.local');

because the N specifies it to be a Unicode character.

因为N指定它是Unicode字符。

then this will work:

然后这将工作：

UPDATE #Names
  SET  Mail = 'osiol2@test.local'
WHERE  Name = N'Osioł';

and if you are using a select it has to be:

如果你使用选择它必须是：

SELECT #Names.Name
    , #Names.Mail
FROM   #Names
WHERE  Name = N'Osioł';

Below should explain what is happening:

下面应该解释发生了什么：

SAMPLE DATA:

样本数据：

IF OBJECT_ID('tempdb..#Names') IS NOT NULL
    BEGIN
       DROP TABLE #Names;
    END;

CREATE TABLE #Names(Name NVARCHAR(50)
               , Mail NVARCHAR(50));

INSERT INTO       #Names(Name
                   , Mail)
VALUES
      (N'Osioł'
     , 'osiol@test.local');


INSERT INTO       #Names(Name
                   , Mail)
VALUES
      ('Osioł'
     , 'osiol@test.local');

SHOW INSERTED VALUES IN THE TABLE:

在表中显示插入的值：

SELECT #Names.Name 
    , #Names.Mail
FROM   #Names;

UPDATE THE UNICODE VALE WITH POLISH CHARACTER ONLY:

更新仅具有波兰字符的UNICODE VALE：

UPDATE #Names
  SET  Mail = 'osiol2@test.local'
WHERE  Name = N'Osioł';

SHOW RESULTS:

显示结果：

SELECT #Names.Name
    , #Names.Mail
FROM   #Names
WHERE  Name = N'Osioł';

SELECT #Names.Name
    , #Names.Mail
FROM   #Names
WHERE  Name = 'Osioł';

As you can see without the N the server looks up the VARCHAR value rather than the unicode NVARCHAR

如您所见，没有N，服务器会查找VARCHAR值而不是unicode NVARCHAR

#2

You need to explicitly state that your string literal is nvarchar. Notice the N at the beginning of the string.

您需要明确声明您的字符串文字是nvarchar。注意字符串开头的N.

UPDATE Names SET Mail='osiol2@test.local' WHERE Name=N'Osioł';

#1