您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL TIMESTAMP 時間戳完整指南：四大變體、顯示尺寸、自動更新規則與 2038 年問題

編輯：MySQL 資料庫相關知識

四大定義變體與行為矩陣

定義語法	DEFAULT 值	ON UPDATE 行為	典型用途
`TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	當前時間	自動更新	修改時間 `updated_at`
`TIMESTAMP DEFAULT CURRENT_TIMESTAMP`	當前時間	不更新	創建時間 `created_at`
`TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	`0000-00-00 00:00:00`	自動更新	極少用
`TIMESTAMP DEFAULT '2024-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP`	固定值	自動更新	基準時間 + 自動更新

顯示尺寸與存儲實相

列類型	顯示格式	實際存儲
`TIMESTAMP(14)`	YYYYMMDDHHMMSS	統一 4 字節存儲完整 14 位秒級時間戳
`TIMESTAMP(12)`	YYMMDDHHMMSS
`TIMESTAMP(10)`	YYMMDDHHMM
`TIMESTAMP(8)`	YYYYMMDD
`TIMESTAMP(6)`	YYMMDD
`TIMESTAMP(4)`	YYMM
`TIMESTAMP(2)`	YY

關鍵規則

定義長度 0 或 >14 → 強制 14
奇數長度 → 向上取偶 (1→2, 5→6)
存儲始終完整 14 位，顯示尺寸僅影響查詢返回格式
ALTER TABLE ... TIMESTAMP(14) 可「恢復」被截斷顯示的時分秒

自動更新觸發條件 (僅首個 TIMESTAMP 列)

INSERT/LOAD DATA 未顯式指定該列值
UPDATE 未顯式指定該列值，且其他列有實際變更
顯式設定該列為 NULL
非首個 TIMESTAMP 列：設為 NULL 或 NOW() 可觸發更新

實戰演示

CREATE TABLE ts_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts1 TIMESTAMP(14) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ts2 TIMESTAMP(14) DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO ts_demo (id) VALUES (1);
-- ts1=2024-01-01 10:00:00, ts2=2024-01-01 10:00:00

UPDATE ts_demo SET id=2 WHERE id=1;
-- ts1=2024-01-01 10:00:05 (自動更新), ts2=2024-01-01 10:00:00 (不變)

UPDATE ts_demo SET id=3, ts1=ts1 WHERE id=2;
-- ts1 不變 (ts1=ts1 視為無實際變更), ts2 不變

UPDATE ts_demo SET id=4, ts2=NOW() WHERE id=3;
-- ts1 自動更新, ts2=當前時間 (顯式觸發)

格式化輸出

SELECT id,
    DATE_FORMAT(ts1, '%Y-%m-%d %H:%i:%s') AS ts1_full,
    DATE_FORMAT(ts2, '%Y-%m-%d') AS ts2_date
FROM ts_demo;

跨類型賦值陷阱

源類型 → 目標類型	結果
DATE → DATETIME/TIMESTAMP	時間部分補 `00:00:00`
DATETIME/TIMESTAMP → DATE	時間部分截斷丟失
DATETIME → TIMESTAMP	超出 1970-2038 範圍 → 變 `0`

2038 年問題與避坑

範圍：1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC (32 位有符號整數上限)
風險：長期項目 (銀行、保單、日誌) 將在 2038 年溢出
方案：
- 改用 DATETIME (8 字節，範圍至 9999 年)
- 改用 BIGINT 存儲毫秒級時間戳 (應用層處理)
- MySQL 8.0+ 考慮 TIMESTAMP(6) 微秒擴展 (但範圍未變)