萬盛學電腦網

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

MySQL 設置欄位默認值為當前時間:TIMESTAMP 與 DATETIME 詳解

MySQL 自動填充創建/更新時間的正確姿勢

MySQL 不支援 `DEFAULT NOW()` 等函數作為預設值 (8.0.13 前)。正確做法是使用 `TIMESTAMP` 或 `DATETIME(6)` 配合 `DEFAULT CURRENT_TIMESTAMP`。

核心差異對比

特性TIMESTAMPDATETIME (5.6.5+)
儲存範圍1970-2038 (Year 2038 問題)1000-9999
儲存大小4 bytes5 bytes (無小數秒) / 8 bytes (有小數秒)
時區轉換存儲為 UTC,讀取轉換不轉換,原樣存儲
自動初始化支援 (首個欄位)支援 (任意欄位)
自動更新支援 ON UPDATE支援 ON UPDATE

三種創建方式

方法一:ALTER TABLE 修改現有表

USE test_db1;
CREATE TABLE test_ta1 (
    id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    createtime DATETIME,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- 修改為 TIMESTAMP 並設定默認值
ALTER TABLE test_ta1 
CHANGE createtime createtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

方法二:建表時直接指定 (推薦)

USE test_db1;
CREATE TABLE test_ta1 (
    id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    createtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

方法三:視覺化工具 (MySQL-Front/Workbench)

  1. 右鍵欄位 → 屬性
  2. Type 選 TIMESTAMP
  3. Default 選 <INSERT-TimeStamp> 或 CURRENT_TIMESTAMP

進階:同時記錄創建與更新時間

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

行為:

  • created_at:INSERT 時自動填入,之後不變
  • updated_at:INSERT/UPDATE 時自動填入最新時間

MySQL 8.0.13+ 新特性:DATETIME 也支援函數默認值

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

優勢:無 2038 年問題、無時區轉換、支援微秒精度 DATETIME(6)。

常見陷阱與注意事項

⚠️ 關鍵限制

  • 單表僅一個 TIMESTAMP 可自動初始化/更新 (5.6.5 前),需手動指定哪個欄位享有此特權
  • UPDATE 同值不觸發:`UPDATE t SET ts = ts` 不會更新 TIMESTAMP
  • 時區陷阱:TIMESTAMP 存儲 UTC,跨時區應用需注意
  • NULL 值:`DEFAULT CURRENT_TIMESTAMP` 隱含 `NOT NULL`,插入 NULL 會存當前時間而非 NULL

顯式插入 NULL 的正確做法

-- 若需允許 NULL,需顯式聲明
CREATE TABLE t (
    ts TIMESTAMP NULL DEFAULT NULL
);

-- 插入 NULL 而非當前時間
INSERT INTO t (ts) VALUES (NULL);

總結建議

  • MySQL 8.0.13+:優先用 DATETIME(6) DEFAULT CURRENT_TIMESTAMP
  • 舊版本:用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  • 需記錄更新時間:加 ON UPDATE CURRENT_TIMESTAMP
  • 跨時區應用:傾向 DATETIME 存儲 UTC,應用層處理顯示

正確選擇時間類型與默認值策略,避免 2038 年問題與時區困擾。

copyright © 萬盛學電腦網 all rights reserved