MySQL TIMESTAMP vs DATETIME 深度對比：儲存機制、時區行為與自動初始化陷阱

MySQL TIMESTAMP vs DATETIME 深度對比：儲存機制、時區行為與自動初始化陷阱

時間類型選型直接影響跨時區應用正確性、歷史資料相容性與維護成本。本文從儲存原理、時區轉換、範圍差異、自動屬性四大維度深度解析，助你做出正確選擇。

一、當前時間表達式總覽

MySQL 提供多種等效函數：

• CURRENT_TIMESTAMP / CURRENT_TIMESTAMP()
• NOW()
• LOCALTIME / LOCALTIME() / LOCALTIMESTAMP / LOCALTIMESTAMP()

二、核心差異對比

1. 儲存與讀取機制（根本差異）

實測驗證

-- 建表
CREATE TABLE test_ts (id INT, hiredate TIMESTAMP);
CREATE TABLE test_dt (id INT, hiredate DATETIME);

-- 插入同一時間
INSERT INTO test_ts VALUES (1, '2015-12-08 00:00:00');
INSERT INTO test_dt VALUES (1, '2015-12-08 00:00:00');

-- 修改會話時區為 UTC
SET time_zone = '+0:00';

-- 查詢結果
SELECT * FROM test_ts;  -- 2015-12-07 16:00:00 (提前 8 小時，CST→UTC)
SELECT * FROM test_dt;  -- 2015-12-08 00:00:00 (不變)

結論：跨時區業務必選 TIMESTAMP；單時區、歷史歸檔、需保留原始輸入時間選 DATETIME。

2. 儲存範圍差異

三、自動初始化與更新機制

MySQL 5.6.5 前：僅首個 TIMESTAMP 欄位預設 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，且限一個欄位。

5.6.5+：TIMESTAMP/DATETIME 均支援，無數量限制。

隱式行為示例

CREATE TABLE t (id INT, ts TIMESTAMP);
INSERT INTO t(id) VALUES (1);  -- 未給 ts 值
-- 實際執行：ts 自動填入 CURRENT_TIMESTAMP
-- SHOW CREATE TABLE 顯示：ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

禁用自動屬性三種方式

1. 啟用參數：SET explicit_defaults_for_timestamp = ON;
2. 顯式指定 DEFAULT：ts TIMESTAMP DEFAULT '0000-00-00 00:00:00'
3. 允許 NULL：ts TIMESTAMP NULL（預設值變為 NULL）

四、選型決策樹

1. 需跨時區顯示 → TIMESTAMP
2. 資料超出 2038 年或早於 1970 年 → DATETIME
3. 單時區、需保留原始輸入時間（如日誌、審計） → DATETIME
4. 需要多個自動更新時間欄位（5.6.5+） → DATETIME 更靈活

參考：DATETIME、Timestamp Initialization