MySQL數據類型與表字段約束完整指南

MySQL數據類型與表字段約束完整指南

合理選擇數據類型與約束，是數據庫設計性能與數據質量的基石。本文系統梳理MySQL支持的數據類型、字段屬性約束及建表最佳實踐。

一、數據類型詳解

1. 數值類型

關鍵點：

• M為顯示寬度，D為小數位數。FLOAT(3,1)範圍-99.9~99.9
• 整型可加UNSIGNED擴大正數範圍；浮點/定點數不可用
• DECIMAL必須指定M,D；FLOAT/DOUBLE可選
• 原則：滿足範圍前提下，選擇最小類型（如年齡用TINYINT(3)）

2. 字符串類型

關鍵點：

• CHAR按定長存儲，VARCHAR按實際長度+長度前綴存儲
• 推薦使用VARCHAR或NVARCHAR(Unicode)，避免TEXT類型（讀取效率低、難建索引）
• MySQL 5.0+ 推薦VARCHAR(MAX)替代TEXT

3. 日期時間類型

實踐建議：PHP常用INT存儲Unix時間戳，應用層格式化；MySQL 5.6.4+ DATETIME支持微秒且無2038年問題，優先選擇。

二、表字段約束與屬性

1. 主鍵

PRIMARY KEY (column_name)

• 唯一標識每行，非空、唯一
• 一張表僅能有一個主鍵（可為復合主鍵）
• 建議：無業務含義、系統自增、永不更新、不含動態數據

2. 外鍵

FOREIGN KEY (uid) REFERENCES user(uid)

• 維護表間引用完整性，防止孤兒數據
• 主表（被引用表）必須有主鍵/唯一索引
• 從表（引用表）定義外鍵
• 刪除主表記錄前，必須先刪除從表關聯記錄

3. 自動遞增

uid MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT

• 僅限整數類型
• 隱含非空、唯一，無需額外定義
• 插入時可省略或指定NULL/0觸發自增

4. 非空與默認值

username CHAR(20) NOT NULL DEFAULT ''
regdate INT(10) UNSIGNED NOT NULL DEFAULT 0

• NOT NULL防止意外空值，配合DEFAULT簡化插入語句
• 修改：ALTER TABLE user CHANGE username username CHAR(20) NULL（去掉非空）

5. 唯一性約束

UNIQUE KEY username (username)
-- 添加/刪除
ALTER TABLE tb_name ADD UNIQUE (column_name);
ALTER TABLE tb_name DROP INDEX column_name;

• 主鍵自動包含唯一約束，無需重複定義
• 允許多個NULL值（標準SQL規範）

6. 索引

KEY email (email)
-- 等同於
INDEX email (email)

• 加速查詢，代價是寫入性能與存儲空間
• 為經常出現在WHERE、JOIN、ORDER BY的列建立索引

三、綜合建表最佳實踐範例

CREATE TABLE `user` (
  `uid` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` CHAR(15) NOT NULL DEFAULT '',
  `password` CHAR(32) NOT NULL DEFAULT '',
  `email` VARCHAR(40) NOT NULL DEFAULT '',
  `regdate` INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

表選項說明：

• ENGINE=MyISAM：高性能、無事務、全文索引；InnoDB：事務、行鎖、外鍵
• CHARSET=utf8：國際通用編碼，避免亂碼
• AUTO_INCREMENT=1：自增起始值

四、選型決策速查表

遵循「最小足夠原則」選擇類型，配合完整約束體系，構建高性能、高可靠的數據庫基礎架構。