mysql 日期查询当天，当月，上个月，当年的数据sql语句

'# MySQL 日期查询当天、当月、上个月、当年的数据 SQL 语句

一、背景与问题

在业务系统中，日期范围查询是常见的数据检索需求。比如电商系统需要统计当日销售额、统计当月订单量、分析上月用户行为趋势、年度报表生成等场景。这类查询的核心在于如何通过 MySQL 的日期函数和条件表达式，准确地定位目标时间段。

传统做法中，开发人员常通过硬编码日期值（如 WHERE create_time >= '2023-10-01'）来实现，但这种方式存在以下问题：

1. 日期计算复杂：需手动计算上个月最后一天、当年最后一天等边界值
2. 可维护性差：每次查询需重新计算日期参数
3. 性能隐患：未对日期字段建立索引时可能导致全表扫描
4. 时区问题：不同服务器时区设置可能导致查询结果偏差

本文将深入探讨如何通过 MySQL 日期函数实现灵活的日期范围查询，分析不同场景下的实现方式，并给出可复用的解决方案。

二、基本原理

MySQL 提供了丰富的日期函数来处理时间类型字段。核心函数包括：

日期范围查询的关键在于：

• 确定起始时间：如当天开始时间 DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
• 确定结束时间：如当天结束时间 DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')
• 计算边界值：如上个月最后一天 LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

三、环境准备

-- 创建测试表
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    create_time DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO sales (order_no, customer_id, amount, create_time) VALUES
('SN20231001001', 1001, 150.00, '2023-10-01 08:00:00'),
('SN20231001002', 1002, 200.00, '2023-10-01 12:30:00'),
('SN20230930001', 1003, 300.00, '2023-09-30 18:00:00'),
('SN20230929001', 1004, 400.00, '2023-09-29 09:00:00'),
('SN20230831001', 1005, 500.00, '2023-08-31 15:00:00');

四、核心实现

1. 查询当天数据

SELECT * FROM sales
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
  AND create_time < DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59');

关键代码解释：

• DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')：获取当天零点时刻
• DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')：获取当天23:59:59时刻
• 使用 < 而不是 <= 是为了防止跨天查询（例如：2023-10-01 23:59:59 和 2023-10-02 00:00:00 的边界处理）

性能优化：在 create_time 字段上建立索引：

CREATE INDEX idx_create_time ON sales(create_time);

2. 查询当月数据

SELECT * FROM sales
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
  AND create_time < DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59');

关键代码解释：

• DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')：获取当月第一天零点
• LAST_DAY(NOW())：获取当月最后一天（如 2023-10-31）
• DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59')：获取当月最后一天23:59:59

性能优化：使用范围查询时，索引效率更高：

EXPLAIN SELECT * FROM sales
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
  AND create_time < DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59');

3. 查询上个月数据

