MySQL TIMESTAMP 完整指南：顯示格式、自動更新與避坑技巧

為何需要 TIMESTAMP？

MySQL 不支援欄位 DEFAULT 為函數（如 NOW()），若需自動記錄建立/更新時間，TIMESTAMP 是首選方案。

TIMESTAMP 顯示格式與儲存特性

關鍵規則：

• 完整格式為 14 位；定義長度為 0 或 >14 強制為 14；1~13 奇數長度向上取偶。
• 所有 TIMESTAMP 實際儲存 14 位完整精度，顯示長度僅影響查詢回傳格式。
• 非法日期會被儲存為 00000000000000。

隱藏資訊與 ALTER TABLE 行為

1. 定義 TIMESTAMP(8) 實際仍存完整年月日時分秒；ALTER TABLE 擴寬欄位時，隱藏的時分秒會顯現。
2. 縮小欄位不會遺失資料，僅改變顯示內容。
3. 直接操作儲存值的函數僅有 UNIX_TIMESTAMP()；若格式化結果不含時分秒，HOUR()、SECOND() 等函數會產生不可預期結果。

自動更新規則

僅第一個 TIMESTAMP 欄位具備自動更新特性，觸發條件：

1. INSERT/LOAD DATA INFILE 未明確指定該欄值。
2. UPDATE 未指定該欄且其他欄位值真正改變（設為原值不觸發）。
3. 明確設定該欄為 NULL。

非第一個 TIMESTAMP 欲自動更新，需明確設為 NULL 或 NOW()。

實戰範例

CREATE TABLE test (
  id INT(3) UNSIGNED AUTO_INCREMENT,
  date1 TIMESTAMP(14),
  date2 TIMESTAMP(14),
  PRIMARY KEY(id)
);

INSERT INTO test (id, date1, date2) VALUES (1, NULL, NULL); -- 兩欄皆為當前時間
INSERT INTO test SET id=2; -- date1=當前時間, date2=0000...

UPDATE test SET id=3 WHERE id=1; -- date1 更新, date2 不變
UPDATE test SET id=1, date1=date1, date2=NOW() WHERE id=3; -- date1 不變, date2 更新

格式化輸出與跨類型轉換

使用 DATE_FORMAT() 將數字格式轉為可讀日期：

SELECT id,
  DATE_FORMAT(date1, '%Y-%m-%d %H:%i:%s') AS date1,
  DATE_FORMAT(date2, '%Y-%m-%d %H:%i:%s') AS date2
FROM test;

日期類型轉換注意：

• DATE → DATETIME/TIMESTAMP：時間部份補 00:00:00。
• DATETIME/TIMESTAMP → DATE：時間部份被截斷。
• TIMESTAMP 範圍限 1970~2037，超出範圍會變為 0。

字串指定日期陷阱：'10:11:12' 在日期語境下會被解析為 2010-11-12；兩位數年份 00-69 對應 2000-2069，70-99 對應 1970-1999。