MySQL 資料型態介紹 – 文字型態 CHAR / VARCHAR / TEXT

---

簡介

在資料庫設計中，文字資料是最常見的類型之一，無論是使用者名稱、商品描述、部落格內容，甚至是 JSON、XML 文字，都會落在 CHAR、VARCHAR 或 TEXT 的範疇。選擇合適的文字型態直接影響 存儲空間、查詢效能、索引策略，甚至會牽涉到未來的維護成本。

本篇文章將從概念、範例、常見陷阱與最佳實踐，帶你全面了解這三種文字型態的差異與適用情境，讓你在設計 MySQL 資料表時能夠做出最適合的決策。

---

核心概念

1. CHAR – 固定長度字元

• 定義：CHAR(n) 會為每一筆資料分配 固定的 n 個字元，不足的部分會以空白 (' ') 填充。
• 適用情境：欄位長度固定且變化不大，例如國家代碼 ('TW')、性別 ('M'/'F')、郵遞區號等。
• 優點：
  • 讀取速度較快，因為每筆資料的長度固定，磁碟與記憶體的定位更直接。
  • 在較舊的 MyISAM 引擎或某些嵌入式系統中，效能優於可變長度型態。
• 缺點：
  • 若實際資料遠小於定義長度，會浪費大量儲存空間。
  • 在 UTF-8 編碼下，CHAR(10) 可能佔用 30 個位元組（每個字元最多 3 位元組），必須留意字符集的實際位元組需求。

2. VARCHAR – 可變長度字元

• 定義：VARCHAR(n) 允許每筆資料的長度 在 0~n 之間變動，MySQL 會在資料前加上 1~2 位元組的長度資訊（取決於 n 是否大於 255）。
• 適用情境：欄位長度不固定且變化範圍較大，例如使用者暱稱、電子郵件地址、商品名稱等。
• 優點：
  • 節省儲存空間，只佔用實際字元數加上長度資訊。
  • 支援索引（在 InnoDB 中，索引長度上限 767 位元組，UTF-8 時需要注意）。
• 缺點：
  • 需要額外的長度資訊，對極小的欄位（如只會存放 1 個字元）可能略微慢於 CHAR。

3. TEXT – 大型文字型態

• 定義：TEXT 系列（TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT）用來存放 大量文字，不需要指定長度。
• 適用情境：文章內容、商品說明、日誌紀錄、JSON/HTML 文字等，長度可能超過 VARCHAR 的上限（VARCHAR 最大 65,535 位元組，但實際受行大小限制）。
• 特性：
  • 存放在 BLOB 類型的資料頁，與普通行資料分離（取決於行長度）。
  • 不能直接建立全文索引（除非使用 FULLTEXT），且在 InnoDB 中，TEXT 欄位的前 768 位元組會被緩存於二級索引。
• 注意：TEXT 欄位在 SELECT 時若不需要，最好使用 SELECT column_name FROM table（避免 SELECT *）以減少不必要的 I/O。

---

程式碼範例

以下範例展示 建立、插入、查詢、索引 的基本操作，並說明每種型態的實務用法。

範例 1：建立包含 CHAR、VARCHAR、TEXT 的範例表

