MySQL 數據類型完全指南：選型原理、存儲特性與避坑實戰
合理的數據類型選型是資料庫效能優化的基石，影響存儲空間、索引效率、查詢速度與數據準確性。本文系統梳理 MySQL 所有數據類型的存儲特性、適用場景、常見誤區與最佳實踐，幫助開發者做出最優選擇。
數值類型
整數類型對比表
| 類型 | 存儲字節 | 有符號範圍 | 無符號範圍 | 典型用途 |
|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 | 布爾標誌、狀態碼、極小計數 |
| SMALLINT | 2 | -32,768 ~ 32,767 | 0 ~ 65,535 | 小範圍計數、年齡、短整數 |
| MEDIUMINT | 3 | -8,388,608 ~ 8,388,607 | 0 ~ 16,777,215 | 中等計數、IP 地址存儲 |
| INT / INTEGER | 4 | -2,147,483,648 ~ 2,147,483,647 | 0 ~ 4,294,967,295 | 主鍵 ID、通用計數、外鍵 |
| BIGINT | 8 | -9E18 ~ 9E18 | 0 ~ 1.8E19 | 大數據量主鍵、時間戳毫秒、雪花算法 ID |
關鍵選型原則
- 足夠即可：選擇能容納業務最大值的最小類型，節省存儲與索引空間
- 優先 UNSIGNED：非負業務（ID、計數、年齡）必加
UNSIGNED，正數範圍翻倍
- 主鍵首選 BIGINT：分佈式系統預留擴展空間，配合雪花算法生成全局唯一 ID
- 顯示寬度誤區：
INT(11) 僅影響 ZEROFILL 顯示，不限制存儲範圍
定點數與浮點數
| 類型 | 存儲 | 精度 | 適用場景 | 風險 |
|---|
| DECIMAL(M,D) / NUMERIC | M+2 字節 (近似) | 精確，M 位總數，D 位小數 | 金額、價格、匯率、精確計算 | 運算較慢，存儲較大 |
| FLOAT(M,D) | 4 字節 | 單精度，約 7 位有效數字 | 科學計算、近似值、坐標 | 精度丟失：99.000099 → 99.0001 |
| DOUBLE(M,D) | 8 字節 | 雙精度，約 15 位有效數字 | 高精度科學計算 | 仍有精度風險，嚴禁用於金融 |
金融金額存儲最佳實踐
-- 方案 1：DECIMAL (推薦)
price DECIMAL(10,2) -- 99,999,999.99 足夠大多數電商
-- 方案 2：整數分存儲 (最高效能)
price_cents BIGINT -- 存儲分為單位，應用層除 100 顯示
-- 99.99 元 → 存 9999
-- 方案 3：定點數擴展
amount DECIMAL(18,4) -- 支持更高精度，如匯率計算
字符串類型
CHAR vs VARCHAR 核心差異
| 特性 | CHAR(N) | VARCHAR(N) |
|---|
| 存儲方式 | 固定長度，右填充空格 | 變長，1-2 字節長度前綴 + 實際數據 |
| 最大長度 | 255 字符 | 65,535 字符 (受行大小限制) |
| 空間效率 | 短數據浪費大 (CHAR(10) 存 'PHP' 浪費 7 字符) | 按需分配，極致節省 |
| 處理速度 | 略快 (固定偏移定位) | 需讀取長度前綴 |
| 尾部空格 | 存儲時保留，檢索時可能截斷 | 保留尾部空格 |
| InnoDB 建議 | 極少用 | 首選，配合行溢出機制 |
二進制字符串：BINARY / VARBINARY
- 存儲字節而非字符，長度單位為字節
- 無字符集概念，按字節比較，適合存儲加密數據、哈希值、文件指紋
BINARY(32) 存儲 MD5，BINARY(64) 存儲 SHA256
大對象類型：BLOB / TEXT 家族
| 類型 | 最大長度 | 存儲開銷 | 用途 |
|---|
| TINYBLOB / TINYTEXT | 255 字節 | 1 字節長度前綴 | 極小二進制/文本 |
| BLOB / TEXT | 64 KB | 2 字節長度前綴 | 小圖片、長文本 |
| MEDIUMBLOB / MEDIUMTEXT | 16 MB | 3 字節長度前綴 | 文檔、較大圖片 |
| LONGBLOB / LONGTEXT | 4 GB | 4 字節長度前綴 | 大文件、完整日誌 |
關鍵差異與最佳實踐
- BLOB：二進制，無字符集，按字節比較排序
- TEXT：有字符集與校對規則，按字符比較排序
- InnoDB 行溢出：> 768 字節自動移至溢出頁，前綴 768 字節存主頁
- 避免 SELECT *：查詢大字段導致大量隨機 I/O 讀取溢出頁
- 極大對象建議：存儲對象存儲，數據庫只存 URL/路徑
特殊類型：ENUM 與 SET
ENUM (枚舉)
status ENUM('pending', 'paid', 'shipped', 'cancelled')
-- 存儲：1 字節 (≤255 個值) 或 2 字節 (≤65535 個值)
-- 內部存儲為索引：'pending'=1, 'paid'=2...
-- ⚠️ 經典陷阱：
INSERT INTO t(status) VALUES (2); -- 存儲 'paid' (索引 2)
INSERT INTO t(status) VALUES ('2'); -- 存儲 '2' (字符串匹配)
INSERT INTO t(status) VALUES ('3'); -- 存儲 'cancelled' (索引 3！)
建議：僅用於固定、極少變更的狀態碼，嚴禁數字字符串混用。
SET (集合)
permissions SET('read', 'write', 'execute', 'admin')
-- 最多 64 個成員，存儲為位圖 (1/2/3/4/8 字節)
-- 值自動去重、排序：'a,d' = 'd,a' = 'a,d,a' = 'a,d,d'
日期時間類型
| 類型 | 存儲 | 範圍 | 精度 | 時區感知 | 推薦場景 |
|---|
| DATE | 3 字節 | 1000-01-01 ~ 9999-12-31 | 天 | 否 | 生日、紀念日、純日期業務 |
| TIME | 3 字節 | -838:59:59 ~ 838:59:59 | 秒 | 否 | 時長、每日定時任務 |
| DATETIME | 8 字節 (5.6.4+ 5+字節) | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 秒/微秒 | 否 | 歷史記錄、日誌、不需時區轉換 |
| TIMESTAMP | 4 字節 | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC | 秒/微秒 | 是 | 創建/更新時間、跨時區應用 |
| YEAR | 1 字節 | 1901 ~ 2155 | 年 | 否 | 年份篩選、年度報表 |
TIMESTAMP 時區自動轉換機制
| 業務場景 | 推薦類型 | 核心理由 |
|---|
| 自增主鍵 ID | BIGINT UNSIGNED | 分佈式擴展、雪花算法兼容 |
| 用戶手機號 | CHAR(11) / BIGINT | 固定長度/數字運算優化 |
| 商品價格 | DECIMAL(10,2) / BIGINT(分) | 精確計算、無精度丟失 |
| 商品描述 | VARCHAR(2000) / TEXT | 變長、按需存儲 |
| 圖片/文件 | VARCHAR(500) (存 URL) | 對象存儲分離、DB 輕量 |
| 訂單狀態 | TINYINT UNSIGNED / ENUM | 極小範圍、索引效率高 |
| 創建時間 | TIMESTAMP / DATETIME(3) | 時區自動轉換/微秒精度 |
| IP 地址 | INT UNSIGNED / VARBINARY(16) | INET_ATON()/INET6_ATON() 轉換 |
| JSON 數據 | JSON (5.7+) | 原生函數支持、虛擬列索引 |
總結
數據類型選型遵循「最小充足、語義正確、效能優先」三大原則：
- 最小充足：能用 TINYINT 別用 INT，能用 VARCHAR(50) 別用 TEXT
- 語義正確：金額用 DECIMAL，時間用 DATETIME/TIMESTAMP，IP 用 INT/BINARY
- 效能優先：固定長度利於索引，數值比較快於字符串，避免隱式轉換
定期審查表結構，配合 PROCEDURE ANALYSE() 或 SELECT * FROM table PROCEDURE ANALYSE() 獲取優化建議，是維護健康資料庫模型的關鍵習慣。
