萬盛學電腦網

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

MySQL TIMESTAMP 默認當前時間與時區深度解析

核心結論

MySQL 中唯一支援「默認值為函數」的類型是 TIMESTAMP。定義列為 TIMESTAMP 等價於:

dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

插入時忽略該列,自動寫入當前時間;更新行時自動刷新為當前時間。

基本用法示例

CREATE TABLE testB (
  id INT PRIMARY KEY,
  val VARCHAR(10),
  dt TIMESTAMP
);

INSERT INTO testB(id, val) VALUES(1, 'A'), (2, 'B');
SELECT * FROM testB;
-- 结果:
-- id | val | dt
-- 1  | A   | 2024-01-15 10:30:00
-- 2  | B   | 2024-01-15 10:30:01

TIMESTAMP 關鍵特性:時區敏感

存儲與顯示均按會話時區轉換

MySQL 內部以 UTC 存儲 TIMESTAMP。寫入時將會話時區時間轉為 UTC;讀取時從 UTC 轉回會話時區。

-- 會話時區 +08:00 (北京時間)
INSERT INTO j1_dt VALUES ('1970-01-01 08:00:01');  -- 合法,UTC 為 1970-01-01 00:00:01
SET time_zone = '+00:00';
SELECT * FROM j1_dt;  -- 顯示 1970-01-01 00:00:01
SET time_zone = '+01:00';
SELECT * FROM j1_dt;  -- 顯示 1970-01-01 01:00:01

合法範圍(UTC 基準)

  • UTC:'1970-01-01 00:00:01' ~ '2038-01-19 03:14:07'
  • 東八區(+08:00):'1970-01-01 08:00:01' ~ '2038-01-19 11:14:07'

超出範圍的值會被截斷為 '0000-00-00 00:00:00' 並產生 Warning(非 Error,應用層 try-catch 捕獲不到)。

實測邊界值驗證

CREATE TABLE `test` (
  `ID` INT NOT NULL DEFAULT 0,
  `NAME` VARCHAR(20),
  `hiredate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- 東八區會話
INSERT INTO test VALUES(1, 'zjadolf', '1970-01-01 08:01:00'); -- 成功
INSERT INTO test VALUES(2, 'zjadolf', '1970-01-01 08:00:00'); -- Error 1264: Out of range
INSERT INTO test VALUES(3, 'zjadolf', '1970-01-01 08:00:30'); -- 成功
INSERT INTO test VALUES(4, 'zjadolf', '1970-01-01 08:00:01'); -- 成功(最小合法值)
INSERT INTO test VALUES(5, 'zjadolf', '1969-01-01 08:01:02'); -- Error

TIMESTAMP vs DATETIME 選型指南

特性TIMESTAMPDATETIME
範圍1970~2038 (UTC)1000~9999
存儲大小4 字節8 字節 (5.6.4+ 5/8 字節)
時區轉換自動 (UTC ⇄ Session)無,原樣存儲
默認值支援CURRENT_TIMESTAMP5.6.5+ 支援 DEFAULT CURRENT_TIMESTAMP
自動更新ON UPDATE CURRENT_TIMESTAMP5.6.5+ 支援
適用場景記錄創建/修改時間、跨時區應用歷史數據、出生日期、無時區轉換需求

最佳實踐建議

  1. 創建時間/更新時間欄位: 優先用 TIMESTAMP,自動維護,無需應用層填充。
  2. 跨時區應用: 統一資料庫會話時區為 UTC(SET time_zone='+00:00'),應用層處理展示層轉換,避免時區混亂。
  3. 超出 2038 年需求: 必須改用 DATETIME 或 BIGINT 存儲毫秒時間戳。
  4. 避免隱式轉換陷阱: 批量導入歷史數據時,確認會話時區與數據時區一致,否則會發生雙重轉換導致時間偏移。
  5. 關注 Warning: 導入/寫入後執行 SHOW WARNINGS; 捕獲 Out of range value,防止靜默數據損壞。

總結

TIMESTAMP 是 MySQL 唯一原生支援「默認當前時間 + 自動更新」的類型,但其 4 字節存儲決定了 1970~2038 年的硬性範圍,且強綁定時區轉換。理解「存儲 UTC、顯示 Session TZ」機制,配合統一 UTC 會話時區策略,能避開 90% 的時間類型坑。長遠業務請改用 DATETIME 或毫秒級 BIGINT。

copyright © 萬盛學電腦網 all rights reserved