MySQL datetime 查詢異常「Value '0000-00-00' can not be represented as java.sql.Timestamp」解決方案

異常現象

java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

根因分析

• 表結構：dt_col DATETIME NOT NULL (無默認值)
• 插入數據時未指定該列，MySQL 默認填充 '0000-00-00 00:00:00' (零日期)
• JDBC 驅動預設 zeroDateTimeBehavior=exception，遇到零日期拋出異常
• Java java.sql.Timestamp 最小值為 0001-01-01 00:00:00，無法映射零日期

JDBC 連接參數解決方案

方案一：zeroDateTimeBehavior=convertToNull (推薦)

jdbc:mysql://host:3306/db?zeroDateTimeBehavior=convertToNull&characterEncoding=utf8

• 零日期自動轉為 null
• Java 端接收 null，需做非空判斷
• 最符合「無數據即 null」語義

方案二：zeroDateTimeBehavior=round

jdbc:mysql://host:3306/db?zeroDateTimeBehavior=round&characterEncoding=utf8

• 零日期轉為 0001-01-01 00:00:00
• 避免 null，但語義偽造了不存在的日期

方案三：noDatetimeStringSync=true (不推薦)

jdbc:mysql://host:3306/db?noDatetimeStringSync=true&characterEncoding=utf8

• 返回原始字符串 '0000-00-00 00:00:00'
• Java 端需自行解析，極易出錯

數據層面根治方案 (推薦)

1. 修正表結構：允許 NULL + 顯式默認值

ALTER TABLE tbl_name 
MODIFY dt_col DATETIME NULL DEFAULT NULL;
-- 或
ALTER TABLE tbl_name 
MODIFY dt_col DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

2. 應用層插入時顯式賦值

INSERT INTO tbl_name (dt_col, ...) VALUES (NOW(), ...);
-- 或
INSERT INTO tbl_name (dt_col, ...) VALUES ('2024-01-15 14:30:00', ...);

3. 清理現有零日期數據

UPDATE tbl_name SET dt_col = NULL WHERE dt_col = '0000-00-00 00:00:00';
-- 或更新為業務合理默認值
UPDATE tbl_name SET dt_col = CURRENT_TIMESTAMP WHERE dt_col = '0000-00-00 00:00:00';

SQL Mode 影響 (MySQL 5.7+)

-- 檢查當前 sql_mode
SELECT @@sql_mode;

-- 若包含 NO_ZERO_DATE / NO_ZERO_IN_DATE
-- 插入零日期會直接報錯而非警告
-- 建議生產環境開啟嚴格模式
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

驅動版本對應表

總結

零日期異常本質是數據不合法與驅動嚴格映射的衝突。治理優先級：

1. 數據層面：修正表結構允許 NULL、清理歷史零日期、應用層顯式賦值
2. 驅動層面：連接串添加 zeroDateTimeBehavior=convertToNull 作為安全網
3. SQL Mode：開啟 NO_ZERO_DATE 從源頭阻擋零日期寫入