MySQL 日期差值查詢實戰：TIMESTAMPDIFF 與直接比較兩種高效寫法

MySQL 日期差值查詢實戰

業務中常需「查找距離目前 N 天/月/年的資料」，例如「找出 30 年內的員工入職記錄」。MySQL 提供 TIMESTAMPDIFF() 函數與直接日期比較兩種主流寫法，本文透過實測對比其用法與適用場景。

場景設定

假設資料表 testtable 包含 date 欄位 (型別為 DATE/DATETIME 或 VARCHAR)，需查詢 date 與目前時間差 小於 30 年 的記錄。

寫法一：TIMESTAMPDIFF() 函數 (推薦、語義清晰)

SELECT * FROM testtable 
WHERE TIMESTAMPDIFF(YEAR, `date`, NOW()) < 30;

• 優點： 明確指定比較單位 (YEAR/MONTH/DAY/HOUR...)，可讀性強；自動處理 VARCHAR 型別日期字串 (隱式轉換)，兼容性極佳。
• 注意： 若 date 為未來時間，結果為負數，仍滿足 < 30，需視業務決定是否需加 AND `date` <= NOW()。

寫法二：直接日期字面值比較 (效能最優、利用索引)

SELECT * FROM testtable 
WHERE `date` > '1994-01-01';  -- 假設今年 2024 年，30 年前為 1994

• 優點： 可觸發索引範圍掃描 (Range Scan)，大表查詢效能遠優於函數寫法 (函數會導致索引失效)。
• 缺點： 需應用層預先計算閾值日期；要求 date 欄位為標準日期型別 (DATE/DATETIME/TIMESTAMP)，VARCHAR 存儲非標準格式可能比較錯誤。

效能對比總結

最佳實踐建議

1. 大表高頻查詢： 應用層計算邊界日期，使用 WHERE date_col > 'YYYY-MM-DD' 並確保欄位有索引。
2. 臨時分析/兼容髒資料： 直接用 TIMESTAMPDIFF()，省去清洗成本。
3. 索引優化： 對於 TIMESTAMPDIFF 場景，可建立函數索引 (MySQL 8.0+)：ALTER TABLE testtable ADD INDEX idx_year_diff ((TIMESTAMPDIFF(YEAR, `date`, NOW()))); (需注意 NOW() 非確定性函數，實際建議建立虛擬欄位 + 索引)。

補充： 若需「恰好 30 年前」或「30 年前含當天」，請精確調整比較運算子 (<=, >=) 並考慮時區影響。