SQL Server Bulk Insert 能正確解釋某些 Unicode 字元，但不能正確解釋其他字元？

出於某種原因，MS SQL Server 2016 批量插入會誤解/翻譯 Unicode 字元：

• C9 (Is) 變為 2B (+)
• A1 (¡) 到 ED (í)
• A0 () 變成 E1 (á)
• AE (®) 變為 AB («)
• CB (Ë) 轉換為 2D (-)
• D1 (Ñ) 到 2D (-)
• 92 (’) 轉換為 C6 (Æ)
• 96 (-) 進 FB (û)

即 Notepad++ 和 xxd 顯示平面文件有 0xC9，但在批量插入表後顯示“+”，並在 SQL Server 中轉換為 varbinary 顯示為 0x2B。備份也有 0xC9。

我將 25 個平面文件批量插入 MS SQL Server 2016。它是 15Gb 數據，我正在使用管道 ( | ) 欄位分隔符和CRLF行分隔符。

我批量插入到我提供的備份的截斷結構中。當我與備份進行比較時，存在差異。注意：我必須等待 25 小時才能從數據源備份，但可以在 15 分鐘內獲取平面文件。

一些差異是可以接受的（查找並替換我正在應用於平面文件），但許多差異是由於 Unicode 字元被誤解所致。

一個範例表的結構是：

CREATE TABLE [dbo].[obfuscated_name](
   [ob_1] [int] NOT NULL,
   [ob_2] [int] NOT NULL,
   [ob_3] [int] NOT NULL,
   [ob_4] [nvarchar](300) NULL
) ON [PRIMARY]

數據庫排序規則是預設的：SQL_Latin1_General_CP1_CI_AS。沒有列有不同的排序規則。此排序規則應使用程式碼頁 1252，它應正確解釋我遇到問題的字元。

我的流程是針對不斷變化的生產數據執行的，所以我擔心可能會出現其他更改，我想知道問題的根源，而不是嘗試隔離問題並手動更新誤解。

這不是 SQL Server 中的錯誤（甚至在 Windows 中），也不是需要將文件轉換為另一種編碼的額外步驟（即轉換為“Unicode”，在 Windows 世界中意味著“UTF-16 Little端”）。這只是一個簡單的誤傳。

通信故障的來源（它總是一樣的，對 ;-) 僅僅是不同意源數據的性質。將字元數據從一個地方移動到另一個地方時，指定兩邊的編碼很重要。是的，SQL_Latin1_General_CP1_*排序規則使用程式碼頁 1252。但是，如果您不告訴BULK INSERT或BCP.exe源文件的程式碼頁是什麼，那麼他們將假定程式碼頁是系統預設值。

BULK INSERT的文件甚至指出（對於CODEPAGE =參數）：

‘OEM’（預設）= char、varchar或text數據類型的列從系統 OEM 程式碼頁轉換為 SQL Server 程式碼頁。

BCP.exe的文件狀態（用於-C開關）：

OEM = 客戶端使用的預設程式碼頁。如果未指定 -C，這是使用的預設程式碼頁。

Windows 的預設程式碼頁是（至少對於美國英語系統）：437。如果您在命令提示符中執行以下命令，您可以看到這一點：

C:\> CHCP

它將返回：

Active code page: 437

但是您的源文件沒有使用程式碼頁 437 編碼。它是使用程式碼頁 1252 編碼的。

所以這就是正在發生的事情：

1. 字節就是字節。表示字元數據的字節只能通過編碼來解釋。讀取文件的任何內容都不會從文件中讀取字元，它會讀取文件的字節並根據指定的編碼顯示字元。
2. BULK INSERT/BCP 讀取字節0xC9。0xC9顯示為É使用程式碼頁 1252 時。
3. BULK INSERT / BCP 沒有給出原始碼頁，因此它檢查程序的目前程式碼頁並被告知：437。
4. BULK INSERT / BCP 現在有一個字節0xC9用於程式碼頁437（顯示為╔，但 BULK INSERT / BCP 沒有顯示它，所以你不會看到這個）
5. BULK INSERT/BCP 使用指定程式碼頁1252的排序規則將此數據插入到列中。
6. SQL Server 發現傳入數據使用的程式碼頁與目標使用的程式碼頁不同，因此必須轉換傳入數據，以使字元看起來相同（盡可能），即使基礎值發生變化。
7. Code Page 437 到 Code Page 1252 的映射表明字節0xC9映射到字節0x2B。同樣，程式碼頁 437（顯示為）上的字節0xAE（位於程式碼頁 1252 上）映射到程式碼頁 1252 上的字節0xAB（因為它也顯示為）。®``«``«

