MySQL 多欄位 TIMESTAMP 設定限制與三種解決方案

MySQL 限制：單表僅能有一個欄位使用 CURRENT_TIMESTAMP 相關屬性（含 DEFAULT CURRENT_TIMESTAMP 與 ON UPDATE CURRENT_TIMESTAMP）。且第一個定義為 NOT NULL 的 TIMESTAMP 欄位會被隱式加上 DEFAULT CURRENT_TIMESTAMP，導致雙欄位設定失敗。

需求場景

• createtime：Insert 時自動填入當前時間
• updatetime：Update 時自動更新為當前時間

三種解決方案優劣分析

方案一：觸發器

在 BEFORE INSERT、BEFORE UPDATE 觸發器中手動賦值。

• ✅ 完全符合需求，邏輯集中
• ❌ 增加資料庫物件複雜度，維護成本高

方案二：首欄位設 DEFAULT 0 規避隱式預設值（推薦折衷）

CREATE TABLE `device` (
  `createtime` TIMESTAMP NOT NULL DEFAULT 0 COMMENT '建立時間',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間'
) ENGINE=InnoDB;

操作調整：

INSERT INTO device SET toid=11, createtime=NULL;  -- 必須顯式傳 NULL 觸發 0 → 實際會存 0000-00-00，應用層需處理
UPDATE device SET toid=22 WHERE id=1;  -- updatetime 自動更新

• ✅ 無額外物件，僅微調 SQL
• ❌ 需插入 NULL 觸發，語意不直觀；0000-00-00 可能導致應用層報錯

方案三：應用層顯式傳入時間戳（最通用、可移植）

INSERT INTO device SET toid=11, createtime='2024-01-15 10:30:00', updatetime='2024-01-15 10:30:00';
UPDATE device SET toid=22, updatetime='2024-01-15 10:35:00' WHERE id=1;

• ✅ 完全繞過資料庫特有語法，遷移其它 DB（PostgreSQL、Oracle）零修改
• ✅ 語意清晰，掌控權在應用層
• ❌ 多寫幾行代碼

最終建議

若專案無跨資料庫遷移需求且接受觸發器，**方案一**最純粹；追求簡單可維護且避免 0000-00-00 坑，**方案三**是工業界主流選擇。