MySQL 5.5 多 TIMESTAMP 列 CURRENT_TIMESTAMP 報錯的觸發器兼容方案

版本差異根因

• MySQL 5.5：單表僅允許 **一個** TIMESTAMP 列使用 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP，不可同時用於不同列
• MySQL 5.6.5+：解除限制，任意 TIMESTAMP/DATETIME 列均可任意組合使用自動初始化/更新子句

典型報錯：ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

業務場景

需同時記錄 created_at (創建時自動賦值) 和 updated_at (更新時自動刷新) 兩個時間戳列。

方案一：觸發器兼容方案 (5.5 必用，通用)

-- 表結構調整：updated_at 改為 DATETIME，去除自動屬性
CREATE TABLE `example` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 保留一個自動 TIMESTAMP
  `lastUpdated` DATETIME NOT NULL,                        -- 改為 DATETIME 手動維護
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 創建 BEFORE UPDATE 觸發器
DROP TRIGGER IF EXISTS `update_example_trigger`;
DELIMITER //
CREATE TRIGGER `update_example_trigger` BEFORE UPDATE ON `example`
FOR EACH ROW
BEGIN
    SET NEW.`lastUpdated` = NOW();  -- 或 UTC_TIMESTAMP() 統一 UTC
END//
DELIMITER ;

優點：兼容 5.5，邏輯清晰可控。缺點：觸發器有微小性能開銷，DDL 變更需同步維護觸發器。

方案二：應用層顯式維護 (推薦現代架構)

不依賴數據庫自動機制，應用層統一設置：

• 插入時：created_at = NOW(), updated_at = NOW()
• 更新時：updated_at = NOW()

配合 ORM 框架 (Hibernate @CreationTimestamp/@UpdateTimestamp, MyBatisPlus @TableField(fill=...), JPA @PrePersist/@PreUpdate) 實現零侵入自動填充。

方案三：升級 MySQL 版本 (根治)

5.6.5+ 直接支持：

CREATE TABLE `example` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

或 DATETIME 組合 (5.6.5+ 同樣支持)：

`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`lastUpdated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

遷移建議

1. 新項目：直接用 5.7+/8.0 + 方案二 (應用層控制) 或方案三 (原生支持)
2. 存量 5.5 系統：若不可升級，採用方案一 (觸發器) 過渡
3. 統一時間源：推薦應用層使用 Instant.now() (UTC 毫秒戳) 寫入 BIGINT 或 DATETIME(3)，徹底擺脫數據庫時區/版本依賴