MySQL TIMESTAMP 默認值與自動更新共存版本差異解析

MySQL TIMESTAMP 默認值與自動更新共存版本差異解析

建表時同時需要 創建時間（默認 CURRENT_TIMESTAMP） 與 更新時間（ON UPDATE CURRENT_TIMESTAMP），MySQL 5.5/5.6 版本有嚴格限制，5.7+ 已放開。本文詳細說明版本差異與遷移方案。

一、問題重現

CREATE TABLE RS_SIGNUPUSER (
  ID VARCHAR(36) PRIMARY KEY,
  CREATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '註冊時間',
  UPDATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間'
);

MySQL 5.5/5.6 報錯

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

二、官方文檔限制說明

5.5/5.6：Timestamp Initialization

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

5.7+：已移除該限制，支援多欄位獨立使用 DEFAULT/ON UPDATE。

三、低版本解決方案

方案 1：升級到 MySQL 5.7+（推薦）

原生支援，無需代碼變更，同時獲得性能、優化器、JSON、GIS 等新特性。

方案 2：觸發器模擬自動更新

CREATE TABLE RS_SIGNUPUSER (
  ID VARCHAR(36) PRIMARY KEY,
  CREATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UPDATETIME TIMESTAMP DEFAULT '0000-00-00 00:00:00'
);

DELIMITER //
CREATE TRIGGER trg_update_time BEFORE UPDATE ON RS_SIGNUPUSER
FOR EACH ROW BEGIN
  SET NEW.UPDATETIME = CURRENT_TIMESTAMP;
END//
DELIMITER ;

方案 3：應用層維護

• INSERT 時不寫 CREATETIME（由默認值填充）。
• UPDATE 時在應用代碼顯式 SET UPDATETIME = NOW()。
• ORM 框架（Hibernate/MyBatis/JPA）均支援 @CreationTimestamp/@UpdateTimestamp 自動填充。

方案 4：使用 DATETIME + 默認值函數（MySQL 8.0.13+）

CREATE TABLE t (
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

MySQL 8.0.13 起 DATETIME 也支援 DEFAULT CURRENT_TIMESTAMP 與 ON UPDATE，且無 5.6 版本的單欄限制。

四、遷移檢查清單

1. 確認目標版本是否支援多 TIMESTAMP 自動屬性。
2. 若用觸發器，檢查是否與現有觸發器衝突（同表同事件只能有一個 BEFORE UPDATE）。
3. 應用層方案需覆蓋所有寫入路徑（含批量導入、ETL、手工 SQL）。
4. 驗證時區設置：system_time_zone、time_zone 一致，避免時間偏移。

五、最佳實踐建議

• 新項目直接用 MySQL 5.7+ 或 8.0，避開歷史包袱。
• 統一使用 DATETIME(6)（微秒精度）+ 應用層/ORM 維護，跨版本兼容性最好。
• 嚴禁使用 TIMESTAMP 存儲業務時間：2038 年溢出、時區轉換陷阱、隱式轉換導致索引失效。