查詢「今天」資料最簡潔寫法:
SELECT * FROM `table` WHERE DATE(date_col) = CURDATE();
DATE() 只保留日期部分,直接與 CURDATE() 比較,可使用索引(MySQL 5.6+ 支援函數索引)。
| 需求 | SQL 範例 |
|---|---|
| 今天 | WHERE DATE(create_time) = CURDATE() |
| 本週 | WHERE YEARWEEK(create_time) = YEARWEEK(NOW()) |
| 上週 | WHERE YEARWEEK(create_time) = YEARWEEK(NOW()) - 1 |
| 本月 | WHERE DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') |
| 上月 | WHERE DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') |
| 最近 6 個月 | WHERE create_time BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW() |
| 最近 N 秒 | WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(create_time) <= 30 |
SELECT
COUNT(*) AS total,
SUM(IF(log_type=2, 1, 0)) AS type2_cnt,
SUM(IF(log_type=3, 1, 0)) AS type3_cnt
FROM test_log
WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(log_time) <= 30;
利用 IF(條件, 1, 0) 轉換為 0/1 再 SUM,可一次查詢完成多維度統計。
YEAR()、MONTH()),改用範圍查詢 BETWEEN 或 >=/< 以利用索引。DATE(create_time) 函數索引或生成欄位索引。TO_DAYS()、WEEKOFYEAR() 等函數同樣會阻礙索引,慎用於大表。