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

MySQL NOW() 與 TIMESTAMP 類型深度解析：自動初始化/更新、顯示寬度陷阱與時區行為

編輯：MySQL 資料庫相關知識

前言：時間戳自動維護是資料庫級審計的基石

MySQL 不支援 DEFAULT NOW()，但 TIMESTAMP 類型天然具備「首列自動初始化/更新」特性。本文深度解析 TIMESTAMP 顯示寬度規則、存儲實際精度、自動更新觸發條件、NOW() vs SYSDATE()、時區轉換、與 DATETIME 互轉損失，助你正確設計時間欄位。

一、TIMESTAMP 顯示寬度 vs 存儲精度

定義	顯示格式	存儲精度	備註
TIMESTAMP(14)	YYYYMMDDHHMMSS	全部 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, 13→14)。
存儲永遠 14 位完整精度，顯示寬度僅影響 SELECT 輸出格式。
ALTER TABLE ... MODIFY ts TIMESTAMP(14) 可「展寬」顯示隱藏的時分秒。

二、自動初始化/更新機制 (核心特性)

觸發條件 (僅第一個 TIMESTAMP 列)

INSERT / LOAD DATA 未顯式指定該列值。
UPDATE 修改了其他列，且未顯式指定該列值 (若 SET ts=ts 則不更新，MySQL 優化忽略無實際變更的賦值)。
顯式 SET ts = NULL。

非首列 TIMESTAMP 手動觸發

UPDATE t SET ts2 = NULL WHERE ...;   -- 更新為當前時間
UPDATE t SET ts2 = NOW() WHERE ...;  -- 同上

實戰演示

CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP(14),  -- 首列，自動
  ts2 TIMESTAMP(14)   -- 非首列，預設 0
);

INSERT INTO t (id, ts1, ts2) VALUES (1, NULL, NULL); -- 兩列均為當前時間
INSERT INTO t SET id=2;                              -- ts1=當前, ts2=0

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

三、NOW() vs SYSDATE() vs CURRENT_TIMESTAMP

函數	取值時機	複制行為	觸發器/存儲過程
NOW()	語句開始執行時固定	主從一致 (複製語句文本)	同一調用上下文固定
SYSDATE()	實時執行時取值	主從可能不一致 (需 row 格式)	每次調用取最新
CURRENT_TIMESTAMP	同 NOW()	同 NOW()	同 NOW()

建議：業務時間用 NOW()，審計/日誌用 SYSDATE()，TIMESTAMP 自動行為語義等同 NOW()。

四、格式化輸出：FROM_UNIXTIME / DATE_FORMAT

SELECT DATE_FORMAT(ts1, '%Y-%m-%d %H:%i:%s') FROM t;
-- 2024-05-20 14:30:45

SELECT DATE_FORMAT(ts1, '%Y-%m-%d') FROM t;
-- 2024-05-20

五、日期類型互轉與資訊損失

來源 → 目標	結果	損失
DATE → DATETIME/TIMESTAMP	時間部份補 00:00:00	無 (補零)
DATETIME/TIMESTAMP → DATE	截取日期部份	時間部份丟失
DATETIME → TIMESTAMP	時區轉換 (系統時區 → UTC 存儲)	超出 1970-2038 範圍變 0
TIMESTAMP → DATETIME	UTC → 系統時區還原	無 (範圍內)

六、常見陷阱與避坑

TIMESTAMP 範圍 1970-01-01 至 2038-01-19，歷史/遠期數據必須用 DATETIME。
兩位數年份解析：00-69 → 2000-2069，70-99 → 1970-1999；永遠用四位年份。
'10:11:12' 在日期語境下被解析為 2010-11-12 (冒號誤導)；'10:45:15' 月份 45 非法 → 0000-00-00。
HOUR(ts) 等時間提取函數要求顯示寬度含對應部份 (如 TIMESTAMP(10) 以上才有時)；否則結果不可預期。解決：用 TIMESTAMP(14) 或 DATETIME。
字面 NULL 插入 TIMESTAMP 會觸發自動賦值，若需存真 NULL → 定義 NULL DEFAULT NULL (5.6.6+ 支援 DEFAULT NULL 且不自動更新，需 EXPLICIT_DEFAULTS_FOR_TIMESTAMP=ON)。

七、最佳實踐建議

建立時間：created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP (5.6.6+ 可顯式 DEFAULT CURRENT_TIMESTAMP)。
修改時間：updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP (5.6.5+ 支援)。
若需 NULL 表「未設定」：created_at TIMESTAMP NULL DEFAULT NULL + 應用層顯式賦值。
跨時區應用：統一存儲 UTC (TIMESTAMP 自動處理)，應用層按用戶時區展示。
歷史/未來日期、精確審計、需索引範圍查詢 → 優先 DATETIME。