前言
正確選擇資料型別與約束，是資料庫設計的基石，直接影響儲存空間、查詢效能、資料完整性與應用層開發體驗。本文系統整理 MySQL 8.0 主流型別分類、儲存範圍、適用場景、常見誤區，並詳細說明主鍵、外鍵、索引、自增、非空、唯一、預設值、CHECK 約束的語法與最佳實踐。
一、數值型別
| 型別 | 儲存位元組 | 有號範圍 | 無號範圍 | 適用場景 |
|---|
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 | 布爾旗標、狀態碼、極小計數 |
SMALLINT | 2 | -32,768 ~ 32,767 | 0 ~ 65,535 | 小範圍計數、年齡、郵遞區號 |
MEDIUMINT | 3 | -8,388,608 ~ 8,388,607 | 0 ~ 16,777,215 | 中等計數、IPv4 整數存儲 |
INT / INTEGER | 4 | -2,147,483,648 ~ 2,147,483,647 | 0 ~ 4,294,967,295 | **主鍵首選**、通用計數、外鍵 |
BIGINT | 8 | -9.22e18 ~ 9.22e18 | 0 ~ 1.84e19 | 極大主鍵 (分散式 ID)、毫秒時間戳 |
DECIMAL(M,D) | M+2 近似 | 精確定點數 | - | **金額、價格、匯率** (不可用 FLOAT/DOUBLE) |
FLOAT | 4 | 單精度浮點 | - | 科學計算、不需精確的測量值 |
DOUBLE | 8 | 雙精度浮點 | - | 同上，精度更高 |
BIT(M) | ~M/8 | 位元向量 | - | 權限位圖、特徵向量 |
關鍵提醒
INT(11) 中的 11 僅為**顯示寬度**，不影響儲存範圍，**請忽略**。- **主鍵建議
BIGINT UNSIGNED AUTO_INCREMENT** (配合雪花演算法或自增)。
- 金額**嚴禁**用
FLOAT/DOUBLE，務必 DECIMAL(19,4) 或 DECIMAL(27,8) (加密貨幣)。
二、字串型別
| 型別 | 最大長度 | 儲存方式 | 適用場景 |
|---|
CHAR(N) | 255 字元 | 定長，右補空格 | 固定長度代碼 (ISO 國別碼、MD5、UUID 去連字號) |
VARCHAR(N) | 65,535 字元 (共享 64KB 行上限) | 變長，1-2 字節長度前綴 + 內容 | **通用變長字串** (姓名、郵件、地址、JSON 片段) |
TINYTEXT | 255 字元 | 溢位頁 | 極短文本 |
TEXT | 64 KB | 溢位頁 | 文章內容、日誌 |
MEDIUMTEXT | 16 MB | 溢位頁 | 長文本 |
LONGTEXT | 4 GB | 溢位頁 | 超長文本、完整 JSON 文檔 |
BINARY/VARBINARY | 同 CHAR/VARCHAR | 位元組串 | 加密資料、檔案雜湊、二進制協議 |
BLOB 系列 | 同 TEXT 系列 | 二進制大物件 | 圖片、檔案 (建議存物件儲存，DB 存路徑) |
ENUM | 65,535 成員 | 1-2 字節索引 | 固定值集合 (狀態: 'pending','paid','cancelled') |
SET | 64 成員 | 位元組位圖 | 多選標籤 (tags: 'tech','life','sport') |
CHAR vs VARCHAR 選型原則
- 長度固定、頻繁更新 →
CHAR (避免行遷移)。
- 長度變動、平均長度遠小於最大 →
VARCHAR (節省空間)。
VARCHAR(255) 仍用 1 字節長度前綴；VARCHAR(256+) 用 2 字節。
三、時間日期型別
| 型別 | 儲存位元組 | 範圍 | 精度 | 時區感知 | 適用場景 |
|---|
DATE | 3 | 1000-01-01 ~ 9999-12-31 | 天 | 否 | 生日、發文日期、報表維度 |
TIME | 3 | -838:59:59 ~ 838:59:59 | 秒 | 否 | 每日排程、工時 |
DATETIME | 5-8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59.999999 | 微秒 | 否 (儲存本地時間) | **業務時間、訂單建立時間** (不隨時區變) |
TIMESTAMP | 4-7 | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC | 微秒 | **是** (儲存 UTC，讀取轉 session 時區) | **修改時間戳 updated_at**、跨時區系統 |
YEAR | 1 | 1901 ~ 2155 | 年 | 否 | 年份維度 |
DATETIME vs TIMESTAMP 選型決策
需要 2038 年後支援？ → DATETIME
需要自動時區轉換？ → TIMESTAMP
業務語意為「本地牆鐘時間」？ → DATETIME
業務語意為「絕對時間點」？ → TIMESTAMP
索引範圍查詢頻繁？ → 兩者皆可，DATETIME 範圍更大
四、空間與 JSON 型別 (MySQL 5.7+)
GEOMETRY、POINT、LINESTRING、POLYGON 等空間型別，支援 SPATIAL INDEX (R-tree)，適用 LBS、GIS。JSON 型別：二進制儲存、支援路徑查詢 (->, ->>)、虛擬欄位索引、JSON_TABLE (8.0) 關聯查詢。
五、欄位約束
5.1 主鍵
CREATE TABLE t (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);
- 隱含
NOT NULL + UNIQUE。
- InnoDB 聚簇索引即主鍵，**務必單調遞增** (自增或雪花)。
5.2 外鍵
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES user(id)
ON DELETE RESTRICT ON UPDATE CASCADE;
ON DELETE: RESTRICT (預設阻止)、CASCADE (級聯刪除)、SET NULL (需欄位可空)。- **高併發核心業務建議應用層維護關聯**，關閉外鍵 (減少鎖爭用、死鎖、備份恢復複雜度)。
5.3 索引
-- 普通索引
KEY idx_status (status)
-- 唯一索引
UNIQUE KEY uk_email (email)
-- 複合索引 (最左前綴)
KEY idx_status_created (status, created_at)
-- 全文索引 (中文需 ngram parser)
FULLTEXT KEY ft_content (content) WITH PARSER ngram
5.4 自增
AUTO_INCREMENT 僅限整數型別，每表一個。innodb_autoinc_lock_mode=2 (interleaved, 8.0 預設) 高併發插入效能最佳。- 手動指定值會重置計數器：
INSERT ... VALUES (100, ...) → 下次自增 101。
5.5 非空與預設值
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0:草稿 1:發布 2:下架',
email VARCHAR(128) NOT NULL DEFAULT '' COMMENT '空字串而非 NULL',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
5.6 唯一約束
UNIQUE KEY uk_xxx (col) 允許一個 NULL (多個 NULL 不視為重複)。- 業務主鍵 (如 email、手機) 必須加唯一索引。
5.7 CHECK 約束 (MySQL 8.0.16+)
price DECIMAL(19,4) NOT NULL CHECK (price >= 0),
status TINYINT NOT NULL CHECK (status IN (0,1,2)),
CHECK (end_at > start_at)
六、完整建表範例：訂單表
CREATE TABLE `order` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
`order_no` CHAR(32) NOT NULL COMMENT '訂單號 (UUID無連字號)',
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '下單用戶',
`total_amount` DECIMAL(19,4) NOT NULL DEFAULT 0.0000 CHECK (`total_amount` >= 0) COMMENT '訂單金額',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 CHECK (`status` IN (0,1,2,3,4)) COMMENT '0待付款 1已付款 2已發貨 3已完成 4已取消',
`pay_at` DATETIME NULL COMMENT '支付時間',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
`deleted_at` DATETIME NULL COMMENT '軟刪除時間',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_status` (`user_id`, `status`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='訂單表';
結語： 型別選型原則：**「夠用就好、越小越好、語意精確」**。約束設計原則：**「資料完整性由資料庫守護、業務邏輯由應用層守護」**。定期用 pt-table-checksum 驗證主從一致性，用 EXPLAIN 驗證索引覆蓋，讓 Schema 成為系統穩健演進的基石。
