核心原則

• 避免對欄位用函數：WHERE DATE(col) = ... 會導致索引失效
• 使用範圍查詢：WHERE col >= '2024-01-01' AND col < '2024-02-01' 可用索引
• 統一用 DATE/DATETIME 型別，避免字串比較

常用範圍查詢模板

需求	推薦寫法 (可用索引)	替代寫法 (函數法、不走索引)
今天	WHERE dt >= CURDATE() AND dt < CURDATE() + INTERVAL 1 DAY	WHERE TO_DAYS(dt) = TO_DAYS(NOW())
昨天	WHERE dt >= CURDATE() - INTERVAL 1 DAY AND dt < CURDATE()	WHERE TO_DAYS(NOW()) - TO_DAYS(dt) = 1
最近 7 天 (含今天)	WHERE dt >= CURDATE() - INTERVAL 6 DAY AND dt < CURDATE() + INTERVAL 1 DAY	WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(dt)
最近 30 天	WHERE dt >= CURDATE() - INTERVAL 29 DAY AND dt < CURDATE() + INTERVAL 1 DAY	WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(dt)
本月	WHERE dt >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND dt < DATE_FORMAT(CURDATE(), '%Y-%m-01') + INTERVAL 1 MONTH	WHERE DATE_FORMAT(dt, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
上月	WHERE dt >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') AND dt < DATE_FORMAT(CURDATE(), '%Y-%m-01')	WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(dt, '%Y%m')) = 1
本季度	WHERE dt >= MAKEDATE(YEAR(CURDATE()),1) + INTERVAL QUARTER(CURDATE())-1 QUARTER AND dt < ... + INTERVAL 1 QUARTER	WHERE QUARTER(dt) = QUARTER(NOW())
上季度	WHERE dt >= ... - INTERVAL 1 QUARTER AND dt < ...	WHERE QUARTER(dt) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER))
今年	WHERE dt >= MAKEDATE(YEAR(CURDATE()),1) AND dt < MAKEDATE(YEAR(CURDATE())+1,1)	WHERE YEAR(dt) = YEAR(NOW())
去年	WHERE dt >= MAKEDATE(YEAR(CURDATE())-1,1) AND dt < MAKEDATE(YEAR(CURDATE()),1)	WHERE YEAR(dt) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))
本週 (週一開始)	WHERE dt >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND dt < ... + INTERVAL 7 DAY	WHERE YEARWEEK(dt, 1) = YEARWEEK(NOW(), 1)
上週	WHERE dt >= ... - INTERVAL 7 DAY AND dt < ...	WHERE YEARWEEK(dt, 1) = YEARWEEK(NOW(), 1) - 1

UNIX 時間戳欄位查詢

-- 時間戳欄位 ts (INT)
-- 今天
WHERE ts >= UNIX_TIMESTAMP(CURDATE()) AND ts < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)

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

效能驗證

EXPLAIN SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01';
-- type=ref/range, key=idx_create_time → 索引生效

EXPLAIN SELECT * FROM orders 
WHERE DATE_FORMAT(create_time, '%Y%m') = '202401';
-- type=ALL, key=NULL → 全表掃描

存儲過程封裝範例

CREATE PROCEDURE get_orders_by_period(IN period_type VARCHAR(20))
BEGIN
    DECLARE start_dt DATETIME;
    DECLARE end_dt DATETIME;
    
    CASE period_type
        WHEN 'today' THEN
            SET start_dt = CURDATE();
            SET end_dt = CURDATE() + INTERVAL 1 DAY;
        WHEN 'this_month' THEN
            SET start_dt = DATE_FORMAT(CURDATE(), '%Y-%m-01');
            SET end_dt = start_dt + INTERVAL 1 MONTH;
        -- 其他分支...
    END CASE;
    
    SELECT * FROM orders 
    WHERE create_time >= start_dt AND create_time < end_dt;
END;