萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 按日期範圍查詢 SQL 大全:今天/昨天/近N天/本月/上月/本季/本年/本周

核心函數速查

函數說明
NOW()當前日期時間
CURDATE()當前日期
DATE_SUB(date, INTERVAL n UNIT)日期減法
DATE_FORMAT(date, '%Y-%m')格式化輸出
TO_DAYS(date)日期轉天數(從年0開始)
YEARWEEK(date)年週數
QUARTER(date)季度(1-4)
PERIOD_DIFF(p1,p2)月份差(YYYYMM格式)

常用範圍查詢模板(假設時間欄位為 create_time)

今天


SELECT * FROM t WHERE DATE(create_time) = CURDATE();
-- 或
SELECT * FROM t WHERE TO_DAYS(create_time) = TO_DAYS(NOW());

昨天


SELECT * FROM t WHERE DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
-- 或
SELECT * FROM t WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) = 1;

近 7 天(含今天)


SELECT * FROM t WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

近 30 天


SELECT * FROM t WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

本月


SELECT * FROM t WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

上月


SELECT * FROM t WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%Y%m'), DATE_FORMAT(create_time, '%Y%m')) = 1;
-- 或
SELECT * FROM t WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m');

本季度


SELECT * FROM t WHERE QUARTER(create_time) = QUARTER(NOW());

上季度


SELECT * FROM t WHERE QUARTER(create_time) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER));

本年


SELECT * FROM t WHERE YEAR(create_time) = YEAR(NOW());

上年


SELECT * FROM t WHERE YEAR(create_time) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR));

本周(以週一為起始,YEARWEEK 模式 3)


SELECT * FROM t WHERE YEARWEEK(create_time, 3) = YEARWEEK(NOW(), 3);

上周


SELECT * FROM t WHERE YEARWEEK(create_time, 3) = YEARWEEK(NOW(), 3) - 1;

任意固定區間(如最近 6 個月)


SELECT * FROM t WHERE create_time BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

性能關鍵:索引友好寫法

避免在列上使用函數:DATE(create_time) = ... 會導致索引失效。

❌ 慢查詢(索引失效)


WHERE DATE(create_time) = '2023-11-10'
WHERE YEAR(create_time) = 2023

✅ 快查詢(索引生效)


WHERE create_time >= '2023-11-10 00:00:00'
  AND create_time <  '2023-11-11 00:00:00'

WHERE create_time >= '2023-01-01'
  AND create_time <  '2024-01-01'

通用最佳實踐函數


-- 取某日期 00:00:00
DATE(date_col)

-- 取某日期 23:59:59
DATE_ADD(DATE(date_col), INTERVAL 1 DAY) - INTERVAL 1 SECOND
-- 或
DATE_ADD(DATE(date_col), INTERVAL 86399 SECOND)

應用層組裝 WHERE create_time >= ? AND create_time < ?,傳入起始/結束時間參數,兼顧索引與可讀性。

copyright © 萬盛學電腦網 all rights reserved