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 ← 既有資料變成零時間,非預期!
核心差異總結
| 行為 | MySQL 5.5 | MySQL 5.6+ |
| ALTER TABLE 加欄位 | 掃描全表、回填零時間、耗時久、產生大量 Binlog | 僅修改元資料、既有行保持 NULL (若允許) 或隱性預設值、秒級完成 |
| 既有行預設值 | 0000-00-00 00:00:00 (無效時間) | 保持 NULL 或隱性 CURRENT_TIMESTAMP |
| 新增行行為 | 同 5.6,自動填入當前時間 | 自動填入當前時間 |
| UPDATE 觸發 | 自動更新為當前時間 | 自動更新為當前時間 |
手動指定值行為 (兩版本一致)
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)
- 評估既有資料:檢查是否含
0000-00-00 無效時間
- 修正既有資料:
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';
- 調整欄位定義:建議改為
DATETIME + 應用層維護,或 TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP 避免零時間
- 測試驗證:插入、更新、讀取全流程驗證
補充:判斷欄位是否含多字節字元 (中文)
-- 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