MySQL 資料類型系統全景圖與選型實戰
資料類型選擇直接影響儲存空間、查詢效能、數據精確度與應用邏輯正確性。本文系統梳理 MySQL 三大類型體系（數值、日期時間、字串），提供儲存需求、範圍對比、適用場景與避坑指南，助您做出最優選型決策。
一、數值類型
1. 整數類型對比表
| 類型 | 大小 | 有符號範圍 | 無符號範圍 | 適用場景 |
|---|
| TINYINT | 1 Byte | -128 ~ 127 | 0 ~ 255 | 極小整數、布爾旗標、枚舉值 |
| SMALLINT | 2 Bytes | -32,768 ~ 32,767 | 0 ~ 65,535 | 較小整數、狀態碼、年份 |
| MEDIUMINT | 3 Bytes | -8,388,608 ~ 8,388,607 | 0 ~ 16,777,215 | 中等整數、IPv4 數值存儲 |
| INT/INTEGER | 4 Bytes | -2,147,483,648 ~ 2,147,483,647 | 0 ~ 4,294,967,295 | 通用整數、自增主鍵、外鍵 |
| BIGINT | 8 Bytes | -9.22e18 ~ 9.22e18 | 0 ~ 1.84e19 | 極大整數、分散式 ID、時間戳毫秒 |
2. 浮點與定點數
| 類型 | 大小 | 精度特徵 | 適用場景 |
|---|
| FLOAT | 4 Bytes | 單精度，~7 位有效數字 | 科學計算、近似值、儲存空間敏感 |
| DOUBLE | 8 Bytes | 雙精度，~15 位有效數字 | 高精度科學計算、座標經緯度 |
| DECIMAL(M,D) | M+2 或 D+2 Bytes | 精確定點數，無精度損失 | 金融金額、價格、比率、任何需精確計算場景 |
3. 關鍵選型原則
- 能用整數別用浮點：金額以「分」為單位存 INT/BIGINT
- 無符號優先：非負業務欄位加
UNSIGNED 翻倍正數範圍
- 最小足夠原則：狀態碼用 TINYINT，別濫用 INT
- DECIMAL 精度預留：如價格用
DECIMAL(10,2)，留 8 位整數部
二、日期時間類型
| 類型 | 大小 | 範圍 | 格式 | 核心差異 |
|---|
| DATE | 3 Bytes | 1000-01-01 ~ 9999-12-31 | YYYY-MM-DD | 純日期，無時區 |
| TIME | 3 Bytes | -838:59:59 ~ 838:59:59 | HH:MM:SS | 時間間隔或一天內時間 |
| YEAR | 1 Byte | 1901 ~ 2155 | YYYY | 僅年份，極省空間 |
| DATETIME | 8 Bytes | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 無時區，靜態時間點 |
| TIMESTAMP | 4 Bytes (5.6+) | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC | YYYY-MM-DD HH:MM:SS | 帶時區自動轉換，受 2038 年問題限制 |
選型決策樹
- 需時區感知、自動記錄創建/更新時間 → TIMESTAMP
- 歷史日期、生日、排程日期、跨世紀業務 → DATE/DATETIME
- 僅需年份（如出生年、模型年款） → YEAR
- 耗時、工時、倒計時 → TIME
- 避坑：TIMESTAMP 2038 年上限，長期系統必用 DATETIME
三、字串類型
1. 定長 vs 變長
| 類型 | 最大長度 | 儲存特徵 | 適用場景 |
|---|
| CHAR(N) | 255 字元 | 固定長度，右補空格，不截斷尾部空格 | 定長代碼（MD5、UUID、國際標準代碼）、極短高頻欄位 |
| VARCHAR(N) | 65,535 字元 (共享 64KB) | 變長，1-2 Bytes 長度前綴，保留尾部空格 | 通用變長字串、姓名、地址、描述 |
2. 二進制與大物件
| 類型 | 最大長度 | 用途 |
|---|
| BINARY/VARBINARY | 同 CHAR/VARCHAR | 二進制資料（加密、壓縮、圖片縮圖），按字節比較 |
| TINYBLOB/TEXT | 255 Bytes | 極小二進制/文本 |
| BLOB/TEXT | 64 KB | 小型文檔、日誌、JSON 片段 |
| MEDIUMBLOB/TEXT | 16 MB | 中型文檔、文章內容 |
| LONGBLOB/TEXT | 4 GB | 大型文檔、二進制檔案、完整日誌 |
3. 字串選型黃金法則
- 長度固定且極短 → CHAR（如 ISO 國家代碼 CHAR(2)）
- 長度可變 → VARCHAR(N) 設定業務實際上限 + 10% 緩衝
- 超過 64KB 文本 → TEXT 系列（避免行溢出頁）
- 二進制資料 → BLOB 系列（不做字符集轉換）
- 枚舉/集合 → ENUM/SET（內部用整數存儲，極省空間）
四、字符集與排序規則關鍵知識
- utf8mb4：真正支援完整 Unicode（含 Emoji），新專案預設首選
- utf8：MySQL 歷史偽 utf8，僅支援 BMP 平面（3 Bytes/字元）
- 排序規則：
_general_ci 速度快、準確度略低；_unicode_ci 標準、準確
- 大小寫不敏感：預設
_ci 結尾；需區分大小寫用 _cs 或 _bin
五、總結與最佳實踐清單
- 建表前先做數據剖析：實際長度分佈、基數、空值率、查詢模式
- 遵循「最小足夠、精確優先、業務語意」三原則
- 定期用
PROCEDURE ANALYSE() 或 SELECT * FROM table PROCEDURE ANALYSE() 獲取優化建議
- 遷移時注意：CHAR→VARCHAR 可能需重建表；VARCHAR 擴容通常 Online DDL 秒級完成
- 監控
Data_length/Index_length 趨勢，預判空間增長
正確的資料類型選擇是高效資料庫設計的基石。建議將本指南納入團隊開發規範，建立類型選型檢查清單，從源頭避免儲存浪費、精度損失與效能陷阱。
