主页 / dba / 问题 / 191595
Accepted
Álvaro González
Asked: 2017-11-24 07:46:44 +0800 CST

无法在表格行中将“CO2”更新为“CO₂”

  • 772

鉴于此表：

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)

就好像 SQL Server 确定，因为2显然只是一个很小的​​2，所以最终值不会改变，所以不值得改变它。

有人可以对此有所了解，并可能提出解决方法（而不是更新到中间值）？

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

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

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

  2. @gbn 已经解释了基本原因并进行了修复，但是您所看到的行为的具体原因是这样的：

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

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

    您可以通过执行以下简单测试来查看此效果：

    SELECT '₂', N'₂';

    回报：

    2    ₂

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

    重要提示：请注意，转换是在解释字符串文字时发生的，这是在将其存储到列中之前。这意味着即使该列可以包含该字符，它也已根据数据库的默认排序规则转换为其他内容，这一切都是由于省略了N该字符串文字上的前缀。这正是你正在（或曾经）经历的。

    例如，如果您的数据库的默认排序规则是韩语排序规则之一（四个双字节字符集之一），那么您不会看到这个问题，因为“下标 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数据的韩语排序规则确实与“下标 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₂

    更新

    对于有兴趣了解更多关于这里到底发生了什么（即所有血腥细节）的人，请参阅我刚刚发布的两部分调查：

    • 22

相关问题