萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 5.5 vs 5.6 TIMESTAMP 默認值行為大不同:DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 避坑指南

MySQL 5.5 與 5.6 對 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 的處理存在關鍵差異:5.5 不自動回填既有資料,5.6 自動回填。本文實測對比、原理解析、手動指定值行為、多字節字元長度判斷補充,助你跨版本遷移零驚喜。

實測對比

MySQL 5.6 (5.6.13-log)

ALTER TABLE s__uk ADD last_op_time TIMESTAMP NOT NULL 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-- Query OK, 0 rows affected

SELECT id, last_op_time FROM s__uk LIMIT 1;
-- 1 | 2013-10-10 10:17:57  ← 既有資料自動回填當前時間

MySQL 5.5 (5.5.15-log)

ALTER TABLE s__uk ADD last_op_time TIMESTAMP NOT NULL 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-- Query OK, 2523 rows affected  ← 顯示受影響行數!

SELECT last_op_time FROM s__uk LIMIT 1;
-- 0000-00-00 00:00:00  ← 既有資料變成零時間,非預期!

核心差異總結

手動指定值行為 (兩版本一致)

UPDATE s__uk SET last_op_time = '2012-12-12 00:00:00' WHERE id = 1;
SELECT id, last_op_time FROM s__uk LIMIT 1;
-- 1 | 2012-12-12 00:00:00  ← 手動指定值生效,不被 ON UPDATE 覆蓋

結論:顯式賦值優先級最高,ON UPDATE 僅在「未顯式指定該欄位」時觸發。

遷移最佳實踐 (5.5 → 5.6/5.7/8.0)

  1. 評估既有資料:檢查是否含 0000-00-00 無效時間
  2. 修正既有資料:
    UPDATE t SET ts_col = NULL WHERE ts_col = '0000-00-00 00:00:00';
    -- 或回填建立時間
    UPDATE t SET ts_col = created_at WHERE ts_col = '0000-00-00 00:00:00';
  3. 調整欄位定義:建議改為 DATETIME + 應用層維護,或 TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP 避免零時間
  4. 測試驗證:插入、更新、讀取全流程驗證

補充:判斷欄位是否含多字節字元 (中文)

-- CHARACTER_LENGTH = 字元數,LENGTH = 位元組數
SELECT 
  col,
  CHARACTER_LENGTH(col) AS char_len,
  LENGTH(col) AS byte_len,
  CASE WHEN CHARACTER_LENGTH(col) = LENGTH(col) THEN '純ASCII' ELSE '含多字節' END AS type
FROM t;
  • 相等 → 純 ASCII (英文、數字、符號)
  • 不等 → 含中文/日文/Emoji 等多字節字元

MongoDB 小贈士

-- find() 結果美化輸出
db.collection.find().pretty()
MySQL 5.5 加 TIMESTAMP 欄位會「偷偷」把既有資料改成零時間,5.6+ 修正此行為。跨版本遷移務必檢查既有 0000-00-00 資料並修正,否則應用層解析會拋異常 (如 Java java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Timestamp)。
copyright © 萬盛學電腦網 all rights reserved
行為MySQL 5.5MySQL 5.6+
ALTER TABLE 加欄位掃描全表、回填零時間、耗時久、產生大量 Binlog僅修改元資料、既有行保持 NULL (若允許) 或隱性預設值、秒級完成
既有行預設值0000-00-00 00:00:00 (無效時間)保持 NULL 或隱性 CURRENT_TIMESTAMP
新增行行為同 5.6,自動填入當前時間自動填入當前時間
UPDATE 觸發自動更新為當前時間自動更新為當前時間