CREATE TABLE user_profile (
    user_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_code CHAR(2)        NOT NULL,          -- 例：TW、US
    username     VARCHAR(30)    NOT NULL,          -- 使用者暱稱
    bio          TEXT          NULL,              -- 個人簡介，可長可短
    created_at   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

說明

• country_code 使用 CHAR(2)，因為國家代碼永遠是 2 個字元。
• username 使用 VARCHAR(30)，讓使用者可以自行決定長度，最多 30 個字元。
• bio 使用 TEXT，因為簡介文字長度不確定，可能會超過 VARCHAR 的上限。

範例 2：插入資料，觀察儲存空間差異

INSERT INTO user_profile (country_code, username, bio) VALUES
('TW', 'alice', '熱愛程式設計，喜歡分享技術文章。'),        -- 短文字
('US', 'bob_the_builder', REPEAT('建築師 ', 200));          -- 長文字 (約 1200 位元組)

說明

• REPEAT('建築師 ', 200) 會產生 200 次「建築師 」字串，總長度約 1,200 位元組，演示 TEXT 能容納大量文字。
• 若改用 VARCHAR(500)，此筆資料會因超過上限而失敗。

範例 3：為 VARCHAR 欄位建立索引，並測試查詢效能

-- 建立唯一索引，保證使用者名稱不重複
CREATE UNIQUE INDEX idx_username ON user_profile (username(20));

-- 查詢測試
EXPLAIN SELECT * FROM user_profile WHERE username = 'alice';

說明

• 索引長度使用 username(20)，只取前 20 個字元作為索引鍵，足以區分大多數情況，且避免過長索引佔用過多空間。
• EXPLAIN 可看到索引是否被使用，若顯示 type: ref，代表索引有效。

範例 4：使用 FULLTEXT 索引搜尋 TEXT 欄位

-- 為 bio 欄位建立全文索引（InnoDB 需要 MySQL 5.6+）
ALTER TABLE user_profile ADD FULLTEXT INDEX ft_bio (bio);

-- 搜尋包含「程式」的簡介
SELECT user_id, username, MATCH(bio) AGAINST('程式' IN NATURAL LANGUAGE MODE) AS relevance
FROM user_profile
WHERE MATCH(bio) AGAINST('程式' IN NATURAL LANGUAGE MODE) > 0
ORDER BY relevance DESC;

說明

• FULLTEXT 索引只能在 MyISAM 或 InnoDB（5.6 以上）上使用，適合關鍵字搜尋。
• 只對 TEXT、VARCHAR（長度 > 3）等欄位有效。

範例 5：比較 CHAR 與 VARCHAR 的儲存空間（使用 SHOW TABLE STATUS）

-- 先插入大量相同長度的資料
INSERT INTO user_profile (country_code, username, bio)
SELECT 'TW', LPAD('u', 30, 'x'), NULL FROM seq_1_to_10000;

-- 查看資料表大小
SHOW TABLE STATUS LIKE 'user_profile';

說明

• LPAD('u', 30, 'x') 產生長度恰好 30 的字串，若改成 CHAR(30)，每筆會固定佔用 30 位元組；若改成 VARCHAR(30)，則只佔用實際長度 + 1 位元組的長度資訊。
• 觀察 Data_length 欄位的差異，即可體會兩者的儲存空間差別。

---

常見陷阱與最佳實踐

陷阱	說明	最佳做法
忘記考慮字元集的位元組數	在 UTF-8 (utf8mb4) 下，中文、日文等多位元組字元會佔用 3~4 位元組。若直接以「字元」計算長度，可能超過欄位上限。	設計時以 位元組 為基準，使用 CHARACTER_LENGTH()（字元）與 LENGTH()（位元組）檢查。
在 VARCHAR 上建立過長的索引	索引長度超過 InnoDB 允許的 767 位元組（utf8mb4 為 191 個字元）會失敗。	針對 VARCHAR 建索引時，指定前綴長度（如 username(20)），或改用 utf8（3 位元組）字元集。
使用 TEXT 作為唯一鍵	MySQL 不允許在 TEXT 上直接建立 UNIQUE 索引，會產生錯誤。	若需要唯一性，改用 VARCHAR（加長度限制）或在 TEXT 前加一個 SHA1/MD5 雜湊欄位作為唯一鍵。
忽略 NULL 與空字串的差異	CHAR 欄位若插入 NULL，會儲存為 NULL；若插入空字串，會被填滿空白。	明確定義欄位是否允許 NULL，必要時使用 NOT NULL DEFAULT ''。
過度使用 TEXT 造成 I/O 負擔	SELECT * 時會把所有 TEXT 欄位一起讀取，導致不必要的磁碟 I/O。	只選取需要的欄位，或使用 分割表（將長文字搬到另一張表）以降低主表負載。

---

實際應用場景

1. 會員系統

   • country_code → CHAR(2)（固定長度）
   • email → VARCHAR(255)（RFC 5321 最長 254）
   • profile_description → TEXT（可容納長篇自我介紹）

2. 商品目錄

   • sku → CHAR(8)（固定 SKU 編碼）
   • product_name → VARCHAR(100)（視需求調整）
   • product_detail → MEDIUMTEXT（支援圖片說明、HTML 內容）

3. 日誌與審計

   • log_message → TEXT（記錄任意長度訊息）
   • 若需要關鍵字搜尋，為 log_message 加上 FULLTEXT 索引。

4. 多語系內容管理

   • title_en, title_zh_TW → VARCHAR(150)（各語系標題）
   • content_en, content_zh_TW → LONGTEXT（可能包含大量文字與 HTML）

---

總結

• **CHAR** 適合固定長度、高頻讀取的欄位，雖然會浪費空間，但在特定情況下能提升效能。
• **VARCHAR** 是最常用的文字型態，提供彈性與節省空間的平衡，建議在大多數欄位使用，並適度設定 前綴索引 以避免索引過長。
• **TEXT** 系列則用於大量文字，配合 FULLTEXT 可支援關鍵字搜尋，但要注意 I/O 與索引限制。

在設計 MySQL 資料表時，先分析資料的長度與使用頻率，再選擇最合適的文字型態。正確的型態選擇不僅能降低儲存成本，還能提升查詢效能與系統可維護性。祝你在實務開發中玩得開心、寫出高效能的資料庫設計！