MySQL TIMESTAMP 默認值與自動更新機制深度解析：從陷阱到最佳實踐

前言

MySQL TIMESTAMP 的默認值與 ON UPDATE 行為存在大量隱式規則，稍有不慎即陷入「時間不更新」「唯一索引衝突」「建表報錯」等坑。本文通過 8 個遞進實驗，系統揭示 TIMESTAMP 隱式屬性、單列限制、修改姿勢，助你精準掌控時間列行為。

一、 核心隱式規則

二、 八大實驗遞進驗證

實驗 1：雙 TIMESTAMP 表，無顯式定義

CREATE TABLE dj1 (a CHAR(1), b TIMESTAMP, c TIMESTAMP);
INSERT INTO dj1 VALUES (1,NULL,NULL), (2,NULL,NULL);
-- 結果：b、c 均為 INSERT 時刻，b 具備 ON UPDATE 自動更新，c 固定不變

實驗 2：更新觸發 b 自動更新，c 不變

UPDATE dj1 SET a=9 WHERE a=1;
-- b 變為更新時刻，c 保持插入時刻

實驗 3：再次更新驗證

UPDATE dj1 SET a=8 WHERE a=2;
-- b 再次更新，c 不變

實驗 4：b 加唯一索引，全表更新報 Duplicate Entry

CREATE UNIQUE INDEX dj1_idx_u1 ON dj1(b);
UPDATE dj1 SET a=1;  -- ERROR 1062: Duplicate entry '2009-09-09 13:54:45' for key 'dj1_idx_u1'
-- 原因：所有行 b 同時更新為同一秒時間戳，唯一索引衝突

實驗 5：SHOW CREATE TABLE 看隱式屬性

SHOW CREATE TABLE dj1\G
-- b: timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- c: timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

實驗 6：建表 dj2，b 僅 DEFAULT CURRENT_TIMESTAMP（無 ON UPDATE）

CREATE TABLE dj2 (a CHAR(1), b TIMESTAMP DEFAULT CURRENT_TIMESTAMP, c TIMESTAMP DEFAULT '0000-00-00 00:00:00');
-- 更新 dj2 不再觸發 b 自動更新，唯一索引無衝突

實驗 7：嘗試兩列都帶 CURRENT_TIMESTAMP → 報錯 1293

CREATE TABLE dj3 (b TIMESTAMP DEFAULT CURRENT_TIMESTAMP, c TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
-- ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

實驗 8：各種雙列組合均報 1293

-- dj4: b DEFAULT CURRENT_TIMESTAMP, c DEFAULT '0' ON UPDATE CURRENT_TIMESTAMP → 1293
-- dj5: b DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c DEFAULT CURRENT_TIMESTAMP → 1293

三、 修改現有列屬性的正確姿勢

-- 1. 查看當前定義
SHOW CREATE TABLE tbl_ledgerrecord\G

-- 2. 移除自動更新，僅保留插入時默認
ALTER TABLE tbl_ledgerrecord 
CHANGE intoStorageDate intoStorageDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 3. 完全關閉自動屬性（需 5.6.5+）
ALTER TABLE tbl_ledgerrecord 
CHANGE intoStorageDate intoStorageDate TIMESTAMP DEFAULT '0000-00-00 00:00:00';

-- 4. 明確指定 ON UPDATE（僅限非第一個 TIMESTAMP 列）
ALTER TABLE tbl_ledgerrecord 
CHANGE updateTime updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

四、 最佳實踐清單

1. 顯式定義一切：不依賴隱式規則，所有 TIMESTAMP 列寫明 DEFAULT 與 ON UPDATE
2. 創建時間列：created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP（無 ON UPDATE）
3. 更新時間列：updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP（放在第 2+ 位）
4. 避免唯一索引：updated_at 絕不可加唯一索引，高併發必衝突
5. 5.6.5 前遷移注意：舊版隱式行為不同，遷移時必須顯式重定義所有時間列
6. 時區一致性：TIMESTAMP 存儲為 UTC，讀取按會話時區轉換；跨時區應用統一應用層處理

五、 總結

MySQL TIMESTAMP 的「第一列自動獲得 ON UPDATE」「全表僅一列可用 CURRENT_TIMESTAMP」兩大隱式規則，是無數遷移故障與詭異 Bug 的根源。顯式定義、錯位放置、拒絕唯一索引 三原則，讓時間列行為完全可控、可預測、可維護。