MySQL DATE 時間類型完全指南：格式、比較規則、寬鬆解析與 SQL 模式控制

標準格式與插入寬鬆解析

• 標準格式：'YYYY-MM-DD' (ISO 8601)
• MySQL 接受多種「寬鬆」格式自動轉換：

INSERT INTO t (dt) VALUES
('2024-01-15'),      -- 標準
('2024/01/15'),      -- 斜線分隔
('2024.01.15'),      -- 點分隔
('2024 01 15'),      -- 空格分隔
('20240115'),        -- 無分隔符
(20240115);          -- 數值字面量

所有以上寫入結果相同：2024-01-15。

比較運算的隱式轉換規則

特殊值處理

• '0000-00-00'：「零日期」，可儲存/讀取，NO_ZERO_DATE 模式下禁止。
• MyODBC 2.50.12+ 自動將零日期轉為 NULL。
• 無法轉換的無效日期 → 儲存為 0，讀取為 '0000-00-00'。

SQL 模式控制嚴格度

-- 嚴格模式：拒絕零日期、零月/日
SET sql_mode = 'NO_ZERO_DATE,NO_ZERO_IN_DATE';

-- 寬鬆模式（預設）：允許 '2024-00-00'、'0000-00-00'
SET sql_mode = '';

Web 應用常用寬鬆模式接收前端分離的 年/月/日，避免前端驗證漏網。

常見函數與運算

-- 日期運算
SELECT DATE_ADD('2024-01-15', INTERVAL 10 DAY);
SELECT DATEDIFF('2024-12-31', '2024-01-01');

-- 格式化輸出
SELECT DATE_FORMAT(dt, '%Y年%m月%d日') FROM t;

-- 截取部分
SELECT YEAR(dt), MONTH(dt), DAY(dt) FROM t;

避坑清單

1. 字串比較陷阱：WHERE dt = '2024-1-1' (單位數月/日) 會轉字串比較，索引失效 → 統一補零 '2024-01-01'。
2. 時區影響：TIMESTAMP 儲存為 UTC，讀取轉換為 Session 時區；DATETIME 不轉換。跨時區應用優先用 TIMESTAMP。
3. 範圍查詢包含邊界：WHERE dt >= '2024-01-01' AND dt < '2024-02-01' 比 BETWEEN 更安全（避免時間元件導致漏查）。