MySQL 數據類型與字段屬性完全手冊：選型原理與避坑指南

MySQL 數據類型與字段屬性完全手冊：選型原理與避坑指南

合理的數據類型選型是資料庫性能優化的基石，影響存儲空間、查詢速度、索引效率及應用邏輯正確性。本文系統梳理 MySQL 所有數據類型特性、存儲開銷、選型原則及常見陷阱。

一、日期時間類型

關鍵差異：TIMESTAMP 存儲 UTC，讀取時按會話時區轉換；DATETIME 存儲原始值，不做時區轉換。2038 年問題限制 TIMESTAMP 長期使用。

二、數值類型

1. 整數類型

選型原則：夠用就好，越小越好。無符號可擴大正數範圍，但應用層需處理邊界。

2. 浮點/定點類型

陷阱實驗：FLOAT(5,3) 插入 123.45678 實際存儲 99.999（超出範圍截斷）。金融、價格類絕對禁止使用 FLOAT/DOUBLE。

三、字符串類型

關鍵差異點

1. CHAR 尾部空格會被截斷，VARCHAR/TEXT 保留
2. VARCHAR 需 1-2 字節記錄長度，TEXT 固定 2 字節
3. CHAR/VARCHAR 可建立完整索引，TEXT 僅支持前綴索引
4. UTF8MB4 下：CHAR(100) 佔 400 字節，VARCHAR(100) 實際佔用按內容

四、枚舉與集合類型

注意：ENUM 成員順序即排序順序，修改成員需 ALTER TABLE 重建表。

五、核心字段屬性詳解

六、主鍵設計最佳實踐

1. 單列主鍵：業務無關代理鍵（如自增 ID），不可變更
2. 聯合主鍵：多列組合唯一，順序遵循最左前綴原則
3. 避免：UUID、長字符串、業務含義字段作主鍵
4. InnoDB 特有：主鍵即聚簇索引，二級索引葉子節點存主鍵值，主鍵越小二級索引越緊湊

七、修改字段類型操作指南

-- 安全修改：兼容現有數據
ALTER TABLE tbl MODIFY col NEW_TYPE;

-- 風險操作：數據可能截斷/轉換失敗
ALTER TABLE tbl MODIFY col VARCHAR(10) NOT NULL;

原則：擴大類型安全，縮小類型需驗證數據兼容性；生產環境大表使用 pt-online-schema-change。

八、選型決策速查表

• 金額/價格 → DECIMAL(10,2) 或 BIGINT 存分
• 手機號 → CHAR(11) 或 BIGINT
• 身份證 → CHAR(18)
• 郵箱 → VARCHAR(100)
• 文章內容 → TEXT / LONGTEXT
• 狀態枚舉 → TINYINT + 應用層常量 或 ENUM
• 標籤集合 → SET 或 關聯表（推薦）
• 創建時間 → DATETIME 默認 CURRENT_TIMESTAMP
• 更新時間 → DATETIME 默認 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

數據類型選型看似基礎，實則影響深遠。遵循「最小足夠、業務對齊、性能優先」原則，結合具體存儲引擎特性，才能構建高效穩健的資料庫模型。