MySQL中DATE_FORMAT函数为什么会导致索引失效？-mysql教程-PHP中文网

date_format在where中失效是因为其对索引列实时计算导致无法使用b+树索引，应改用时间范围条件如create_time >= '2026-07-01 00:00:00' and create_time

DATE_FORMAT 在 WHERE 里用就失效

因为 MySQL 索引存的是 create_time 的原始 datetime 值，而 DATE_FORMAT(create_time, '%Y-%m-%d') 是对每一行实时计算的字符串结果。优化器没法拿着这个字符串去 B+Tree 里二分查找——它根本不在索引结构里。

常见错误写法：
SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2026-07-01';
这句会触发全表扫描，哪怕 create_time 上有索引也白搭。

不是“索引坏了”，是查询写法绕过了索引能力

函数作用在索引列上 → 索引树无法复用 → 只能逐行计算再比对

哪怕只查一天，性能也可能从几毫秒掉到几秒（数据量越大越明显）

替代方案：用范围条件代替格式化比较

把日期逻辑从“字符串相等”换回“时间范围”，索引就能立刻生效。

比如要查 2026-07-01 全天的数据，写成：
SELECT * FROM orders WHERE create_time >= '2026-07-01 00:00:00' AND create_time <p>这样写，MySQL 能直接用 <code>create_time</code> 索引定位起止位置，不用碰其他行。</p><div class="aritcle_card flexRow artxards">
											<div class="artcardd flexRow">
												<a class="aritcle_card_img" rel="nofollow" href="/xiazai/skill6716" title="OGT Docs Create"><img
														src="https://img.php.cn/upload/skill/000/000/081/179109380251617.jpg" alt="OGT Docs Create" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
												<div class="aritcle_card_info flexColumn">
													<a rel="nofollow" href="/xiazai/skill6716" title="OGT Docs Create" class="overflowclass">OGT Docs Create</a>
													<p class="overflowclass">在文档优先系统中创建新的文档实体。路由至专门的任务、定义、规则、功能及社交内容创建子技能。用于添加任何新文档。</p>
												</div>
												<a rel="nofollow" href="/xiazai/skill6716" title="OGT Docs Create" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
												</a>
											</div>
										</div>
比 <code> 更安全，避免跨日边界误差

注意时区：如果字段是 TIMESTAMP，确保常量也按同一时区理解

如果字段含毫秒（如 datetime(3)），范围写法依然精确；DATE_FORMAT 却可能因截断丢精度

真需要格式化输出？别混在 WHERE 里

格式化是展示需求，不是查询需求。WHERE 负责过滤，SELECT 负责呈现，职责必须分开。

正确分层写法：
SELECT
  id,
  create_time,
  DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') AS create_time_str
FROM orders
WHERE create_time >= '2026-07-01 00:00:00' AND create_time 
create_time 保持原类型，供 WHERE、ORDER BY、JOIN 使用

create_time_str 是额外加的字符串列，只用于前端显示或导出

千万别在 JOIN 后的 WHERE 里对关联表日期字段套 DATE_FORMAT，否则连关联顺序都可能被优化器误判

特殊情况：非得按格式查，又不能改应用逻辑？

如果上游系统硬性要求传入 '2026-07' 这种字符串月份，且你无法控制输入源，可以考虑虚拟列 + 索引：
ALTER TABLE orders ADD COLUMN ym CHAR(7) AS (DATE_FORMAT(create_time, '%Y-%m')) STORED;
CREATE INDEX idx_ym ON orders(ym);
之后就能安全地写：
SELECT * FROM orders WHERE ym = '2026-07';
虚拟列值在写入时计算并存储，索引建在它上面，完全可 SARGable

注意：STORED 是必须的，VIRTUAL 列不存物理值，无法建索引

该方案增加存储开销，且每次 INSERT/UPDATE 都多一次计算，权衡后再用

真正容易被忽略的点是：同一个字段，你在 SELECT 里格式化没关系，但只要出现在 WHERE、ON、HAVING 里被函数包裹，索引就作废。这不是 MySQL 的 bug，是索引机制本身的约束。