日期比較是業務查詢高頻場景,寫法不當極易導致**索引失效、全表掃描**。本文系統講解 MySQL 日期比較函數、範圍查詢標準寫法、索引友好模式、常見反模式與修正方案。
核心日期比較函數
| 函數 | 用途 | 示例 |
DATEDIFF(date1, date2) | 天數差 (date1 - date2) | DATEDIFF('2023-08-08', '2023-08-01') = 7 |
TIMESTAMPDIFF(unit, dt1, dt2) | 指定單位差值 | TIMESTAMPDIFF(DAY, '2023-01-01', '2023-08-08') = 219 |
DATE_ADD(date, INTERVAL n UNIT) | 日期加法 | DATE_ADD('2023-08-01', INTERVAL 7 DAY) = '2023-08-08' |
DATE_SUB(date, INTERVAL n UNIT) | 日期減法 | DATE_SUB('2023-08-08', INTERVAL 7 DAY) = '2023-08-01' |
DATE(expr) | 提取日期部分 | DATE('2023-08-08 14:30:00') = '2023-08-08' |
YEAR/MONTH/DAY/HOUR/MINUTE/SECOND | 提取單一組件 | MONTH('2023-08-08') = 8 |
DAYOFWEEK/WEEKDAY | 星期索引 | DAYOFWEEK('2023-08-08') = 3 (1=週日) |
QUARTER | 季度 | QUARTER('2023-08-08') = 3 |
DAYOFYEAR | 年間第幾天 | DAYOFYEAR('2023-08-08') = 220 |
❌ 索引失效反模式(嚴禁)
| 反模式 | 原因 | 影響 |
WHERE DATE(create_time) = '2023-08-08' | 函數包裹欄位,無法走索引 | 全表掃描 |
WHERE YEAR(create_time) = 2023 AND MONTH(create_time) = 8 | 函數包裹欄位 | 全表掃描 |
WHERE create_time BETWEEN '2023-08-01' AND '2023-08-31 23:59:59' | DATETIME 精度漏掉毫秒 | 資料遺漏 |
WHERE create_time LIKE '2023-08%' | 隱式轉型為字串比較 | 全表掃描 + 錯誤結果 |
✅ 索引友好標準寫法:半開半閉區間 [start, end)
-- 某一天
WHERE create_time >= '2023-08-08 00:00:00'
AND create_time < '2023-08-09 00:00:00'
-- 近 30 天(含今天)
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
-- 本月
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND create_time < DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')
-- 本週(週一開始)
WHERE create_time >= DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY)
AND create_time < DATE_ADD(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 7 DAY)
-- 本季度
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01') - INTERVAL (MONTH(NOW())-1)%3 MONTH
AND create_time < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01') - INTERVAL (MONTH(NOW())-1)%3 MONTH, INTERVAL 3 MONTH)
動態參數化查詢(應用層構建)
-- PHP PDO 範例
$start = (new DateTime('2023-08-01'))->format('Y-m-d H:i:s');
$end = (new DateTime('2023-09-01'))->format('Y-m-d H:i:s');
$sql = "SELECT * FROM orders WHERE create_time >= ? AND create_time < ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$start, $end]);
特殊場景處理
1. 只存 DATE 類型(無時間部分)
-- 可直接用等值或 BETWEEN(含邊界)
WHERE order_date = '2023-08-08'
WHERE order_date BETWEEN '2023-08-01' AND '2023-08-31'
2. 跨時區查詢
-- 統一轉換到 UTC 比較
WHERE CONVERT_TZ(create_time, '+08:00', '+00:00') >= '2023-08-08 00:00:00'
AND CONVERT_TZ(create_time, '+08:00', '+00:00') < '2023-08-09 00:00:00'
-- 或應用層統一存儲 UTC,查詢時轉換參數
3. 歷史分區表按日期分區
CREATE TABLE orders (
id BIGINT,
create_time DATETIME,
...
) PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
...
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 查詢自動分區裁剪
SELECT * FROM orders WHERE create_time >= '2023-08-01' AND create_time < '2023-09-01';
效能驗證清單
EXPLAIN 確認 type=range/ref、key=目標索引、rows 合理。
Handler_read_next / Handler_read_key 狀態變數觀察掃描行數。
- 慢查詢日誌記錄執行時間、掃描行數、鎖等待。