核心定義差異
| 特性 | TEXT 系列 | BLOB 系列 |
|---|
| 數據類型 | 字符串 (非二進制) | 二進制字節流 |
| 字符集 | 有 (utf8mb4, gbk 等) | 無 (二進制) |
| 排序比較 | 按字符集校對規則 | 按字節數值 |
| 大小寫轉換 | 支持 (按字符集) | 不支持 |
| 典型用途 | 文章、評論、JSON、日志文本 | 圖片、文件、加密數據、序列化對象 |
四大子類型對照
| 類型 | 最大長度 | 存儲開銷 | 適用場景 |
|---|
| TINYTEXT / TINYBLOB | 255 字節 (2^8-1) | 1 字節長度前綴 | 極短文本/圖標 |
| TEXT / BLOB | 64 KB (2^16-1) | 2 字節長度前綴 | 普通文章、縮略圖 |
| MEDIUMTEXT / MEDIUMBLOB | 16 MB (2^24-1) | 3 字節長度前綴 | 長文、高清圖片 |
| LONGTEXT / LONGBLOB | 4 GB (2^32-1) | 4 字節長度前綴 | 巨型文檔、視頻、備份 |
博客正文存儲選型決策
方案 A：TEXT (推薦 99% 博客)
CREATE TABLE posts (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
-- 或 MEDIUMTEXT (16MB 足夠絕大多數文章)
created_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB;
- **優勢**：原生字符集支持、全文索引、字符串函數 (
SUBSTRING, REPLACE, LENGTH 按字符)、應用層直讀無解碼
- **圖片處理**：現代博客**不存二進制圖片**，圖片上傳至對象存儲 (OSS/S3) 或靜態服務器，正文存 `
` URL
方案 B：BLOB (極少數場景)
- 需要存儲**加密後的文章內容**（應用層加密，數據庫不可讀）
- 存儲**序列化對象** (Protobuf, MessagePack, Python pickle)
- 存儲**圖片二進制**（極不推薦：備份巨大、無法預覽、SQL 注入風險、CDN 不可用）
關鍵限制與避坑指南
🚫 硬性限制
- 不能有默認值：
DEFAULT 'xxx' ❌ 报錯
- 索引必須指定前綴長度：
INDEX idx_content (content(255))，不能全文索引（需 FULLTEXT）
- 排序只用前 max_sort_length 字節：默認 1024 字節，
ORDER BY content 可能不準確
- 內存臨時表不支持：含 TEXT/BLOB 查詢強制落磁盤臨時表 (MyISAM)，
Using temporary 性能陡降
max_allowed_packet 限制單次傳輸大小，需同步調大客戶端/服務端
性能優化最佳實踐
1. 選擇合適長度類型
-- 文章正文：MEDIUMTEXT (16MB) 通常足夠，節省元數據開銷
-- 評論/摘要：TEXT (64KB)
-- 避免盲目用 LONGTEXT (4GB)，除非真有超大文檔
2. 將大字段拆分到獨立表 (垂直分表)
-- 主表：高頻訪問列
CREATE TABLE posts (
id BIGINT UNSIGNED PRIMARY KEY,
title VARCHAR(200),
author_id INT,
status TINYINT,
created_at DATETIME(3),
INDEX idx_author_status (author_id, status)
) ENGINE=InnoDB;
-- 擴展表：低頻大字段
CREATE TABLE post_content (
post_id BIGINT UNSIGNED PRIMARY KEY,
content MEDIUMTEXT CHARACTER SET utf8mb4,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;
- 主表行長度縮短 → **更多行放入一頁** → 緩存命中率 ↑、掃描速度 ↑
- 按需 JOIN 獲取內容，避免列表查詢讀取大字段
3. 避免 SELECT *
-- ❌ 列表頁讀取大字段
SELECT * FROM posts WHERE status=1;
-- ✅ 只選需要列
SELECT id, title, created_at FROM posts WHERE status=1;
-- 詳情頁再 JOIN post_content 獲取 content
4. 全文搜索優化
-- MySQL 5.6+ InnoDB 支持 FULLTEXT
ALTER TABLE posts ADD FULLTEXT INDEX ft_title_content (title, content);
-- 自然語言模式
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE);
-- 布爾模式
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('+MySQL -舊版' IN BOOLEAN MODE);
ngram_parser 支持中文分詞 (5.7.6+)- 大數據量建議 Elasticsearch/Meilisearch 專用搜索引擎
5. 參數調優
[mysqld]
# 客戶端/服務端協商包大小 (需 > 最大 TEXT/BLOB)
max_allowed_packet = 256M
# 排序緩衝 (影響 ORDER BY TEXT)
sort_buffer_size = 2M
max_sort_length = 2048 # 增加排序比較字節數
# InnoDB 緩衝池 (容納熱點大字段頁)
innodb_buffer_pool_size = 70% RAM
遷移建議：TEXT ↔ BLOB
-- TEXT → BLOB (字符集移除)
ALTER TABLE t MODIFY col BLOB; -- 數據不變，僅元數據變更
-- BLOB → TEXT (需指定字符集)
ALTER TABLE t MODIFY col TEXT CHARACTER SET utf8mb4;
-- 注意：若原二進制數據非合法 UTF-8，查詢會報錯/亂碼
總結
博客正文存儲**首選 TEXT (utf8mb4)**，配合**垂直分表**隔離大字段，**對象存儲**處理圖片，**全文索引**或**專用搜索引擎**實現搜索。BLOB 僅用於二進制不可讀數據。理解「字符集語義」與「二進制語義」的本質區別，是正確選型的關鍵。
