MySQL 日期查询优化与常用函数速查

核心痛点：日期区间查询的性能陷阱

在进行日期范围查询时，许多开发者习惯直接使用DATE_FORMAT函数将字段转换为字符串再进行比较，例如：
SELECT * FROM dateTest WHERE DATE_FORMAT(date,'%Y%m%d') BETWEEN '20210601' AND '20210825'
这种写法的致命缺陷在于：当对列应用函数时，MySQL无法使用该列上的索引，导致全表扫描。随着数据量增长，查询速度会急剧下降。

第1步：识别无效索引的日期写法

避免在WHERE子句中对被查询的日期列使用任何函数。如果必须格式化展示，应将逻辑放在应用程序层，或在SQL中使用范围比较。
错误示例：DATE_FORMAT(column, format) = 'value'
正确思路：直接使用BETWEEN或>= / <=进行值比较。

第2步：采用范围比较实现高性能查询

对于包含时间的字段，直接使用大于等于和小于号是最标准且高效的写法。假设要查询特定时间段的数据：
SELECT * FROM t_user WHERE addedTime >= '2017-01-01 00:00:00' AND addedTime < '2018-01-01 00:00:00'
这种方式能充分利用B-Tree索引，显著提升检索速度。如果是仅涉及日期的比较，确保比较值的格式与存储格式一致。

高频场景：基于当前时间的动态区间查询

在实际业务中，我们经常需要查询“今天”、“昨天”、“最近7天”或“本月”的数据。以下是经过优化的通用查询模板：

• 查询今天的数据：
  SELECT fullName, addedTime FROM t_user WHERE to_days(addedTime) <= to_days(now())
  注意：to_days()虽方便，但在极大数据量下也建议改用addedTime >= CURDATE()。
• 查询昨天的数据：
  SELECT fullName, addedTime FROM t_user WHERE to_days(NOW()) - TO_DAYS(addedTime) <= 1
• 查询近7天的数据：
  SELECT fullName, addedTime FROM t_user WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(addedTime)
• 查询本月的数据：
  SELECT fullName, addedTime FROM t_user WHERE MONTH(addedTime) = MONTH(CURDATE()) AND YEAR(addedTime) = YEAR(CURDATE())
  虽然DATE_FORMAT(addedTime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')常见，但拆分月份和年份有时比格式转换更易于索引优化（视具体索引结构而定），或者直接利用BETWEEN：
  SELECT * FROM table WHERE addedTime >= DATE_FORMAT(NOW(), '%Y-%m-01') AND addedTime < DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'), INTERVAL 1 MONTH)
• 查询本周的数据：
  SELECT fullName, addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now())

基础函数：获取与格式化日期时间

1. 获取当前时间戳与日期

掌握不同时间函数的细微差别是编写健壮SQL的关键。

• NOW()：返回当前日期和时间。在执行语句开始时确定值，后续查询中不再变化。
• SYSDATE()：返回函数执行时的精确时间。若在执行过程中有耗时操作（如SLEEP），其返回值会与NOW()不同。
  示例：SELECT NOW(), SLEEP(3), NOW(); 前后两个NOW()结果相同。
• CURDATE()：仅返回当前日期（无时间部分）。
  示例：SELECT CURDATE(); 输出 2021-08-31
• CURRENT_TIMESTAMP：同NOW()，支持带参数形式。

2. 日期与字符串的相互转换

当数据源格式不规范或需要自定义展示格式时，需使用转换函数。

• Date/Time to Str：DATE_FORMAT(date, format)
  将日期格式化为字符串。常用标识符：Year(%Y), Month(%m), Day(%d), Hour(%H)。
  示例：SELECT DATE_FORMAT('2021-08-31 22:23:01', '%Y%m%d%H%i%s'); 输出 20210831222301
• Str to Date：STR_TO_DATE(str, format)
  将符合特定格式的字符串解析为日期对象。常用于导入杂乱数据。
  示例：SELECT STR_TO_DATE('08/31/2021', '%m/%d/%Y'); 输出 2021-08-31

3. 其他实用转换工具

• To/From Days：TO_DAYS(date) 和 FROM_DAYS(days)。用于计算绝对天数，适用于跨世纪的天数差计算。
  示例：SELECT TO_DAYS('2021-08-31'); 输出 738398
• Unix Timestamp：UNIX_TIMESTAMP() 和 FROM_UNIXTIME(unix_timestamp)。便于与Java、Python等后端语言进行时间交互。

进阶运算：日期加减与差值计算

在处理周期性报表或倒计时功能时，日期计算函数必不可少。

1. 增加或减少时间间隔

DATE_ADD(date, INTERVAL value unit) 和 DATE_SUB(date, INTERVAL value unit) 功能相似，区别仅在于增加还是减少。支持单位包括：SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR等。

• 增加1天：DATE_ADD(NOW(), INTERVAL 1 DAY)
• 减去1个月：DATE_SUB(NOW(), INTERVAL 1 MONTH)
• 也可使用负数作为间隔值，如INTERVAL -1 DAY等同于减1天。

2. 计算两个日期的差值

• DATEDIFF(date1, date2)：仅计算天数差。
  示例：SELECT DATEDIFF('2021-08-31', '2021-08-20'); 输出 11
• TIMEDIFF(time1, time2)：计算时间差，返回HH:MM:SS格式。要求两个参数类型均为时间或日期时间。
  示例：SELECT TIMEDIFF('2021-08-31 08:08:08', '2021-08-30 00:00:00'); 输出 32:08:08

3. 时间戳高级操作

TIMESTAMPADD(unit, interval, datetime_expr) 类似于DATE_ADD。
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) 返回两个时间点之间的差值，以指定单位表示。
示例：SELECT TIMESTAMPDIFF(YEAR, '2001-01-01', '2021-08-31'); 输出 20，计算相隔多少年。

特殊场景：时区转换

在全球化应用中，处理不同时区的时间至关重要。CONVERT_TZ(dt, from_tz, to_tz) 函数可用于在不同时区间转换时间。

示例：将北京时间（+08:00）转换为UTC时间（+00:00）：
SELECT CONVERT_TZ('2021-08-31 12:00:00', '+08:00', '+00:00');
结果：2021-08-31 04:00:00

若无严格时区转换需求，也可通过DATE_ADD手动偏移小时数来实现时区调整，但推荐使用专门的时区函数以保证准确性。

简要总结

MySQL日期查询的核心在于平衡易用性与性能。日常开发中应优先使用>= / <= / BETWEEN结合标准时间字符串进行范围过滤，避免在WHERE条件中包裹日期列使用DATE_FORMAT等函数。对于复杂的周期性统计，MONTH()、YEAR()配合逻辑判断，或DATE_SUB/CURDATE()组合是更可靠的选择。熟练掌握这些函数及最佳实践，能有效解决绝大多数日期查询难题，保障数据库高效运行。以上就是MySQL日期格式化及复杂日期区间查询的详细内容，更多关于MySQL日期操作的资料请关注本站其它相关文章！