MySQL TIMESTAMP 欄位 CURRENT_TIMESTAMP 限制差異：5.5 vs 5.6+ 解決方案

MySQL 5.5 僅允許單張表擁有一個 TIMESTAMP 欄位使用 CURRENT_TIMESTAMP (預設值或自動更新)，5.6.5+ 移除此限制。跨版本部署時常遇遷移報錯，本文提供三種解決方案。

錯誤現場

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

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

版本差異根因

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

升級至 5.6.5+ 或 8.0，直接支援多欄位自動時間戳，無需代碼修改。

方案二：使用 DATETIME + 觸發器 (5.5 相容)

將自動更新欄位改為 DATETIME，配合 BEFORE UPDATE 觸發器維護：

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

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();
END//
DELIMITER ;

優點： 5.5 完美相容、語義清晰
缺點： 觸發器增加寫入開銷、維護成本、NOW() 精度為秒

方案三：應用層維護 (完全可控)

在應用代碼 (PHP/Java/Go/Python) 明確設定兩個欄位值：

-- 插入時
INSERT INTO example (created, lastUpdated) VALUES (NOW(), NOW());

-- 更新時
UPDATE example SET lastUpdated = NOW() WHERE id = ?;

優點： 版本無關、業務邏輯集中、可用高精度時間 (如 NOW(6) 微秒)
缺點： 依賴應用層規範、易漏寫、分布式系統需統一時間源

方案四：單一 TIMESTAMP + 計算欄位 (5.7+ 虛擬欄位)

CREATE TABLE `example` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- 5.7+ 虛擬欄位 (不佔儲存)
    `lastUpdated` DATETIME GENERATED ALWAYS AS (`updated`) VIRTUAL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

最佳實踐建議

1. 新專案：直接使用 MySQL 8.0，原生支援多 DATETIME/TIMESTAMP 自動時間戳
2. 維護 5.5 舊系統：採用方案二 (觸發器) 或方案三 (應用層)，統一團隊規範
3. 遷移評估：mysql_upgrade 後檢查 SHOW CREATE TABLE 確認欄位定義自動展開
4. 時間精度：5.6.4+ 支援 TIMESTAMP(6)/DATETIME(6) 微秒精度，建議新表統一使用

驗證腳本

-- 檢查表中所有自動時間戳欄位
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
  AND (EXTRA LIKE '%DEFAULT_GENERATED%' OR EXTRA LIKE '%ON UPDATE%')
  AND DATA_TYPE IN ('timestamp','datetime');

1. 上一篇文章： Debian/Ubuntu 徹底卸載 MySQL/MariaDB 並清理殘留文件指南
2. 下一篇文章： MySQL GROUP BY 與 ORDER BY 執行順序深度解析：取每組最新記錄的正確姿勢

MySQL 資料庫相關知識

忘記 MySQL root 密碼？5 步快速重置找回權限

遺忘 MySQL root 密碼是常見運維場景，通過跳過權限

MySQL 入門系列：SQL 解決方案隨筆 — 子查詢改寫與缺值查找

3.8.1 子查詢改寫為聯結（MySQL 3.24 前無子查

Windows XP 下 MySQL 5.5.28 圖文安裝配置完整教程 (典型/自定義/配置向導全流程)

MySQL 5.5 on Windows XP 手把手安裝指

MySQL 資料遷移至 SQLite 工具：CYQ.DBImport 使用指南

需將 MySQL 資料匯入 SQLite (嵌入式應用、離線