MySQL 中 DATE、DATETIME 和 TIMESTAMP 的區別

在 MySQL 中，DATE、DATETIME 和 TIMESTAMP 都是用于存儲日期和時間的數據類型，但它們在格式、范圍、存儲大小、時區處理和功能上存在顯著差異。以下將逐步對比這些區別，幫助您根據實際需求選擇合適的數據類型。

1. 定義和基本用途

• DATE：僅存儲日期部分（年、月、日），不包括時間信息。適用于只需要記錄日期的場景，如生日、事件日期。
• DATETIME：存儲日期和時間（年、月、日、時、分、秒）。適用于需要精確時間點的場景，如訂單創建時間、日志記錄。
• TIMESTAMP：也存儲日期和時間，但與時區相關。它基于 Unix 時間戳（從 ‘1970-01-01 00:00:00’ UTC 開始的秒數），并自動進行時區轉換。適用于需要自動更新或國際化處理的場景，如用戶登錄時間。

2. 格式和有效范圍

• DATE：
  • 格式：YYYY-MM-DD。
  • 范圍：‘1000-01-01’ 到 ‘9999-12-31’。
  • 示例：2023-10-05
• DATETIME：
  • 格式：YYYY-MM-DD HH:MM:SS。
  • 范圍：‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
  • 示例：2023-10-05 14:30:00'
• TIMESTAMP：
  • 格式：YYYY-MM-DD HH:MM:SS，但在存儲時轉換為 UTC 時間。
  • 范圍：‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC（由于 4 字節存儲限制，稱為“2038 年問題”）。
  • 示例：2023-10-05 14:30:00'

3. 存儲大小

• DATE：占用 3 字節。
• DATETIME：占用 8 字節（在 MySQL 5.6.4 及之后版本中占用5字節）。
• TIMESTAMP：占用 4 字節。

存儲大小影響性能和空間效率：TIMESTAMP 更緊湊，但范圍較小；DATETIME 范圍更大但占用更多空間；DATE 適用于純日期場景。

4. 時區處理

• DATE 和 DATETIME：存儲為字面值，不進行時區轉換。插入和檢索時值保持不變（例如，插入 ‘2023-10-05 14:30:00’ 會原樣存儲）。
• TIMESTAMP：存儲時轉換為 UTC 時間，檢索時根據 MySQL 服務器的時區設置或會話時區轉換回本地時間。例如，如果服務器時區為 UTC+8，插入 ‘2023-10-05 14:30:00’ 會存儲為 UTC 時間 ‘2023-10-05 06:30:00’，檢索時再轉換回 ‘2023-10-05 14:30:00’。

5. 自動更新功能

• TIMESTAMP：支持自動更新特性。可以在表定義中設置 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP，使其在插入或更新時自動設置為當前時間（例如，用于記錄最后修改時間）。
• DATETIME：在 MySQL 5.6.5 及之后版本也支持自動更新（如 DEFAULT CURRENT_TIMESTAMP），但 TIMESTAMP 更常用且兼容性好。
• DATE：不支持自動更新。

6. 使用場景建議

• 使用 DATE 當：只關心日期，如員工入職日期、活動日期。
• 使用 DATETIME 當：需要完整日期和時間，且范圍較大（超過 2038 年）或不需要時區轉換，如歷史數據記錄。
• 使用 TIMESTAMP 當：需要自動更新時間、處理時區敏感數據或節省存儲空間，如用戶會話跟蹤、國際化應用。

總結關鍵區別

特性	DATE	DATETIME	TIMESTAMP
存儲內容	僅日期 (YYYY-MM-DD)	日期和時間 (YYYY-MM-DD HH:MM:SS)	日期和時間 (YYYY-MM-DD HH:MM:SS)
范圍	1000-01-01 到 9999-12-31	1000-01-01 00:00:00 到 9999-12-31 23:59:59	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
存儲大小	3 字節	8 字節 （5字節）	4 字節
時區處理	無轉換	無轉換	存儲為 UTC，檢索時轉換
自動更新	不支持	支持（5.6.5+）	支持

在實際應用中，選擇數據類型時需考慮數據范圍、時區需求和存儲效率。例如，對于用戶注冊時間，優先使用 TIMESTAMP 以利用自動更新；對于長期歷史數據，DATETIME 更可靠。建議參考 MySQL
官方文檔測試具體版本行為。

其他時間字段

1. TIME

   • 存儲內容：時間（時分秒）
   • 格式：HH:MM:SS
   • 范圍：-838:59:59 到 838:59:59（可表示時間間隔）
   • 示例：14:30:45 或 -02:30:00（表示負時間間隔）

2. YEAR

   • 存儲內容：年份
   • 格式：YYYY（4 位）或 YY（2 位，已棄用）
   • 范圍：1901 到 2155
   • 示例：2023

3. 微秒支持（MySQL 5.6.4+）

   • 通過附加精度參數實現（如 DATETIME(3) 存儲毫秒）
   • 格式：YYYY-MM-DD HH:MM:SS.ffffff
   • 示例：2023-10-05 14:30:45.123456

---

選擇建議

• 只需日期 → DATE
• 需完整時間且不涉及時區 → DATETIME
• 需自動時區轉換或記錄更新時間 → TIMESTAMP
• 僅需時間或時間間隔 → TIME
• 僅需年份 → YEAR

示例：

-- 創建包含三種類型的表
CREATE TABLE example (date_col DATE comment '創建日期',time_col TIME comment '時間',year_col YEAR comment '年份',datetime_col DATETIME DEFAULT CURRENT_TIMESTAMP comment '創建時間',timestamp_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '時間戳'
);