MySQL TIMESTAMP 時間戳完整用法詳解與最佳實踐

MySQL TIMESTAMP 時間戳完整用法詳解

在 MySQL 資料庫中，TIMESTAMP 是一種特殊的時間資料類型，常用於自動記錄資料的建立與修改時間。本文將深入解析其各種變體、顯示格式、儲存特性以及實際操作中的注意事項。

一、TIMESTAMP 的四種常見變體

建立資料表時，可以透過不同的屬性組合來定義 TIMESTAMP 欄位的行為：

1. 建立與更新皆自動刷新：
   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
2. 僅建立時自動設定，更新不變：
   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
3. 建立時設為 0，更新時自動刷新：
   TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
4. 建立時指定預設值，更新時自動刷新：
   TIMESTAMP DEFAULT 'yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

注意：MySQL 不支援將函數直接作為欄位的 DEFAULT 值，若需實現「預設為當前時間」的功能，必須使用 TIMESTAMP 類型。

二、TIMESTAMP 顯示格式與儲存特性

TIMESTAMP 的有效範圍為 1970 年至 2037 年，精度為秒。其顯示格式取決於定義時指定的長度（M）：

關鍵規則：

• 「完整」格式為 14 位，常用尺寸為 6、8、12、14
• 定義長度為 0 或 >14 會被強制為 14
• 1~13 的奇數長度會被強制為下一個偶數
• 所有 TIMESTAMP 欄位儲存大小相同，皆以 14 位完整精度儲存
• 查詢時依定義長度回傳格式化後的值

三、顯示長度對資料的影響

雖然建表時定義 TIMESTAMP(8)，實際儲存仍包含完整的年月日時分秒。透過 ALTER TABLE 擴寬欄位可顯示被「隱藏」的時間資訊；縮小欄位不會導致資料遺失，僅影響顯示。

函數操作限制：直接操作儲存值的函數僅有 UNIX_TIMESTAMP()。若欄位定義長度不足（如 TIMESTAMP(8)），使用 HOUR()、SECOND() 等函數會產生不可預期結果。

四、非法值處理與多欄位自動更新規則

非法的 TIMESTAMP 值會被轉換為對應類型的「零值」（00000000000000）。

若資料表有多個 TIMESTAMP 欄位，只有第一個 會在以下情況自動更新：

1. INSERT 或 LOAD DATA INFILE 未明確指定欄位值
2. UPDATE 未指定該欄位且其他欄位值實際改變
3. 明確設定該欄位為 NULL

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

五、日期類型轉換與常見陷阱

• DATE → DATETIME/TIMESTAMP：時間部分補 00:00:00
• DATETIME/TIMESTAMP → DATE：時間部分被捨棄
• 範圍限制：TIMESTAMP 不能早於 1970 年或晚於 2037 年，超出範圍的值會變為 0

字串格式陷阱：'10:11:12' 在日期語境下會被解析為 '2010-11-12'；'10:45:15' 因月份 45 不合法而變為零值。

兩位數年份規則：00-69 → 2000-2069；70-99 → 1970-1999。

六、格式化輸出建議

使用 DATE_FORMAT() 將數字格式的 TIMESTAMP 轉為易讀格式：

SELECT DATE_FORMAT(date_col, '%Y-%m-%d %H:%i:%s') FROM table_name;