萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 伺服器教學

資料庫字串比對失敗?隱形特殊字元惹的禍

現象

兩筆肉眼看起來完全相同的字串(如 ABC123),在 SQL WHERE col = 'ABC123' 或應用程式比對時卻判定不相等。

根因

資料來源為 Excel 匯入,儲存格內含不可見的控制字元(如 CHAR(160) 不中斷空白、CHAR(9) Tab、CHAR(13) CR、CHAR(10) LF、Zero Width Space 等)。

偵測方法

  1. HEX 轉碼檢視:
    SELECT col, HEX(col) FROM table WHERE id = ...;
    

    正常 ASCII 字元每字元 1 byte;特殊字元會顯示額外位元組(如 C2A0 為 UTF-8 的 NBSP)。

  2. 長度比對:
    SELECT LENGTH(col), CHAR_LENGTH(col) FROM table ...;
    

    若 LENGTH > CHAR_LENGTH,提示有多位元組字元。

  3. Excel 端檢查:公式 =CODE(MID(A1,1,1)) 或複製到編輯器(Notepad++、VS Code)開啟「顯示所有字元」。

清理策略

-- 移除常見不可見字元
UPDATE table SET col = TRIM(BOTH CHAR(160) FROM col);
UPDATE table SET col = REPLACE(col, CHAR(9), '');   -- Tab
UPDATE table SET col = REPLACE(col, CHAR(13), '');  -- CR
UPDATE table SET col = REPLACE(col, CHAR(10), '');  -- LF

-- 通用:僅保留可列印 ASCII
UPDATE table SET col = REGEXP_REPLACE(col, '[[:cntrl:]]', '');

預防措施

  • 匯入前在 Excel 以 CLEAN()、TRIM()、SUBSTITUTE() 清洗。
  • ETL 流程加入資料品質檢核步驟(長度、HEX 掃描)。
  • 資料庫欄位設定 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin 區分大小寫與特殊字元。
copyright © 萬盛學電腦網 all rights reserved