您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL TIMESTAMP 時間戳類型完整指南：變體、顯示尺寸、自動更新規則與陷阱

編輯：MySQL 綜合教學

前言：TIMESTAMP 不只是「存時間」，還藏著自動更新機制

MySQL 的 TIMESTAMP 類型除儲存日期時間外，還具備自動初始化、自動更新特性，常用於「創建時間」、「最後修改時間」欄位。但其顯示尺寸規則、零值處理、時區轉換、版本差異等細節極易踩坑。本文系統整理官方文檔與實測結論，作為查閱手冊。

一、四大自動更新變體（建表時指定）

定義語法	創建行時	更新行時	典型用途
`TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	當前時間	刷新為當前時間	最後修改時間
`TIMESTAMP DEFAULT CURRENT_TIMESTAMP`	當前時間	不變	創建時間
`TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	0（零值）	刷新為當前時間	極少用
`TIMESTAMP DEFAULT '2023-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP`	指定固定值	刷新為當前時間	業務預設時間

注意：MySQL **不支援** DEFAULT NOW() 等函數作為預設值，只能用 TIMESTAMP 類型實現自動時間戳。

二、顯示尺寸與存儲的「冰山理論」

列類型定義	顯示格式	實際存儲
`TIMESTAMP(14)`	YYYYMMDDHHMMSS	統一 4 字節，存儲 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 之間的秒數
`TIMESTAMP(12)`	YYMMDDHHMMSS
`TIMESTAMP(10)`	YYMMDDHHMM
`TIMESTAMP(8)`	YYYYMMDD
`TIMESTAMP(6)`	YYMMDD
`TIMESTAMP(4)`	YYMM
`TIMESTAMP(2)`	YY

關鍵結論：

**顯示尺寸只影響「查詢時返回的字串格式」，不影響磁碟存儲**（永遠 4 字節、全精度）。
定義長度 0 或 >14 → 強制 14；1~13 奇數 → 強制下一個偶數（如 1→2, 5→6）。
ALTER TABLE ... MODIFY col TIMESTAMP(14) 擴寬列，**「隱藏」的時分秒會重現**；縮小列不丟失數據，僅顯示截斷。
直接操作存儲值的唯一函數是 UNIX_TIMESTAMP()；若顯示尺寸 < 10（無時分秒），HOUR()、SECOND() 等函數會返回不可預期結果。
非法日期（如 '1968-01-01' 超出 1970 下限）寫入會變為「零值」00000000000000。

三、自動更新觸發條件（僅第一個 TIMESTAMP 列生效）

表中有多個 TIMESTAMP 列時，**只有第一個**享受自動更新特性。觸發條件：

INSERT/LOAD DATA 未顯式指定該列值。
UPDATE 未顯式指定該列值，**且有其他列值真正改變**（SET col=col 不算改變，MySQL 優化跳過）。
顯式 SET ts_col = NULL。
非第一個 TIMESTAMP 列想自動更新 → 必須顯式 SET ts_col = NULL 或 NOW()。

四、實測案例：雙 TIMESTAMP 列行為

CREATE TABLE test (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP(14),
  ts2 TIMESTAMP(14)
);

INSERT INTO test (id, ts1, ts2) VALUES (1, NULL, NULL);  -- 兩列皆得當前時間
INSERT INTO test SET id=2;                                -- ts1=當前時間, ts2=零值

UPDATE test SET id=3 WHERE id=1;                          -- ts1 刷新, ts2 不變
UPDATE test SET id=1, ts1=ts1, ts2=NOW() WHERE id=3;      -- ts1 不變(ts1=ts1), ts2 刷新

查詢時用 DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s') 格式化輸出。

五、與 DATETIME、DATE 互轉的陷阱

轉換方向	結果	說明
DATE → DATETIME/TIMESTAMP	時間部分補 `00:00:00`	DATE 無時間信息
DATETIME/TIMESTAMP → DATE	時間部分截斷丟失	DATE 不存時間
字串 '10:11:12' 寫入 DATE	解析為 `2010-11-12`	冒號被當作日期分隔符
2 位年份	00-69 → 2000-2069; 70-99 → 1970-1999	MySQL 內部規則

六、版本差異與最佳實踐

MySQL 5.6.5+：支援 DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，**不再強依賴 TIMESTAMP** 實現自動更新，且 DATETIME 範圍更大（1000-9999 年）、無時區轉換、無 2038 年問題。
建議：新專案優先用 DATETIME(3)（毫秒精度）+ 應用層/觸發器維護創建/修改時間；舊專案維護既有 TIMESTAMP 即可。
時區：TIMESTAMP 存儲為 UTC，讀取時按 time_zone 會話變量轉換；跨時區應用務必統一會話時區或應用層處理。