MySQL 資料表設計指南：命名、編碼、引擎、欄位類型與索引最佳實踐

資料表設計直接影響系統效能、擴展性與維護成本。本文從命名規範、字符集、儲存引擎、欄位類型、預設值、多庫架構、索引策略七大維度，總結 MySQL 表設計的核心原則與避坑指南。

1. 表名命名規範

• 推薦：下劃線分隔小寫（my_customer），符合開源社區慣例，避免大小寫混淆。
• 不推薦：駝峰式（myCustomer）易遺漏大寫；中文名（我的客戶）涉及編碼轉換風險。

2. 字符集選擇

建議：庫、表、連接、結果集全鏈路統一 utf8mb4，連接字串加 characterEncoding=utf8mb4。

3. 儲存引擎選型

原則：無特殊理由統一用 InnoDB；MySQL 8.0 已移除部分舊引擎。

4. 欄位類型精選

• 整數：TINYINT(1B)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B)。UNSIGNED 不改變儲存大小，僅擴大正數範圍。
• 定長字符：CHAR(N) 適合 ≤10 字符 且長度固定（如 MD5、UUID、手機號），MEMORY 引擎會將 VARCHAR 轉為 CHAR。
• 變長字符：VARCHAR(N) 佔用實際長度+1~2 字節，通用首選。
• 精確數值：DECIMAL(M,D) / NUMERIC 用於金額、價格；FLOAT/DOUBLE 僅用於科學計算、非精確場景。
• 大文本：TEXT/BLOB 族群（含 TINY/MEDIUM/LONG），BLOB 為二進制，不做字符集校驗。
• 枚舉：ENUM('a','b') 佔 1~2 字節，適合狀態碼、性別等固定值集合，效能優於 VARCHAR。
• 日期時間：
  • DATE (3B) 日期
  • TIME (3B) 時間
  • YEAR (1B) 年份，存年份極省空間
  • DATETIME (8B) 無時區，範圍 1000~9999 年
  • TIMESTAMP (4B) 有時區轉換，範圍 1970~2038 年
• 布林：BOOLEAN 即 TINYINT(1)，佔 1 字節。
• 自增鍵：大併發下避免 ORDER BY RAND() 導致鎖爭用，改用應用層生成隨機序列或雪花算法 ID。
• ZEROFILL：INT(5) ZEROFILL 存 12 顯示 00012，替代前導零字符串存儲。

5. 預設值與 NOT NULL

• MySQL 5.0+：定義 NOT NULL 且無顯式 DEFAULT 時，系統自動賦予類型零值（'', 0, 0.0, '0000-00-00' 等）。
• 5.0 前：必須顯式指定 DEFAULT，否則 INSERT 不指定該欄位會報錯。
• 強烈建議所有欄位設 NOT NULL，NULL 值佔額外位圖空間、索引統計複雜、COUNT(col) 不計 NULL、NOT IN 子查詢陷阱多。

6. 多資料庫架構

• 業務解耦：不同業務域（用戶、訂單、日誌）建立獨立資料庫，而非塞進單庫。
• 命名風格與表名一致（下劃線小寫），便於自動化工具識別。

7. 索引設計原則

• 初期規劃：依據查詢模式建立索引，而非事後補救。
• 必測驗證：EXPLAIN 確認索引生效，無效索引純增寫入開銷（插入/更新需維護 B+ 樹）。
• 低基數欄位勿建索引：如性別(0/1)、狀態(幾枚值)，索引選擇度極低，優化器傾向全表掃描。改用 ENUM、TINYINT 或拆分為兩張表（如 active_users/inactive_users）。
• 聯合索引遵循最左前綴，高選擇度欄位前置。
• 覆蓋索引：SELECT a,b FROM t WHERE c=1 建 INDEX(c,a,b) 避免回表。

總結清單

1. 表名：snake_case
2. 字符集：全鏈路 utf8mb4
3. 引擎：預設 InnoDB
4. 類型：够用就好、拒絕過度設計
5. 預設值：顯式定義，全欄 NOT NULL
6. 分庫：業務隔離
7. 索引：按查詢建、定期清理無效索引