令人火大的 SQL 字元編碼…
摘要提示
- 外包協作問題: 客戶 IT 委外導致責任分散、問題難以追根究柢，影響問題處理效率。
- 資料搬移場景: 透過 Linked Server 將 A 系統資料複製到中繼庫再清洗，最後匯入 B 系統。
- 中文編碼亂碼: 兩端資料庫中文字編碼不同，直接 SELECT 產生亂碼。
- 初步解法 convert: 將欄位 convert 成 ntext 後，初步能正確顯示中文。
- 異常的資料錯亂: 使用 SELECT INTO 後出現「錯字混入」與「上一筆資料殘留」的怪異現象。
- 排除定序與編碼: 既能正確 SELECT，推論非定序/編碼設定問題，而是更底層的錯誤。
- 溢位推測: 行為酷似緩衝區溢位或未正確零結尾，導致資料被前一筆殘值污染。
- 可能的 SQL 元件 Bug: 懷疑 SQL Server 或 SQL Native Client 在特定操作路徑有缺陷。
- 迴避式解法: 以 CURSOR 逐筆 FETCH 至 nvarchar 變數再 UPDATE/寫回，成功避開問題。
- 務實處理建議: 在無法更新修補環境下，採可行繞道解法，不與系統底層硬碰硬。
全文重點
作者敘述一段在客戶專案中處理資料搬移時遇到的棘手經驗。情境是把客戶 A 系統的資料透過 Linked Server 複製到中繼資料庫，進行整理後再匯入 B 系統。最初遇到的是常見的中文亂碼問題，推測為兩端資料庫字元編碼不一致，於是透過在中繼庫將欄位 convert 成 ntext，成功讓 SELECT 結果顯示正確中文，看似解決。
然而在將 SELECT 改為 SELECT INTO 暫存表、進一步處理時，卻出現更詭異的現象：不是簡單的「變成亂碼或問號」，而是「資料錯置」。具體表現為某筆資料的內容部分被上一筆資料覆蓋，還混入更前面資料的殘字，像是緩衝區沒清空導致舊值滲入。作者將原因拆解：既然直接 SELECT 正確，且若僅是編碼/定序不合，理應只是字形錯亂或問號替代，不可能冒出「上一筆內容」；因此更像底層 Buffer 使用錯誤、字串結尾 0x00 未正確處理、或長度計算出包，導致 overflow/overread 之類的問題。
由於自身不專精資料庫內部與驅動層級工具，也不願在委外多方環境中追查到元兇（且客戶端不願更新或套用修補），作者選擇務實繞路：放棄批次 SELECT INTO 的作法，改用 CURSOR 逐筆 FETCH，將欄位拉進 nvarchar 變數後再寫回更新。這種笨但穩健的方式成功繞過了疑似 SQL Server 或 SQL Native Client 在特定路徑的 Bug，避免資料遭到前筆殘留污染。結尾給出心得：面對多包商、難以控管版本與修補的現場，與其硬攻編碼/定序，倒不如精準判斷可能的層級問題，採取能在現場落地的可靠繞道方案，避免把時間耗在難以改變的外部條件上。
段落重點
問題背景與協作困境
作者受同事之託排除資料搬移問題，場景為 A 系統資料需清洗後匯入 B 系統。因客戶 IT 委外，責任界線模糊，遇到跨系統問題時常成為踢皮球，缺乏快速決策與修補的條件。整體流程是透過 Linked Server 從 A 系統資料庫把整張表 SELECT 到中繼資料庫，再做一系列修正，最後再進 B 系統。這種跨系統、跨團隊又跨資料庫的多節點流程，使任何小瑕疵都可能放大為難以歸因與協調的現場問題。
初始亂碼與第一階段處置
第一步遇到的是中文亂碼：兩端資料庫對中文字編碼/型別不一致，直接 SELECT 就出現錯亂字元。作者請對方 IT 確認編碼差異，並在中繼資料庫側進行調整，將欄位 convert 為 ntext。此舉讓資料在查詢顯示時恢復正確，表面上問題已解。依常理，若只是編碼/定序不合，最多會見到亂碼或問號替代，調整型別即可解套。到此為止，是一個常見且可預期的資料型別/編碼兼容處理案例。
更離奇的資料錯亂與線索
當把流程改為 SELECT INTO 寫入暫存表，再取出處理時，出現離奇的「資料被污染」：某筆資料的第二字原本會變問號，現在卻被上一筆的內容蓋掉，甚至第三個字還殘留更前面記錄的字。這種「上一筆資料殘留」「片段字串拼接」的現象，不像編碼錯誤造成的亂碼，而更像記憶體緩衝處理不當：可能是緩衝區未清空、長度計算錯或未正確以 0x00 結束字串，導致讀取超出邊界，把前一筆或更早的殘值帶進來。且直接 SELECT 一切正常，唯獨 SELECT INTO 的路徑失真，提供了關鍵線索。
推理排除與對底層 Bug 的懷疑
作者採取排除法：既然直接 SELECT 能正確呈現中文，就不太可能是定序或編碼設定本身錯；若是型別不合，典型結果是亂碼或問號而非「上一筆覆蓋」。行為模式高度近似程式語言中的 buffer overflow/overread 或未清理的工作區被重複使用。因此作者傾向判定是 SQL Server 或 SQL Native Client 在特定操作（例如跨 Linked Server 的 SELECT INTO、特定欄位型別組合）下的缺陷。因不打算投入大量時間做底層 trace，也無法要求客戶環境立即升級或套用修補，決定放棄硬攻底層原因。
迴避式解法與實務結論
最終策略是繞行：不用批次 SELECT INTO，而改用 CURSOR 逐筆 FETCH 欄位至 nvarchar 變數，再 UPDATE/寫回暫存表。這種「笨方法」把每筆資料明確經過受控的字串變數，成功避免緩衝區殘值污染，結果完全正確，且無需調整定序或編碼設定。作者以此驗證問題極可能在某路徑的底層實作。結論是，在外在條件不利（多包商協作、環境不更新）的現場，若判斷是底層 Bug，與其死磕，不如採可維護、可落地、風險可控的繞道解法，確保交付與品質。
資訊整理
知識架構圖
- 前置知識：
- 基本 SQL Server 操作（SELECT、SELECT INTO、UPDATE）
- 字元編碼與資料型別（varchar/nvarchar、text/ntext、Unicode 概念）
- Linked Server 基本觀念與資料來源/目的端差異
- Collation（定序）對字串比較與儲存的影響（基本認識即可）
- 實際案例的核心概念與關係：
- 編碼不一致：來源 A 系統與目的 B 系統中文編碼不同，直接 SELECT 會產生亂碼
- 轉型處理：在中繼庫以 CONVERT 成 ntext/nvarchar，直接 SELECT 可正確顯示
- 異常發生點：SELECT INTO 暫存表時出現資料「錯置」與「串接殘留」的現象（像上一筆資料殘影）
- 問題判斷：較像驅動/伺服端的緩衝處理 Bug（類 buffer overflow/未清空緩衝）
- 解法策略：改變資料搬運路徑與機制（改用 CURSOR 逐筆 FETCH 至 nvarchar 變數再寫回）繞過問題
- 技術依賴：
- Linked Server 依賴 OLE DB/Native Client 驅動與其對編碼的處理
- SELECT INTO 依賴目標資料庫/暫存表的預設資料型別與定序
- Unicode 正確性依賴使用 n 字首型別（nvarchar/ntext）與正確轉型
- 整體流程依賴「來源端編碼 → 中繼庫轉型 → 目的端載入」的每一段處理一致性
- 應用場景：
- 跨系統/跨資料庫資料交換（特別是含中文/多語言）
- 以中繼資料庫清洗資料後再匯入目標系統
- 在無法即時升級/打補丁的環境，需以工法繞過底層 Bug 的情境
- 需快速定位並排除資料「變亂碼」vs「資料錯置」的實務除錯
學習路徑建議
- 入門者路徑：
- 了解 varchar vs nvarchar、text/ntext 與 Unicode 基本概念
- 練習在 SQL Server 中以 CONVERT/CAST 進行字串型別轉換
- 嘗試在本機做一個簡單 Linked Server 或模擬來源/目的資料庫的搬移
- 進階者路徑：
- 熟悉 Linked Server 的驅動/Provider 設定與資料型別對應
- 掌握 SELECT INTO 與建立暫存表時的型別/定序落地規則
- 練習將大型 SQL Script 簡化為最小重現案例（min repro）以定位問題
- 學習替代資料搬運方式（BULK INSERT、OPENQUERY、BCP、CURSOR）
- 實戰路徑：
- 建立中繼表並明確指定 nvarchar/ntext 型別，測試 SELECT 與 SELECT INTO 成果差異
- 若出現資料錯置，嘗試以 CURSOR 逐筆 FETCH 至 nvarchar 變數再 UPDATE 寫回以驗證是否為底層搬運問題
- 設計多條搬運管道（直選、SELECT INTO、INSERT…SELECT、OPENQUERY）比對結果並擇可行路徑
- 撰寫操作手冊與落地檢核清單，並與客戶/外包協調更新或長期繞道方案
關鍵要點清單
- 編碼不一致的兩類結果：亂碼 vs 資料錯置 (優先級: 高)
- Unicode 型別的重要性（nvarchar/ntext）的使用時機 (優先級: 高)
- SELECT 與 SELECT INTO 行為可能不同，尤其在型別落地與定序 (優先級: 高)
- Linked Server 驅動/Provider 對字元資料的影響 (優先級: 高)
- 以最小重現案例定位問題的思路與步驟 (優先級: 高)
- 資料錯置像「緩衝未清」的症狀辨識（上一筆殘影、字串被覆蓋） (優先級: 高)
- 以轉型 CONVERT 為 ntext/nvarchar 作為初步修正 (優先級: 中)
- 用 CURSOR 逐筆 FETCH → 變數 → UPDATE 的繞道工法 (優先級: 高)
- 不輕易與定序/編碼設定硬碰硬的決策判斷（時間與風險考量） (優先級: 中)
- 中繼資料庫在跨系統資料清洗/轉換中的角色 (優先級: 中)
- 觀察「直接 SELECT 正確、SELECT INTO 錯誤」的對比診斷法 (優先級: 高)
- 外部環境限制（無法升級/打補丁）下的工程折衷 (優先級: 中)
- 多種搬運策略的 A/B 測試思維（INSERT…SELECT、OPENQUERY、BCP） (優先級: 中)
- 指定目標欄位型別與定序以避免隱性推斷造成誤差 (優先級: 中)
- 與外包/客戶溝通時保留可驗證證據與替代方案 (優先級: 低)
