MySQL TIMESTAMP 與 DATETIME 深度解析：儲存機制、時區行為與自動初始化

MySQL TIMESTAMP 與 DATETIME 深度解析

一、當前時間的多種表示方式

MySQL 提供多個函數獲取當前時間戳：

• CURRENT_TIMESTAMP / CURRENT_TIMESTAMP()
• NOW()
• LOCALTIME / LOCALTIME()
• LOCALTIMESTAMP / LOCALTIMESTAMP()

以上函數在同一條 SQL 內多次調用返回值相同（語句級一致性）。

二、DATE / TIME / DATETIME / TIMESTAMP 類型對比

三、核心差異實測：時區轉換行為

建表與插入

CREATE TABLE test_ts (id INT, hiredate TIMESTAMP);
CREATE TABLE test_dt (id INT, hiredate DATETIME);
INSERT INTO test_ts VALUES (1, '20151208000000');
INSERT INTO test_dt VALUES (1, '20151208000000');

修改會話時區

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

mysql> SET time_zone = '+00:00';  -- UTC

mysql> SELECT * FROM test_ts;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
| 1    | 2015-12-07 16:00:00 |  -- 提前 8 小時（CST=UTC+8）
+------+---------------------+

mysql> SELECT * FROM test_dt;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
| 1    | 2015-12-08 00:00:00 |  -- 不變
+------+---------------------+

結論： TIMESTAMP 寫入時將會話時區時間轉為 UTC 儲存；讀取時再轉回目標會話時區。DATETIME 僅原樣存取。

四、自動初始化與更新機制

預設行為（explicit_defaults_for_timestamp = OFF）

CREATE TABLE test (id INT, hiredate TIMESTAMP);
INSERT INTO test (id) VALUES (1);  -- 未給 hiredate 值
SELECT * FROM test;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
| 1    | 2015-12-08 14:34:46 |  -- 自動填入當前時間

SHOW CREATE TABLE test\G
-- 實際 DDL 含隱式屬性：
-- `hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

規則：首個 TIMESTAMP 欄位 若未顯式指定 DEFAULT/ON UPDATE，自動獲得：

• DEFAULT CURRENT_TIMESTAMP（自動初始化）
• ON UPDATE CURRENT_TIMESTAMP（自動更新）

如何禁用/自訂

方法一：全域參數 SET GLOBAL explicit_defaults_for_timestamp = ON;（5.6.6+）

方法二（參數保持 OFF 時）：

• 顯式指定 DEFAULT 值：hiredate TIMESTAMP DEFAULT '2000-01-01'
• 允許 NULL：hiredate TIMESTAMP NULL → 預設 NULL，無自動屬性
• 指定 DEFAULT 0：hiredate TIMESTAMP DEFAULT 0 → 零值

CREATE TABLE test1 (id INT, hiredate TIMESTAMP NULL);
SHOW CREATE TABLE test1\G
-- `hiredate` timestamp NULL DEFAULT NULL

CREATE TABLE test2 (id INT, hiredate TIMESTAMP DEFAULT 0);
SHOW CREATE TABLE test2\G
-- `hiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

五、版本演進

• 5.6.5 前：僅 TIMESTAMP 支援自動初始化/更新，且每表限一個。
• 5.6.5+：TIMESTAMP 與 DATETIME 均支援，且不限制欄位數量。

六、選型建議

七、常見坑點

1. 2038 年問題：TIMESTAMP 上限 2038-01-19，長期系統必須用 DATETIME 或 BIGINT 存 Unix 毫秒。
2. 時區不一致導致資料錯亂：應用、資料庫、作業系統時區必須統一（建議全鏈路 UTC）。
3. SYSDATE() 非語句級一致：主從複制不安全，啟用 --sysdate-is-now 或改用 NOW()。
4. 零值陷阱：DEFAULT 0 存入 '0000-00-00 00:00:00'，部分驅動程式無法解析，建議用 NULL 或明確預設值。