MySQL 日期時間類型體系
MySQL 提供多種日期時間類型，粒度至秒（5.6.4 前），5.6.4+ 支援微秒精度（DATETIME(6)、TIMESTAMP(6)、TIME(6)）。選型核心考量：儲存範圍、空間效率、時區行為、自動更新機制、應用相容性。
核心類型對比：DATETIME vs TIMESTAMP
| 維度 | DATETIME | TIMESTAMP |
|---|
| 儲存空間 |
8 bytes (5.6.4 前) / 5+ bytes (5.6.4+ 含微秒) |
4 bytes (5.6.4 前) / 4+ bytes (5.6.4+ 含微秒) |
| 儲存範圍 |
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC |
| 時區敏感 |
否：儲存值與時區無關，顯示不轉換 |
是：儲存 UTC，讀取時按會話時區轉換 |
| 零值 |
0000-00-00 00:00:00 |
0000-00-00 00:00:00 (隱含 1970-01-01 UTC) |
| 自動初始化/更新 |
5.6.5+ 支援 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
預設行為：首個 TIMESTAMP 欄位 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
| NULL 預設 |
可為 NULL（除非指定 NOT NULL） |
預設 NOT NULL，隱含 DEFAULT CURRENT_TIMESTAMP |
| 跨版本顯示 |
統一 YYYY-MM-DD HH:MM:SS |
舊版無分隔符，新版統一格式（僅顯示差異） |
| 函數轉換 |
UNIX_TIMESTAMP(dt) → 秒數 |
FROM_UNIXTIME(sec) → DATETIME 格式 |
選型決策樹
需要儲存日期時間？
├─ 範圍超出 1970~2038（如生日、歷史記錄、長期合約）
│ └─ → DATETIME (8B) 或 DATETIME(6) (微秒)
├─ 範圍在 1970~2038 內（如操作日誌、建立/更新時間、Session 過期）
│ ├─ 需要時區轉換（多時區應用、全球化用戶）
│ │ └─ → TIMESTAMP (4B) 或 TIMESTAMP(6)
│ └─ 不需要時區轉換（單時區、內部系統、固定 UTC 儲存）
│ ├─ 追求空間效率 → TIMESTAMP (4B)
│ └─ 追求功能一致性 → DATETIME (8B)
└─ 只需日期 / 只需時間
├─ 只需日期 → DATE (3B)
└─ 只需時間 → TIME (3B+)
TIMESTAMP 特殊行為詳解與避坑
- 首個 TIMESTAMP 欄位自動屬性：建表時未顯式指定，第一個
TIMESTAMP 自動獲得 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- 顯式關閉自動行為：
ts TIMESTAMP NULL DEFAULT NULL 或 ts TIMESTAMP DEFAULT '2000-01-01'
- 時區陷阱：伺服器
system_time_zone、會話 time_zone、應用連線時區三者不一致導致讀寫值偏移。統一使用 UTC 存儲，應用層轉換顯示
- 2038 年問題：32 位有號整數上限。長期專案必須用 DATETIME 或 64 位系統遷移
- 複寫一致性：主從時區不同會導致 TIMESTAMP 值偏移。行複寫 (RBR) 可避免，語句複寫 (SBR) 必須統一時區
高精度儲存方案（微秒級）
| 方案 | 類型 | 空間 | 精度 | 優點 | 缺點 |
|---|
| 原生微秒 (5.6.4+) |
DATETIME(6) / TIMESTAMP(6) |
5~8 bytes |
微秒 |
標準 SQL、函數支援完整、索引可用 |
需 5.6.4+、TIMESTAMP 仍受 2038 限制 |
| BIGINT 毫秒戳 |
BIGINT UNSIGNED |
8 bytes |
毫秒 |
範圍極大、跨語言相容、應用層易運算 |
需應用層轉換、SQL 日期函數不可用 |
| DOUBLE 秒.小數 |
DOUBLE |
8 bytes |
微秒~奈秒 |
可直接數學運算 |
浮點誤差、日期函數不可用、索引範圍查詢複雜 |
| 分離儲存 |
DATETIME + SMALLINT(毫秒) |
8+2=10 bytes |
毫秒 |
相容舊版本、秒部分可用日期函數 |
查詢需組合、排序複雜 |
實戰最佳實踐
- 建立時間/更新時間欄位標準：
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
- 統一 UTC 存儲：伺服器
SET GLOBAL time_zone = '+00:00'，應用層連線 SET time_zone = '+00:00'
- 避免應用層手動 NOW()：用資料庫預設值，保證寫入時間準確、避免時鐘偏移
- 查詢範圍用開區間：
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'，利用索引、避免函數
- 分區表按時間分區：
PARTITION BY RANGE COLUMNS(created_at) 或 PARTITION BY RANGE (TO_DAYS(created_at))
常見陷阱速查