以下範例顯示了問題中提到的所有字元的這種轉換：

DECLARE @CodePageConversion TABLE
(
  [ActualSource_CP1252] AS CONVERT(VARCHAR(10), CONVERT(BINARY(1),
                   [PerceivedSource_CP437])) COLLATE SQL_Latin1_General_CP1_CI_AS,

  [PerceivedSource_CP437] VARCHAR(10) COLLATE SQL_Latin1_General_CP437_CI_AS,

  [Source_Value] AS (CONVERT(BINARY(1), [PerceivedSource_CP437])),

  [Destination_CP1252] AS (CONVERT(VARCHAR(10), [PerceivedSource_CP437]
                 COLLATE SQL_Latin1_General_CP1_CI_AS)),

  [CP1252_Value] AS (CONVERT(BINARY(1), CONVERT(VARCHAR(10),
                 [PerceivedSource_CP437] COLLATE SQL_Latin1_General_CP1_CI_AS)))
);

INSERT INTO @CodePageConversion
VALUES      (0xC9), (0xA1), (0xA0), (0xAE), (0xCB), (0xD1), (0x92), (0x96);

SELECT * FROM @CodePageConversion;

返回：

ActualSource_CP1252  PerceivedSource_CP437  Source_Value  Destination_CP1252  CP1252_Value
É                    ╔                      0xC9          +                   0x2B
¡                    í                      0xA1          í                   0xED
                     á                      0xA0          á                   0xE1
®                    «                      0xAE          «                   0xAB
Ë                    ╦                      0xCB          -                   0x2D
Ñ                    ╤                      0xD1          -                   0x2D
’                    Æ                      0x92          Æ                   0xC6
–                    û                      0x96          û                   0xFB

程式碼頁 1252 中不存在 0xC9、0XCB 和 0xD1 的字元，因此使用了“最合適”的映射，這就是轉換後得到+and-字元的原因。

此外，即使目標列使用NVARCHAR，所有這些映射都是相同的，因此您會看到完全相同的行為。

所以，你的選擇是：

1. 如果使用 T-SQLBULK INSERT命令，請使用WITH CODEPAGE =以下值之一指定選項：

2. 'ACP'（這與 相同'1252'）

3. 'RAW'（如果插入，則使用列的排序規則的程式碼頁，或者插入時與 / 程式碼頁 437 VARCHAR相同）'OEM'``NVARCHAR

4. '1252'（這與 相同'ACP'）

5. 或者，如果使用BCP.exe，則通過命令行開關指示傳入文件使用程式碼頁 1252-C以及以下值之一（請參閱選項 #1 中的註釋）：

6. ACP

7. RAW

8. 1252

請注意：

1. 我使用問題中提到的字元集和插入列進行了測試**，並且BULK INSERT（我相信它代表A** NSI程式碼頁**）**、和值都產生了正確的結果。VARCHAR``ACP``RAW``1252
2. 未指定WITH CODEPAGE =產生的結果與 OP 在問題中報告的結果相同。這與指定WITH CODEPAGE = 'OEM'.
3. 當插入一NVARCHAR列時，兩者都ACP按1252需要工作，但RAW產生的結果相同OEM（即使用程式碼頁 437 而不是由列的排序規則指定的程式碼頁 1252）。
4. 我使用BCP.exe進行了測試，沒有指定-C開關沒有使用程序的程式碼頁。意思是，使用CHCP更改命令提示符的程式碼頁沒有效果：程式碼頁 437 仍然用作原始碼頁。

---

PS 由於這裡的數據都是 8 位編碼的，所以沒有“Unicode 字元”，因為沒有使用 Unicode。

引用自：https://dba.stackexchange.com/questions/204861