SELECT * FROM sales
WHERE create_time >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
  AND create_time < DATE_FORMAT(DATE_SUB(LAST_DAY(NOW()), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59');

关键代码解释：

• DATE_SUB(NOW(), INTERVAL 1 MONTH)：获取上个月的第一天
• DATE_SUB(LAST_DAY(NOW()), INTERVAL 1 MONTH)：获取上个月的最后一天
• 需要特别注意时区问题，建议在应用层统一处理日期计算

常见错误：直接使用 create_time >= '2023-09-01' 会包含2023-09-01 00:00:00到2023-09-30 23:59:59的数据，但可能遗漏9月30日的记录（因 LAST_DAY() 的计算方式）。

五、完整案例

业务场景：电商销售统计系统

需求：按天/月统计销售数据，支持当日、当月、上月、当年的聚合查询

实现方案：

1. 数据表结构：

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    create_time DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 按天统计：

SELECT 
    DATE(create_time) AS day,
    SUM(amount) AS total_sales
FROM sales
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
  AND create_time < DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')
GROUP BY day
ORDER BY day DESC;

3. 按月统计：

SELECT 
    DATE_FORMAT(create_time, '%Y-%m') AS month,
    SUM(amount) AS total_sales
FROM sales
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
  AND create_time < DATE_FORMAT(LAST_DAY(NOW()), '%Y-%m-%d 23:59:59')
GROUP BY month
ORDER BY month DESC;

4. 按年统计：

SELECT 
    DATE_FORMAT(create_time, '%Y') AS year,
    SUM(amount) AS total_sales
FROM sales
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-01-01 00:00:00')
  AND create_time < DATE_FORMAT(DATE_SUB(LAST_DAY(NOW()), INTERVAL 1 YEAR), '%Y-%m-%d 23:59:59')
GROUP BY year
ORDER BY year DESC;

性能优化建议：

• 对 create_time 字段建立索引
• 对大表使用分区表（按日期分区）
• 对聚合查询使用缓存（如 Redis 缓存月度统计结果）

六、源码解析

以查询上个月数据为例，拆解关键步骤：

SELECT * FROM sales
WHERE create_time >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
  AND create_time < DATE_FORMAT(DATE_SUB(LAST_DAY(NOW()), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59');

分步解释：

1. NOW() 获取当前时间 2023-10-05 14:30:00
2. DATE_SUB(NOW(), INTERVAL 1 MONTH) 得到 2023-09-05 14:30:00
3. DATE_FORMAT(..., '%Y-%m-01 00:00:00') 得到 2023-09-01 00:00:00（上个月第一天）
4. LAST_DAY(NOW()) 得到 2023-10-31（当月最后一天）
5. DATE_SUB(LAST_DAY(NOW()), INTERVAL 1 MONTH) 得到 2023-09-30（上个月最后一天）
6. DATE_FORMAT(..., '%Y-%m-%d 23:59:59') 得到 2023-09-30 23:59:59

索引使用分析：

当 create_time 字段建立索引时，MySQL 会使用索引进行范围扫描，而不是全表扫描。但需要注意：

• 如果查询条件中包含函数（如 DATE(create_time)），索引可能失效
• 建议使用原始时间字段进行比较，如 create_time >= '2023-09-01 00:00:00'

七、进阶使用

1. 动态日期范围计算

-- 查询指定日期范围的数据
SELECT * FROM sales
WHERE create_time >= '2023-09-01 00:00:00'
  AND create_time < '2023-10-01 00:00:00';

2. 带时间戳的精确查询

SELECT * FROM sales
WHERE create_time >= '2023-10-01 08:00:00'
  AND create_time < '2023-10-02 00:00:00';

3. 复杂时间范围组合

SELECT * FROM sales
WHERE 
    (create_time >= '2023-09-01 00:00:00' AND create_time < '2023-10-01 00:00:00')
    OR 
    (create_time >= '2023-11-01 00:00:00' AND create_time < '2023-12-01 00:00:00');

八、性能与工程实践

1. 性能优化策略

2. 异常处理

SELECT * FROM sales
WHERE create_time >= DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
  AND create_time < DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59')
LIMIT 1000;

3. 安全风险

SQL 注入风险：

-- 错误示例（不安全）
SELECT * FROM sales WHERE create_time >= '$start_date';

正确做法：

-- 安全示例（使用预编译）
SELECT * FROM sales WHERE create_time >= ?;

九、常见问题与踩坑

1. 时区问题

错误示例：

SELECT * FROM sales WHERE create_time >= '2023-10-01 00:00:00';

问题：服务器时区设置不同，可能导致查询结果偏差

解决方案：

• 在查询中显式指定时区
• 使用 CONVERT_TZ() 函数进行时区转换
• 保持应用层和数据库层时区一致

2. 边界值错误

错误示例：

SELECT * FROM sales WHERE create_time >= '2023-09-30 23:59:59';

问题：未包含9月30日的记录

解决方案：

SELECT * FROM sales WHERE create_time >= '2023-09-30 00:00:00'
  AND create_time < '2023-10-01 00:00:00';

3. 索引失效问题

错误示例：

SELECT * FROM sales WHERE DATE(create_time) = '2023-10-01';

问题：DATE(create_time) 函数导致索引失效

解决方案：

SELECT * FROM sales WHERE create_time >= '2023-10-01 00:00:00'
  AND create_time < '2023-10-02 00:00:00';

十、最佳实践

1. 查询规则

• 使用原始时间字段进行比较
• 避免在查询条件中使用日期函数
• 使用 >= 和 < 而不是 <= 和 > 处理边界值
• 对聚合查询使用 GROUP BY 和 SUM() 等函数

2. 索引策略

• 对日期字段建立索引
• 对高频查询的日期范围建立复合索引（如 (create_time, status)）
• 对分区表使用按日期分区（如按月分区）

3. 安全规范

• 使用预编译语句防止 SQL 注入
• 对用户输入的日期进行校验和格式化
• 对敏感数据进行脱敏处理

4. 性能优化

• 对大表使用分区表
• 对聚合查询使用缓存
• 对高频查询设置查询缓存
• 对慢查询进行分析和优化

十一、总结

本文深入探讨了 MySQL 中日期范围查询的实现方法，通过多个代码示例展示了如何灵活地查询当天、当月、上个月和当年的数据。我们分析了不同实现方式的原理，指出了常见的错误和解决方案，并给出了性能优化和安全方面的建议。

在实际开发中，应根据具体业务需求选择合适的查询策略。对于大数据量的场景，建议使用分区表和索引优化；对于频繁的日期范围查询，可以考虑使用缓存机制。同时，要特别注意时区问题和边界值处理，避免因简单的日期计算导致数据错误。

通过掌握这些技巧，开发人员可以更高效地处理日期相关的查询需求，提高系统的稳定性和性能。在实际项目中，建议结合具体业务场景进行测试和调优，以达到最佳效果。