在 PL/SQL 開發與資料庫設計中,字串型別的選擇直接影響記憶體使用、比較語意、效能與資料完整性。CHAR、VARCHAR2、CHARACTER 三者看似相近,實則在儲存機制、比較規則、子型別關係上有本質差異。本文透過實測程式碼與輸出結果,深度解析這些差異,助您做出正確的型別選擇。
| 特性 | CHAR(n) | VARCHAR2(n) |
|---|---|---|
| 長度特性 | 固定長度,不足右補空格 | 變動長度,依實際資料長度儲存 |
| 記憶體分配時機 | 宣告時立即分配完整長度 | 執行期動態分配 |
| 最大長度 (PL/SQL) | 32,767 bytes | 32,767 bytes |
| 最大長度 (SQL/資料欄) | 2,000 bytes | 4,000 bytes (12c+ MAX_STRING_SIZE=EXTENDED 可至 32K) |
| 比較語意 | Blank-padded comparison(補空格比較) | Non-padded comparison(精確比較) |
| 儲存空間 | 固定佔用 n bytes | 實際長度 + 1~2 bytes 長度前綴 |
| 比較組合 | 比較規則 | 範例結果 |
|---|---|---|
| CHAR vs CHAR / 常數 | Blank-padded:右補空格至同長再比較 | 'A' = 'A ' → TRUE |
| VARCHAR2 vs VARCHAR2 / CHAR / 常數 | Non-padded:精確比較,不補空格 | 'A' = 'A ' → FALSE |
| CHAR vs VARCHAR2 | Non-padded(Oracle 將 CHAR 隱式轉為 VARCHAR2 比較) | CHAR_COL = 'A' 若資料為 'A ' → FALSE |
CREATE TABLE tt (a1 CHAR(2), a2 VARCHAR2(2));
INSERT INTO tt VALUES ('A', 'A');
INSERT INTO tt VALUES ('A ', 'A '); -- 第二筆 a1='A ', a2='A '
-- DECODE 對 CHAR 欄位比較時,將常數視為 VARCHAR2 處理(不補空格)
SELECT DECODE(a1, 'A', 'AAAA', 'BBBB') FROM tt;
-- 結果: 兩筆皆回傳 'BBBB' !因為 'A' != 'A ' (非補空格比較)
-- CASE 遵循 SQL 比較規則,CHAR 欄位補空格比較
SELECT CASE a1 WHEN 'A' THEN 'AAA' ELSE 'BBB' END FROM tt;
-- 結果: 兩筆皆回傳 'AAA' !因為 'A' 補空格後 = 'A '
結論: DECODE 內部將參數視為 VARCHAR2 處理,不遵循 CHAR 補空格比較規則;CASE 表達式則遵循標準 SQL 比較語意。這是極易造成 Bug 的陷阱。
CREATE TABLE t1 (name CHAR(10));
CREATE TABLE t2 (name CHAR(20));
INSERT INTO t1 VALUES ('1'); INSERT INTO t1 VALUES ('2'); ...
INSERT INTO t2 VALUES ('1'); INSERT INTO t2 VALUES ('2'); ...
SELECT name FROM t1 MINUS SELECT name FROM t2; -- 有輸出 '1'..'5'
SELECT name FROM t1 INTERSECT SELECT name FROM t2; -- 無輸出
集合運算(UNION/INTERSECT/MINUS)採用 VARCHAR2 比較語意(不補空格),導致 CHAR(10) 的 '1........'(含 9 空格)與 CHAR(20) 的 '1...................' 不相等。集合運算不遵循 CHAR 比較規則。
| 場景 | 建議型別 | 理由 |
|---|---|---|
| 固定長度代碼(國別碼、貨幣代碼、狀態碼) | CHAR(n) | 長度固定、比較頻繁、索引效能佳、避免行遷移 |
| 變動長度文字(姓名、地址、描述、JSON、XML) | VARCHAR2(n) | 節省空間、符合語意、12c+ 支援 32K |
| 單一字元旗標(Y/N、M/F、0/1) | CHAR(1) 或 CHARACTER | 語意明確、固定 1 字元、索引高選擇性 |
| 頻繁 UPDATE 長度變動大的欄位 | CHAR(n) | 避免行遷移;雖浪費空間但減少 I/O |
| PL/SQL 變數處理大量字串拼接 | VARCHAR2(32767) 或 CLOB | 動態分配、避免記憶體浪費 |
CHAR 與 VARCHAR2 的核心差異在於「固定 vs 變動」儲存機制與「補空格 vs 精確」比較語意。CHARACTER 只是 CHAR 的語意化子型別。關鍵陷阱在於 DECODE 函數將 CHAR 參數視為 VARCHAR2 處理,以及集合運算採用非補空格比較。開發時務必:
RPAD 或 TRIM 統一格式,避免隱式轉換驚喜。DECODE 中直接比較 CHAR 欄位,改用 CASE 或預先 TRIM。掌握這些細節,能避免 90% 的字串型別相關 Bug,提升程式碼健壯度。