MySQL TIMESTAMP 類型與時區深度解析：存儲機制、顯示轉換與避坑指南

核心特性總覽

時區轉換機制實戰演示

CREATE TABLE tz_test (dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

-- 會話時區設為東八區（北京時間）
SET time_zone = '+08:00';
INSERT INTO tz_test VALUES ('1970-01-01 08:00:01');  -- 東八區時間
SELECT * FROM tz_test;  -- 顯示 1970-01-01 08:00:01

-- 切換會話時區為 UTC
SET time_zone = '+00:00';
SELECT * FROM tz_test;  -- 顯示 1970-01-01 00:00:01 (UTC)

-- 切換為東一區
SET time_zone = '+01:00';
SELECT * FROM tz_test;  -- 顯示 1970-01-01 01:00:01

關鍵結論：同一條記錄，不同會話時區下 SELECT 看到的時間不同，但底層存儲的 UTC 時間戳不變。

超出範圍行為

SET time_zone = '+08:00';
INSERT INTO tz_test VALUES ('1970-01-01 00:00:01');  -- 東八區時間早於 1970-01-01 08:00:01
-- Query OK, 1 row affected, 1 warning (0.00 sec)

SHOW WARNINGS;
-- Level: Warning, Code: 1264, Message: Out of range value adjusted for column 'dt' at row 1

SELECT * FROM tz_test;
-- 實際存儲為 0000-00-00 00:00:00

風險提示：僅產生 Warning 不報錯，應用層 try-catch 捕獲不到，數據靜默截斷極其危險。

最佳實踐與避坑指南

1. 明確時區配置

-- my.cnf 全局設置
[mysqld]
default-time-zone = '+08:00'  # 或系統時區

-- 連接字串指定（JDBC 例）
jdbc:mysql://host:3306/db?serverTimezone=Asia/Shanghai

2. 選擇正確類型

• TIMESTAMP：需要時區轉換的業務（如用戶操作時間、全球化應用）
• DATETIME：固定時區業務、歷史數據、超出 2038 年、需精確保存用戶輸入的本地時間

3. 應用層統一處理

// Java 8+ 最佳實踐
// 資料庫存 TIMESTAMP (UTC)
// 應用層用 Instant/ZonedDateTime
Instant instant = Instant.now();  // UTC
ZonedDateTime beijing = instant.atZone(ZoneId.of("Asia/Shanghai"));

// 寫入 DB：PreparedStatement.setTimestamp(1, Timestamp.from(instant))
// 讀出 DB：ResultSet.getTimestamp(1).toInstant()

4. 避免隱式轉換陷阱

-- 危險：字符串隱式轉換依賴會話時區
INSERT INTO tz_test VALUES ('2024-01-01 12:00:00');

-- 安全：顯式指定時區或使用 UTC 時間戳
INSERT INTO tz_test VALUES (CONVERT_TZ('2024-01-01 12:00:00', '+08:00', '+00:00'));
-- 或應用層傳入 UTC 時間

5. 修復已損壞數據

-- 批量修正被截斷為 0000-00-00 的記錄
-- 假設業務邏輯知曉原始時區為 +08:00
UPDATE tz_test 
SET dt = CONVERT_TZ('1970-01-01 08:00:01', '+08:00', '+00:00')
WHERE dt = '0000-00-00 00:00:00';

版本差異提示

• MySQL 5.6.4+：TIMESTAMP(n) 支援微秒精度 (n=0~6)
• MySQL 8.0：不再支援 TIMESTAMP(0) 顯示寬度語法，僅保留精度語義
• 8.0.19+：TIMESTAMP WITH TIME ZONE 別名支援（標準 SQL 兼容）