无法将表行中的"CO2“更新为"CO₂”

Question

鉴于这一表：

CREATE TABLE test (
    id INT NOT NULL,
    description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');

我意识到我无法解决排版问题：

SELECT * FROM test WHERE id = 1;
UPDATE test SET description = 'CO₂' WHERE id = 1;
SELECT * FROM test WHERE id = 1;

因为更新匹配，但没有任何效果：

id          description
----------- -----------
1           CO2

(1 affected rows)

(1 affected rows)

id          description
----------- -----------
1           CO2

(1 affected rows)

似乎Server决定了这一点，因为₂显然只是一个很小的2，所以最终值不会改变，因此不值得更改它。

有人能解释一下这一点并提出解决办法(除了更新到中间值之外)吗？

Answer 1

下标2不是varchar字符集的一部分(在任何排序规则中，不仅仅是Modern_Spanish)。所以让它成为一个nvarchar常量：

UPDATE test SET description = N'CO₂' WHERE id = 1;

Answer 2

@gbn已经解释了基本原因和修复，但是您所看到的行为的具体原因是：

1. 您使用的是VARCHAR文本(没有N前缀)，而不是NVARCHAR文本(带有N前缀的字符串)，因此Unicode字符将被转换为VARCHAR。
2. VARCHAR是一种8位编码，在大多数情况下，每个字符只有一个字节，但也可以是每个字符两个字节。另一方面，NVARCHAR是一种16位编码(UTF-16小端点)，每个字符要么是两个字节，要么是四个字节。
3. 由于用于映射字符的可用字节数的不同，8位编码从本质上说，在可以映射的字符数量上受到了更大的限制。VARCHAR数据为单字节字符集最多256个字符(其中大多数)，双字节字符集最多可达65,536个字符(其中只有少数字符)。另一方面，NVARCHAR数据可以映射110万多个Unicode字符(尽管当前映射的字符不到250 k)。
4. 由于使用8位/ VARCHAR数据可以完成的映射数量有限，不同的字符分组(基于语言/文化)分布在多个“代码页”(即字符集)中。
5. 每个排序规则指定用于VARCHAR数据的代码页(NVARCHAR为所有字符)。
6. 将字符串文字或变量从NVARCHAR (即Unicode / UTF-16 / all字符)转换为VARCHAR (基于代码页的字符集，在大多数排序规则中指定)时，使用数据库的默认排序规则
7. 如果用于转换的排序规则的代码页不包含相同的字符，而是包含“最佳匹配”映射，则将使用“最佳匹配”映射。
8. 如果用于转换的排序规则的代码页不包含相同的字符或包含“最佳匹配”映射，则将使用默认的“替换”字符(最常见的是?)。

因此，您所看到的是由于字符串文本中缺少NVARCHAR前缀而导致的N到VARCHAR的转换。而且，数据库的默认排序规则的代码页不包含完全相同的字符，但是找到了“最佳匹配”映射，这就是为什么要获得2而不是?。

通过执行以下简单的测试，您可以看到这种效果：

SELECT '₂', N'₂';

返回：

2    ₂

要明确的是，如果数据库的默认排序规则的代码页确实包含完全相同的字符，那么它将在该代码页中转换为相同的字符。然后，在您的示例中，由于存储在NVARCHAR列中，它将再次转换回原来的Unicode字符。下面的最后一个示例显示了这种行为。

重要事项:请注意，转换是在字符串文字被解释时发生的，这是在将字符串存储到列之前。这意味着，即使列可以保存该字符，它也已经根据数据库的默认排序规则被转换为其他字符，这一切都是由于去掉了字符串文本上的N前缀。这正是你正在(或曾经)所经历的。

例如，如果数据库的默认排序规则是朝鲜语排序规则之一(四个双字节字符集之一)，那么您就不会看到这个问题，因为"Subscript 2“字符在该字符集中可用(代码页949)。尝试下面的测试来查看(它使用列的排序规则而不是数据库的默认排序规则，因为这样更容易显示)：

CREATE TABLE #TestChar
(
    [8bit_Latin1_General-1252] VARCHAR(2) COLLATE Latin1_General_100_CI_AS_SC,
    [8bit_Korean-949] VARCHAR(2) COLLATE Korean_100_CI_AS_SC,
    [UTF16LE_Latin1_General-1252] NVARCHAR(2) COLLATE Latin1_General_100_CI_AS_SC
);

INSERT INTO #TestChar VALUES (N'₂', N'₂', N'₂');

SELECT * FROM #TestChar;

返回：

8bit_Latin1_General-1252    8bit_Korean-949    UTF16LE_Latin1_General-1252
2                           ₂                  ₂

如您所见，Latin1_General排序规则使用代码页1252 (与Modern_Spanish排序规则使用的代码页相同)对VARCHAR数据没有完全匹配，但它们确实具有“最佳匹配”映射(这就是您正在看到的)。但是，使用代码页949处理VARCHAR数据的朝鲜语排序规则确实与"Subscript 2“字符完全匹配。

为了进一步说明，我们可以创建一个包含韩国排序规则的默认排序规则的新数据库，然后运行问题中的确切SQL：

CREATE DATABASE [TestKorean-949] COLLATE Korean_100_CI_AS_KS_WS_SC;
ALTER DATABASE [TestKorean-949] SET RECOVERY SIMPLE;
GO

USE [TestKorean-949];

CREATE TABLE test (
    id INT NOT NULL,
    description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');


SELECT * FROM test WHERE id = 1;
UPDATE test SET description = 'CO₂' WHERE id = 1;
SELECT * FROM test WHERE id = 1;

返回：

id  description
1   CO2


id  description
1   CO₂

更新

任何有兴趣了解这里到底发生了什么(即所有血淋淋的细节)的人，请看我刚刚发布的两部分调查：

• 哪个排序规则用于在何处条件下将NVARCHAR转换为VARCHAR？(第2部A部：“鸭”)
• 哪个排序规则用于在何处条件下将NVARCHAR转换为VARCHAR？(第2部B部：“兔子”)