Oracle VARCHAR2 位元組與字元語義完解：NLS_LENGTH_SEMANTICS 參數深度解析

核心結論

VARCHAR2(20) 與 VARCHAR2(20 BYTE) 是否相同，取決於資料庫參數 NLS_LENGTH_SEMANTICS：

• BYTE (預設)：長度單位為位元組。
• CHAR：長度單位為字元數 (不論中英數各佔 1 字元)。

參數值為 BYTE 時兩者相等；為 CHAR 時不相等。

參數查詢與修改

-- 查詢目前設定
SHOW PARAMETER nls_length_semantics;
SHOW PARAMETER nls_language;

-- 範例輸出
-- 生產庫: NLS_LENGTH_SEMANTICS = BYTE, NLS_LANGUAGE = SIMPLIFIED CHINESE
-- 測試庫: NLS_LENGTH_SEMANTICS = BYTE, NLS_LANGUAGE = AMERICAN

注意： 參數修改僅影響後續建立的欄位，現有欄位定義不變。

實測驗證

資料表結構

PERSON VARCHAR2(32)

測試資料

'二郎公交車站對面18355200098代簽'  -- 21 字元 (含中文、數字)

查詢語句

SELECT PERSON
FROM temp_opt_order_trace
WHERE LENGTH(PERSON) = 21;  -- LENGTH 回傳字元數

結果分析

• LENGTH() 回傳 21 → 字元長度 21 (中文、英文、數字各算 1)。
• VARCHAR2(32) 以字元為單位可存入，不受 NLS_LENGTH_SEMANTICS=BYTE 影響 (11.2.0.1 實測)。
• 實際儲存位元組數：中文 2 bytes (AL32UTF8 為 3 bytes)、英數 1 byte。

字元集對儲存體的影響

最佳實務建議

1. 明確宣告單位：建立表時習慣寫 VARCHAR2(20 CHAR) 或 VARCHAR2(20 BYTE)，避免依賴參數預設值造成遷移差異。
2. 統一字元集：新專案統一採用 AL32UTF8，支援全語系、Emoji、補充字元。
3. 長度預留：中文業務欄位建議以 CHAR 語義給予 2~3 倍緩衝 (如身分證 20 CHAR、地址 200 CHAR)。
4. 跨環境部署：匯出 DDL 時使用 DBMS_METADATA 保留明確語義，避免目標庫參數不同導致結構不一致。

常見誤區釐清

快速查詢腳本

-- 列出所有 VARCHAR2/CHAR 欄位的長度語義
SELECT owner, table_name, column_name, data_type, char_length, char_used
FROM all_tab_columns
WHERE data_type IN ('VARCHAR2','CHAR')
ORDER BY owner, table_name;

CHAR_USED = 'B' 表 BYTE，'C' 表 CHAR。