Nie można zaktualizować „CO2” do „CO₂” w wierszu tabeli

Biorąc pod uwagę tę tabelę:

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');

Zdałem sobie sprawę, że nie mogę rozwiązać problemu typograficznego:

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

ponieważ aktualizacja jest zgodna, ale nie ma wpływu:

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

(1 affected rows)

(1 affected rows)

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

(1 affected rows)

To tak, jakby SQL Server stwierdził, że skoro ₂ to oczywiście tylko mała 2 , ostateczna wartość się nie zmieni, więc nie warto jej zmieniać.

Czy ktoś może rzucić na to trochę światła i może zasugerować obejście (inne niż aktualizacja wartości pośredniej)?

Indeks dolny 2 nie jest częścią zestawu znaków varchar (w żadnym zestawieniu, nie tylko Modern_Spanish). Niech więc będzie stałą nvarchar:

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

@gbn już wyjaśnił podstawową przyczynę i poprawkę, ale konkretny powód obserwowanego zachowania jest następujący:

1. Używasz VARCHARliterału (bez Nprefiksu) zamiast NVARCHARliterału (ciąg z Nprefiksem), dlatego znak Unicode zostanie przekonwertowany na VARCHAR.
2. VARCHARto 8-bitowe kodowanie, które w większości przypadków jest jednym bajtem na znak, ale może również wynosić dwa bajty na znak. Z drugiej strony NVARCHARjest to 16-bitowe kodowanie (UTF-16 Little Endian), które ma albo dwa bajty, albo cztery bajty na znak.
3. Ze względu na różnicę w liczbie dostępnych bajtów używanych do mapowania znaków, kodowanie 8-bitowe jest z natury znacznie bardziej ograniczone pod względem liczby znaków, które można zmapować. VARCHARdane mają do 256 znaków dla zestawów znaków jednobajtowych (większość z nich) i do 65 536 znaków dla zestawów znaków dwubajtowych (tylko kilka z nich). Z drugiej strony NVARCHARdane mogą zmapować nieco ponad 1,1 miliona znaków Unicode (choć obecnie prawie 250 tys. Mapowanych).
4. Ze względu na ograniczoną liczbę odwzorowań, które można wykonać za pomocą 8-bitów / VARCHARdanych, różne grupy znaków (w oparciu o język / kulturę) są rozłożone na wiele „stron kodowych” (tj. Zestawy znaków)
5. Każde sortowanie określa, która strona kodowa, jeśli w ogóle, ma być używana dla VARCHARdanych ( NVARCHARwszystkie znaki)
6. Podczas konwersji literału łańcucha lub zmiennej z NVARCHAR(tj. Unicode / UTF-16 / wszystkie znaki) na VARCHAR(zestaw znaków oparty na stronie kodowej, która jest określona w większości zestawień), używane jest domyślne sortowanie bazy danych
7. Jeśli strona kodowa sortowania zastosowana do konwersji nie zawiera tego samego znaku, ale zawiera odwzorowanie „najlepszego dopasowania”, zostanie zastosowane odwzorowanie „najlepszego dopasowania”.
8. Jeśli strona kodowa sortowania użyta do konwersji nie zawiera tego samego znaku lub zawiera odwzorowanie „najlepszego dopasowania”, zostanie użyty domyślny znak „zamienny” (najczęściej ?).

Więc, co widzisz jest NVARCHARdo VARCHARkonwersji ze względu na brakujące Nprefiks na ciągiem znaków. Strona kodowa domyślnego sortowania dla bazy danych nie zawiera dokładnie tego samego znaku, ale znaleziono mapowanie „najlepiej dopasowane”, dlatego otrzymujesz 2zamiast niego ?.

Możesz zobaczyć ten efekt, wykonując następujący prosty test:

SELECT '₂', N'₂';

Zwroty:

2    ₂

Dla jasności, JEŚLI strona kodowa domyślnego sortowania dla bazy danych zawiera dokładnie ten sam znak, wówczas zostałaby przetłumaczona na ten sam znak na tej stronie kodowej. A zatem, w twoim przypadku, ponieważ przechowujesz w NVARCHARkolumnie, ponownie przetłumaczyłaby to z powrotem na oryginalny znak Unicode. Ostatni przykład poniżej pokazuje to zachowanie.

WAŻNE: Należy pamiętać, że konwersja następuje w trakcie interpretacji literału łańcuchowego, czyli przed zapisaniem go w kolumnie. Oznacza to, że nawet jeśli kolumna może pomieścić ten znak, zostanie on już przekonwertowany na coś innego, na podstawie domyślnego sortowania bazy danych, a wszystko to z powodu pominięcia Nprzedrostka tego literału łańcucha. I właśnie tego doświadczasz (lub doświadczyłeś).

Na przykład, jeśli domyślnym zestawieniem bazy danych byłby jeden z zestawień koreańskich (jeden z czterech zestawów znaków dwubajtowych), nie zobaczyłbyś tego problemu, ponieważ znak „indeks dolny 2” jest dostępny w tym znaku zestaw (strona kodowa 949). Wypróbuj następujący test, aby zobaczyć (używa sortowania kolumny zamiast domyślnego sortowania bazy danych, ponieważ jest to łatwiejsze do pokazania):

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;

Zwroty:

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

Jak widać, Latin1_General Collations, które używają strony kodowej 1252 (tej samej strony kodowej, której Modern_Spanishużywają Collations) do VARCHARdanych, nie mają dokładnego dopasowania, ale mają mapowanie „najlepiej dopasowane” (to, co widzisz ). ALE koreańskie układy, które używają kodu Page 949 do VARCHARdanych, mają dokładne dopasowanie do znaku „Indeks dolny 2”.

Aby dodatkowo to zilustrować, możemy utworzyć nową bazę danych z domyślnym zestawieniem jednego z koreańskich zestawień, a następnie uruchomić dokładny kod SQL, o którym mowa w pytaniu:

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;

Zwroty:

id  description
1   CO2


id  description
1   CO₂

AKTUALIZACJA

Dla każdego, kto jest zainteresowany dowiedzieć się więcej o tym , co dokładnie się tutaj dzieje (tj. Wszystkie krwawe szczegóły), zapoznaj się z dwuczęściowym dochodzeniem, które właśnie zamieściłem:

• Które zestawienie służy do konwersji NVARCHAR na VARCHAR w GDZIE? (Część A z 2: „Kaczka”)

• Które zestawienie służy do konwersji NVARCHAR na VARCHAR w GDZIE? (Część B 2: „Królik”)