MySQL 同時擁有 created_at 與 updated_at 時間戳：5.5 變通與 5.6+ 原生支援

MySQL 同時擁有 created_at 與 updated_at 時間戳：5.5 變通與 5.6+ 原生支援

業務常需「創建時間不可變、更新時間自動刷新」的雙時間戳列。MySQL 5.5 單表僅允許一個 TIMESTAMP 列使用 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP，5.6+ 解除此限制。本文梳理四種方案及版本演進。

版本限制回顧

5.5 文檔：一張表最多一個 TIMESTAMP 列可自動初始化/更新。
5.6.5 變更：移除限制，任意 TIMESTAMP/DATETIME 列均可組合 DEFAULT 與 ON UPDATE 子句。

方案一：created_at 用 DEFAULT，updated_at 用觸發器（5.5 通用）

CREATE TABLE temp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10),
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL
);

DELIMITER $$
CREATE TRIGGER tri_temp_updated_at BEFORE UPDATE ON temp
FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); END$$
DELIMITER ;

• 插入時 created_at 自動賦值，updated_at 需顯式傳入或為 NULL。
• 更新時觸發器維護 updated_at。
• 缺點：觸發器增加寫入開銷；若插入未給 updated_at 會存 NULL。

方案二：created_at 用觸發器，updated_at 用 DEFAULT+ON UPDATE（5.5 通用）

CREATE TABLE temp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10),
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER tri_temp_created_at BEFORE INSERT ON temp
FOR EACH ROW BEGIN IF NEW.created_at IS NULL THEN SET NEW.created_at = NOW(); END IF; END$$
DELIMITER ;

• updated_at 完全自動化，插入更新皆刷新。
• created_at 由觸發器在 INSERT 時賦值，避免 NULL。
• 推薦 5.5 環境採用，語義清晰。

方案三：created_at 給零值默認，updated_at 用自動屬性（5.5 可用，但有副作用）

CREATE TABLE temp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10),
  created_at TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• 利用「零值不算自動初始化」繞過限制。
• 缺點：NO_ZERO_DATE SQL 模式下會報錯；零值語義模糊，不推薦新專案使用。

方案四：升級 MySQL 5.6+，原生支援雙自動時間戳（最佳）

CREATE TABLE temp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10),
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

驗證：

INSERT INTO temp(name) VALUES('robin'),('wentasy');
UPDATE temp SET name='robinwen' WHERE id=1;
-- created_at 保持創建時刻，updated_at 自動刷新

• 無觸發器、無零值、語義標準、性能最優。
• DATETIME 類型同樣支援（5.6.5+）。

最佳實踐建議

避坑提醒

• DEFAULT CURRENT_TIMESTAMP 列若顯式插入 NULL，會存 NULL（而非當前時間），需定義 NOT NULL 或觸發器兜底。
• 5.5 下若表有多個 TIMESTAMP 列且未顯式指定默認值，第一個會隱式獲得 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，易導致「創建時間隨更新變化」的誤解。
• 複製環境下，觸發器需在主從同步建立，否則從庫時間戳不一致。

總結

MySQL 5.6+ 徹底解決雙自動時間戳痛點，升級是根治之道。受限於 5.5 時，方案二（updated_at 原生自動 + created_at 觸發器賦值） 是工程上最穩健的折中。新架構建議直接使用 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP + ON UPDATE CURRENT_TIMESTAMP，並由應用層統一時區處理。