MySQL时间格式化函数怎么用？where查询中日期范围筛选有哪些技巧？

在业务系统的数据查询里，按天、按月统计数据是非常普遍的需求。MySQL提供了丰富的时间处理函数，但如果把它们直接写在where条件左侧，很可能让优化器放弃索引。理解date_format、str_to_date以及日期字面量的比较规则，是写出高效查询的前提。下面从函数原理、范围写法对比和索引优化三个角度展开说明。

一、date_format与str_to_date的核心机制

date_format的作用是把datetime或date类型按照指定格式转换成字符串，例如date_format(create_time, '%Y-%m-%d')会输出类似2024-03-12的文本。很多开发者习惯在where里写where date_format(create_time, '%Y-%m-%d') = '2024-03-12'，这种做法在逻辑上没错，但数据库必须先对每一行执行函数计算，再和常量比较，导致该列上的普通索引完全无法使用。

与之相对，str_to_date是把字符串解析成时间类型，通常用于把用户输入的文本条件转为时间再做比较。如果表中字段是datetime，而传入的是字符串'2024-03-12'，MySQL会尝试隐式转换，这时字段本身没有经过函数包裹，索引有可能被用上。理解这两个函数一个作用于字段、一个作用于常量，是避免索引失效的关键。

还需要注意格式符的含义，%Y是四位年，%m是两位月，%d是两位日，%H是24小时制小时。如果格式写错，比如用%h表示小时却期望24小时制，会得到错误的结果甚至空值。在调试时可以先单独执行select看转换后的内容，确认无误后再放入where条件。

select
  date_format(create_time, '%Y-%m-%d') as day_str,
  str_to_date('2024-03-12', '%Y-%m-%d') as parsed_time
from orders
limit 1;

二、日期范围筛选的几种写法与边界差异

最常见的范围筛选是使用between。例如where create_time between '2024-03-01 00:00:00' and '2024-03-31 23:59:59'，这种写法直观，但结束时间如果写成'2024-03-31'会被当作'2024-03-31 00:00:00'，漏掉当天其余时段的数据。使用between时必须显式补齐时间部分，否则边界容易出错。

更稳妥的写法是使用大于等于和小于：where create_time >= '2024-03-01' and create_time < '2024-04-01'。这种半开半闭区间不需要关心当天最后一秒，只需让结束日期前进一天即可，也避免了闰秒或毫秒精度带来的遗漏。对于按天统计，常配合date_format放在select里分组，而where里保持字段原样比较。

当需要根据相对时间筛选，比如最近七天，可以用interval：where create_time >= now() - interval 7 day。这里now()返回当前时间，interval表达式结果仍是时间类型，字段未被函数包裹，索引可用。若写成where date_format(create_time, '%Y-%m-%d') >= date_format(now() - interval 7 day, '%Y-%m-%d')则又回到索引失效的老路。

-- 推荐写法：半开区间，索引友好
select count(*)
from orders
where create_time >= '2024-03-01'
  and create_time < '2024-04-01';

-- 相对时间写法
select count(*)
from orders
where create_time >= now() - interval 7 day;

三、利用生成列与索引提升日期查询性能

如果业务频繁按天查询，可以考虑在表上增加一个生成列，把date(create_time)的结果持久化，再在这个列上建索引。生成列的定义如day_date date generated always as (date(create_time)) stored，之后where day_date = '2024-03-12'就能命中索引，避免了在where里直接调用函数。

这种方案的优点是查询语句干净，业务层不需要关心格式化逻辑；缺点是会占用额外存储空间，并且写入时多一次计算。对于上亿数据且按天统计频繁的场景，收益通常大于成本。如果使用的是MySQL 5.7及以上版本，虚拟生成列配合索引也是可行选择，不过虚拟列索引在查询时仍需计算，性能略逊于存储列。

另外要留意时区配置，now()取的是会话时区的时间，而字段若存的是UTC，直接比较会偏移八小时。可以在连接时设置time_zone，或者在写入阶段统一转换。以下示例展示如何建表并加生成列索引：

create table orders (
  id bigint primary key,
  create_time datetime,
  day_date date generated always as (date(create_time)) stored,
  index idx_day (day_date)
);

select count(*)
from orders
where day_date = '2024-03-12';

综合来看，日期范围筛选的核心原则是：where条件左侧尽量保持字段原貌，把格式化操作移到常量或select中；用半开区间代替between能减少边界错误；对高频维度可借助生成列索引。掌握这些技巧后，原本缓慢的报表查询往往能从数秒降到毫秒级。

MySQLdate_formatdatetime_query修改时间：2026-08-16 02:56:12