MySQL 資料類型精選與建庫策略：數值、日期時間、字串、枚舉/集合類型深度解析與空間優化實戰

一、數值類型：精打細算每一個位元組

整數家族（無符號範圍、儲存空間）

策略：選擇「剛好夠用」的最小類型。1000 萬行表，主鍵用 MEDIUMINT 替代 INT 可節省 ~10MB 索引空間，提升快取命中率。

定點數：DECIMAL / NUMERIC（精確計算首選）

• 以字串形式儲存每位數字，小數位數固定，運算不失真。
• 空間：整數部每 9 位約 4 Bytes，小數部每 9 位約 4 Bytes。
• 金額、價格、匯率、比率 必須使用 DECIMAL(precision, scale)，如 DECIMAL(10,2)。
• 嚴禁用 FLOAT/DOUBLE 儲存金額（二進制浮點數無法精確表示 0.1 等十進制小數）。

浮點數：FLOAT / DOUBLE（科學計算、近似值）

適用：科學測量、座標、物理模擬。民用系統極少用到。

二、日期時間類型：精準對應業務語義

關鍵差異：TIMESTAMP 會自動轉換時區（儲存 UTC、讀取時按會話時區轉換），DATETIME 不轉換。跨時區應用優先 TIMESTAMP；單一時區業務用 DATETIME 避免時區困擾。

三、字串類型：固定 vs 可變、小 vs 大

CHAR vs VARCHAR

決策邏輯：若實際長度接近定義長度（如股票名稱多為 4 字 = 8 Bytes），用 CHAR 更省空間（省去長度前綴）；若變動大，用 VARCHAR。

TEXT 家族（大文本）

最佳實踐：大文本拆分到獨立表（垂直分表），主表只存核心欄位，避免大欄位拖慢主表掃描、降低緩衝池效率。

四、枚舉與集合：極致壓縮的低基數欄位

實戰範例

-- 性別：只有兩種，1 Byte 極致省空間且語義清晰
gender ENUM('帥哥', '美眉') NOT NULL DEFAULT '帥哥'

-- 訂單狀態：有限狀態機
status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') NOT NULL DEFAULT 'pending'

-- 文章標籤：多選
tags SET('tech', 'life', 'travel', 'food', 'sport') DEFAULT ''

優勢：比 TINYINT 更具可讀性，直接存取字串值，應用層無需映射字典。

五、建庫策略綜合原則

1. 主鍵優先用整數自增：BIGINT UNSIGNED AUTO_INCREMENT（分散式用 Snowflake ID）。
2. 外鍵型別、長度、字符集、排序規則 100% 一致。
3. 非 NULL 原則：預設 NOT NULL，必要時才允許 NULL（NULL 需額外位圖空間、索引統計複雜）。
4. 預設值：明確給 DEFAULT，避免應用層未傳值報錯。
5. 字符集統一 utf8mb4：DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci。
6. 大欄位垂直分表：TEXT/BLOB/JSON 拆離主表。
7. 索引長度控制：VARCHAR(191) 以下可建完整索引（utf8mb4 下 767 Bytes 限制），超長用前綴索引或 Hash 索引。