MySQL 相對時間範圍查詢 SQL 模板庫

業務中高頻需求：查詢「今天/昨天/最近 N 天/本月/上月」的數據。本文提供標準、高效、可索引的寫法模板。

核心原則：避免對欄位使用函數

WHERE DATE(create_time) = CURDATE()
WHERE YEAR(create_time) = 2023 AND MONTH(create_time) = 5
WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) <= 7

WHERE create_time >= '2023-05-01' AND create_time < '2023-06-01'
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)

標準模板庫 (假設時間欄位為 created_at DATETIME)

1. 今天

-- 最佳：範圍查詢，用上索引
WHERE created_at >= CURDATE() 
  AND created_at < CURDATE() + INTERVAL 1 DAY

-- 兼容寫法 (5.6+)
WHERE created_at >= DATE(CURDATE())
  AND created_at < DATE(CURDATE() + INTERVAL 1 DAY)

2. 昨天

WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND created_at < CURDATE()

3. 最近 7 天 (含今天)

WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  AND created_at < CURDATE() + INTERVAL 1 DAY

4. 最近 30 天

WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND created_at < CURDATE() + INTERVAL 1 DAY

5. 本月

WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND created_at < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01')

6. 上個月

WHERE created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01')

7. 本週 (週一為週首)

WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
  AND created_at < DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) + INTERVAL 7 DAY

8. 上週

WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) - INTERVAL 7 DAY
  AND created_at < DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)

9. 今年

WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-01-01')
  AND created_at < DATE_FORMAT(CURDATE() + INTERVAL 1 YEAR, '%Y-01-01')

10. 去年

WHERE created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), '%Y-01-01')
  AND created_at < DATE_FORMAT(CURDATE(), '%Y-01-01')

進階：動態參數化查詢 (應用層組裝)

-- 應用層計算好開始/結束時間，傳參數
-- 避免 SQL 內部計算，利於查詢緩存與計劃復用

-- Java 範例
LocalDateTime start = LocalDate.now().atStartOfDay();
LocalDateTime end = start.plusDays(1);
String sql = "SELECT * FROM orders WHERE created_at >= ? AND created_at < ?";

特殊場景處理

TIMESTAMP 欄位帶時區

-- 確保會話時區一致
SET time_zone = '+08:00';
-- 或連線參數指定
?serverTimezone=Asia/Shanghai

分區表按時間分區裁剪

-- 確保 WHERE 條件包含分區鍵，實現 Partition Pruning
EXPLAIN PARTITIONS SELECT * FROM logs 
WHERE created_at >= '2023-05-01' AND created_at < '2023-06-01';

大表歷史歸檔查詢

-- 僅查熱數據分區
SELECT * FROM orders_202305 WHERE created_at >= '2023-05-01';

函數速查表

效能驗證

EXPLAIN SELECT * FROM orders 
WHERE created_at >= '2023-05-01' AND created_at < '2023-06-01';
-- type=range, key=idx_created_at, rows=預估掃描行數 (非全表)

收藏此模板庫，時間範圍查詢不再手寫、不再踩索引失效坑。