您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 日期時間類型深度對比：DATETIME vs TIMESTAMP 選型指南

編輯：MySQL 綜合教學

MySQL 日期時間類型深度對比：DATETIME vs TIMESTAMP 選型指南

MySQL 5.x 提供 DATETIME、DATE、TIMESTAMP 三種日期類型。本文聚焦帶時間元件的兩者，解析儲存範圍、預設值行為、時區轉換、自動更新機制，給出建表選型建議。

核心差異速查表

特性	DATETIME	TIMESTAMP
儲存範圍	1000-01-01 ~ 9999-12-31	1970-01-01 ~ 2038-01-19 (32-bit 有符號整數秒數)
儲存空間	8 bytes (5.6.4 前) / 5 bytes + 小數秒 (5.6.4+)	4 bytes (固定)
時區轉換	不轉換，存什麼取什麼	寫入時 UTC→儲存，讀取時儲存→Session 時區
預設值	5.6.5 前不支援 DEFAULT CURRENT_TIMESTAMP 5.6.5+ 支援	預設 `CURRENT_TIMESTAMP`，且自動更新為 `ON UPDATE CURRENT_TIMESTAMP`
NULL 允許	預設允許 NULL	預設 NOT NULL，需顯式指定 NULL
多欄位限制	可建立多個	單表僅一欄可擁有自動初始化/更新屬性 (5.6.5 前)

TIMESTAMP 關鍵行為詳解

必須有預設值，不可為 NULL (除非顯式 NULL DEFAULT NULL)。
不可手動更新：UPDATE t SET ts_col = '2020-01-01' ... 會被忽略，值由系統自動維護。
單表僅一欄自動更新：定義多個 TIMESTAMP 只有第一個 (或顯式指定) 具備 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 行為，其餘需手動指定預設值 (如 '0000-00-00 00:00:00') 但將失去自動更新能力。

格式範例：

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

DATETIME 靈活性優勢

可建立多個 DATETIME 欄位，完全手動控制。
5.6.5 前不支援 DEFAULT CURRENT_TIMESTAMP，常用 觸發器 或應用層 NOW() 實現建立/更新時間自動填充。
5.6.5+ 可直接：created_at DATETIME DEFAULT CURRENT_TIMESTAMP、updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

建表最佳實踐範例

CREATE TABLE `user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) CHARACTER SET utf8mb4 NOT NULL,
  `sex` TINYINT(1) DEFAULT 1,
  `state` SMALLINT(2) DEFAULT 1,
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

策略：create_time 用 DATETIME 固化建立瞬間 (不受時區影響)；update_time 用 TIMESTAMP 自動追蹤最後修改 (含時區感知)。

常見誤區與陷阱

誤區：TIMESTAMP 範圍到 2038 年會爆炸。事實：MySQL 8.0 已支援 64-bit TIMESTAMP (至 3000 年)，但 5.7 仍受限。長期專案建議改用 DATETIME 或 BIGINT 存儲毫秒時間戳。
誤區：編程語言日期範圍 = MySQL 日期範圍。事實：Java java.sql.Timestamp 範圍更大，但 ORM 映射時仍受資料庫限制。應用層需自行驗證邊界。
陷阱：TIMESTAMP 受 time_zone 系統變數影響。跨時區部署時，確保 Master/Slave/應用 time_zone 一致，否則讀取值會偏移。

日期格式轉換與運算速查

-- 字串 → 日期
SELECT STR_TO_DATE('2023-08-15 14:30:00', '%Y-%m-%d %H:%i:%s');

-- 日期 → 字串
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- 日期部分提取
SELECT YEAR(dt), MONTH(dt), DAY(dt), HOUR(dt), MINUTE(dt), SECOND(dt);

-- 日期運算
SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH);
SELECT DATEDIFF('2023-12-31', '2023-01-01');  -- 天數差

-- 比較 (字串可直接比較，格式需統一)
WHERE update_time > '2023-08-01 00:00:00' AND update_time <= '2023-08-15 23:59:59'