Oracle SUBSTR/LENGTH 字元語意 vs 位元組語意：中英混雜資料的致命陷阱
系統割接時用 SUBSTR 處理中英混合欄位，定義 VARCHAR2(30) 變數卻報「字串緩衝區不足」，MAX(LENGTH(col)) 卻只有 20。根因：**SUBSTR/LENGTH 計算字元數，VARCHAR2(n) 限制位元組數**，中文字佔 2-3 位元組導致實際位元組長度超過 30。本文詳細解析差異與正確處理方式。
問題重現
-- 變數定義
V_VAR VARCHAR2(30); -- 限制 30 位元組
-- 資料：中英混合，如 'ABC中文DEF' (3+2*2+3=10 字元，3+2*2+3=10 位元組? 錯！中文 2-3 bytes)
-- 實際：'中' 在 ZHS16GBK 佔 2 bytes，AL32UTF8 佔 3 bytes
-- 錯誤寫法
V_VAR := SUBSTR(MIXED_COL, 1, 30); -- 取前 30 字元，可能超過 30 bytes
-- 報錯：ORA-06502: PL/SQL: numeric or value error: character string buffer too small
-- 困惑點
SELECT MAX(LENGTH(MIXED_COL)) FROM TBL; -- 回傳 20 (字元數)
-- 以為 20 < 30 應該安全，實則位元組數可能 > 30
核心差異對照表
| 函數/定義 | 語意 | 單位 | 中文佔用 (GBK/UTF8) |
|---|
VARCHAR2(n) | 位元組長度上限 | Byte | 2 / 3 |
LENGTH(str) | 字元數 | Char | 1 |
LENGTHB(str) | 位元組數 | Byte | 2 / 3 |
SUBSTR(str, pos, len) | 字元位置/長度 | Char | 1 |
SUBSTRB(str, pos, len) | 位元組位置/長度 | Byte | 2 / 3 |
正確解決方案
- 檢查位元組長度：
SELECT MAX(LENGTHB(MIXED_COL)) FROM TBL; -- 看實際位元組數
-- 若 > 30，需擴大變數定義或截取位元組
- 用 SUBSTRB 截取位元組：
V_VAR := SUBSTRB(MIXED_COL, 1, 30); -- 保證不超過 30 bytes
-- 缺點：可能截斷中文字 (半個中文字元) 產生亂碼
- 安全截取：字元語意 + 位元組檢查：
DECLARE
V_TEMP VARCHAR2(100);
V_RESULT VARCHAR2(30);
BEGIN
-- 先取字元語意的前 30 字元
V_TEMP := SUBSTR(MIXED_COL, 1, 30);
-- 再檢查位元組長度，超長則遞減字元數
WHILE LENGTHB(V_TEMP) > 30 LOOP
V_TEMP := SUBSTR(V_TEMP, 1, LENGTH(V_TEMP) - 1);
END LOOP;
V_RESULT := V_TEMP;
END;
- 定義變數用 CHAR 語意 (12c+)：
V_VAR VARCHAR2(30 CHAR); -- 明確指定 30 字元，Oracle 自動換算位元組
-- 等同於
V_VAR VARCHAR2(30 BYTE); -- 預設
字符集影響對照
| 字符集 | 英文/數字 | 中文/日文/韓文 | Emoji/罕用字 |
|---|
| ZHS16GBK / ZHT16MSWIN950 | 1 Byte | 2 Bytes | 不支援 |
| AL32UTF8 (UTF-8) | 1 Byte | 3 Bytes | 4 Bytes |
| UTF16 (NCHAR/NVARCHAR2) | 2 Bytes | 2 Bytes | 4 Bytes |
最佳實踐總結
- 新專案統一 AL32UTF8 + VARCHAR2(n CHAR)：從源頭消除字元/位元組困擾
- 舊系統遷移：全面掃描
SUBSTR/LENGTH 用法，改為 SUBSTRB/LENGTHB 或加入位元組檢查邏輯
- 介面契約：API 文檔明確標註欄位長度單位 (字元/位元組)
- 測試資料：必須包含中英混合、邊界長度、特殊字元測試案例
