MySQL TEXT 與 BLOB 型別深度解析：選擇指南與最佳實踐

MySQL TEXT 與 BLOB 型別深度解析：選擇指南與最佳實踐

許多開發者在設計文章、日誌、二進位檔案儲存欄位時，容易混淆 TEXT 與 BLOB。本文從儲存本質、字元集、索引、效能等面向詳細比較，助你做正確選型。

核心差異一覽

型別層級對應

兩者各層級最大長度與儲存需求完全相同。

選型決策樹

1. 資料是否為純文字（文章、留言、JSON、XML、原始碼）？
   → 是：選 TEXT 系列。
   → 否（圖片、PDF、壓縮檔、序列化二進位物件）：選 BLOB 系列。
2. 預估最大長度？
   → ≤ 64 KB：TEXT/BLOB
   → ≤ 16 MB：MEDIUMTEXT/MEDIUMBLOB
   → ≤ 4 GB：LONGTEXT/LONGBLOB
3. 是否需要全文搜尋？
   → 是：MySQL 5.6+ InnoDB 支援 FULLTEXT 索引於 TEXT 欄位。
   → 否：均可。

常見誤區與最佳實踐

• 誤區：「部落格文章含 <img> 標籤，所以要用 BLOB。」
  正確：HTML 內容本質是文字，圖片以 URL 參照，應用 TEXT。只有將圖片二進位內容直接寫入資料庫才需 BLOB。
• 大量文字查詢效能：避免 SELECT * 拉取大欄位；列表頁僅查詢摘要欄位或 SUBSTRING(content, 1, 200)。
• 索引策略：ALTER TABLE t ADD INDEX idx_content_prefix (content(100)); 僅索引前 100 字元，平衡查詢速度與索引大小。
• 嚴格模式：sql_mode=STRICT_TRANS_TABLES 下，超長資料會報錯而非截斷；應用層需驗證長度。
• 網路傳輸限制：實際可傳輸大小受 max_allowed_packet 限制（預設 4MB/64MB），大檔案需調大此參數並確保客戶端同步設定。
• 備份與複製：大量 TEXT/BLOB 表備份較慢，考慮將大欄位拆分至獨立表（垂直分表）或改用物件儲存（S3、MinIO）僅存 URL。

進階：超過 max_sort_length 的排序/分組

-- 將長文字轉為固定長度物件再排序
SELECT id, SUBSTRING(content, 1, 2000) AS content_prefix
FROM articles
ORDER BY content_prefix;

總結：文字用 TEXT，二進位用 BLOB，依長度選層級，謹慎建立前綴索引，大檔案優先考慮外部儲存。