MySQL 日期與時間函數完整指南：從基礎操作到進階應用

前言：為何日期時間處理至關重要？

在資料庫開發中，日期與時間函數是不可或缺的工具。無論是追蹤資料更新時間、計算使用者年齡、統計每日流量，還是實現自動時間戳記，MySQL 豐富的日期時間函數都能輕鬆應對。本文將系統整理 MySQL 的日期時間類型、常用函數、查詢技巧以及 TIMESTAMP 自動更新機制，助你高效處理時間相關業務。

一、MySQL 日期時間資料類型總覽

MySQL 提供五種核心日期時間類型，各有適用場景：

• DATETIME：日期與時間組合（YYYY-MM-DD HH:MM:SS），範圍 1000-01-01 至 9999-12-31，佔 8 位元組，不受時區影響。
• TIMESTAMP：時間戳（1970-01-01 至 2038-01-19），佔 4 位元組，會隨時區自動轉換，適合記錄建立/修改時間。
• DATE：僅日期（YYYY-MM-DD），佔 3 位元組。
• TIME：僅時間（HH:MM:SS），佔 3 位元組。
• YEAR：年份（1901/2155），佔 1 位元組。

特別提醒：MySQL 允許存儲「不嚴格合法」的日期（如 1999-11-31 或 1999-00-00），僅檢查月份 0-12、日期 0-31。應用層需自行嚴格驗證；若用於 DATE_ADD() 等運算，結果可能不準確。

二、獲取當前日期時間的三大核心函數

1. CURDATE() / CURRENT_DATE — 僅取日期

SELECT CURDATE();        -- 2024-05-20
SELECT CURDATE() + 0;    -- 20240520 (數值語境)

2. CURTIME() — 僅取時間

SELECT CURTIME();        -- 14:30:45
SELECT CURTIME() + 0;    -- 143045 (數值語境)

3. NOW() / SYSDATE() — 日期時間合一

SELECT NOW();            -- 2024-05-20 14:30:45
SELECT NOW() + 0;        -- 20240520143045 (數值語境)

差異細節：NOW() 在語句開始執行時固定值；SYSDATE() 實時取值，主從複制場景下行為不同。

三、實戰範例：計算寵物年齡（天數）

SELECT name, CURDATE() - birth AS age_days FROM pet;
-- 結果為數值格式天數差，如 80016

若需精確年齡（年），建議用 TIMESTAMPDIFF(DAY, birth, CURDATE()) 或 DATEDIFF()。

四、TIMESTAMP 自動更新機制詳解

這是實現「建立時間」、「修改時間」自動維護的關鍵特性：

• 表中第一個 TIMESTAMP 欄位預設具備自動更新行為。
• 觸發條件：INSERT 未指定值、UPDATE 修改了其他欄位、顯式設為 NULL。
• 若需「建立後不再變更」，可在 UPDATE 時顯式設為原值：UPDATE t SET col=val, ts=ts WHERE ...；或改用 DATETIME 手動維護。
• 存儲優勢：TIMESTAMP 4 位元組 vs DATETIME 8 位元組。

五、日期範圍查詢：避開常見陷阱

錯誤寫法：WHERE entrydate = '2024-05-20' —— 只會匹配 00:00:00 那一筆。

正確寫法三種：

1. 範圍比較（推薦，可用索引）：
   WHERE entrydate >= '2024-05-20' AND entrydate < '2024-05-21'
2. LIKE 模糊匹配（字串比較，需標準格式）：
   WHERE entrydate LIKE '2024-05-20%'
3. DATE() 函數提取日期部份：
   WHERE DATE(entrydate) = '2024-05-20' （注意：可能導致全表掃描）

格式容錯差異：比較運算符支援多種分隔符（2024-05-20、2024/05/20、20240520 均可）；LIKE 嚴格要求 YYYY-MM-DD HH:MM:SS 格式。

六、日期比較與運算進階

TO_DAYS() — 轉換為「第 0 年起的天數」

SELECT TO_DAYS('2024-05-20');           -- 738650
SELECT TO_DAYS(NOW()) - TO_DAYS('2024-01-01'); -- 2024 年已過天數

其他常用函數速查

七、最佳實踐總結

1. 優先用 DATETIME 存儲業務時間（不受 2038 年限制、時區固定）；用 TIMESTAMP 存儲系統級建立/修改時間（自動維護、省空間）。
2. 範圍查詢用 >= 起始 AND < 結束+1天，配合索引效能最佳。
3. 應用層嚴格驗證日期合法性，勿依賴資料庫鬆散檢查。
4. 跨時區應用統一存儲 UTC，展示層按使用者時區轉換。