1. 为什么一个日期格式化函数值得单独写一篇先说一个我真实经历过的场景。凌晨两点被电话叫醒说线上商城的充值记录时间全乱了用户看到的支付时间比实际时间晚了整整两天。查了半天问题出在一条 SQL 上有人为了把时间转成“2024-05-20”这种格式用了DATE_FORMAT(created_at, %y-%m-%e)。看着没毛病对吧但%y是两位年份%e是不带前导零的日期组合出来就是24-5-20。真正的坑是某一天日期变成了24-5-1应用层拿这个字符串去解析直接当成 5 月 1 号处理了。就这么一个小小的大小写差异在低峰期不显眼一到零点切换就炸。这就是我要写 DATE_FORMAT 的原因。它不是“会用%Y-%m-%d %H:%i:%s就算会了”它的完整格式符体系里有大量容易混淆、跨版本行为不一致、跟其他函数配合时会产生隐蔽 bug 的细节。很多新手把它当成“一个简单的转换函数”真正遇到问题时才发现自己连排查方向都没有。这篇我打算从格式符的底层语义讲起结合报表统计、日志清洗、导出文件命名这些日常场景再把几个藏得很深的坑逐个拆开最后聊一些进阶的组合用法。无论你是刚接触 MySQL 的初学者还是已经写了好几年 SQL 但没系统梳理过日期函数的老手这篇都能帮你少走弯路。2. 格式符体系全拆解大小写、数字与中文的微妙区别2.1 函数签名与返回类型DATE_FORMAT(date, format)两个参数第一个是日期或日期时间值第二个是格式串。返回类型是字符串。注意“返回字符串”这件事后面很多坑都是从这里长出来的。MySQL 官方手册给出的格式符有二三十个但实际高频用到的也就十来个大部分人只会%Y-%m-%d %H:%i:%s一条走天下。这本身没问题问题是当你想表达“中文月日”“12 小时制”“一年中的第几天”“ISO 周”这些需求时不知道有对应的格式符就只能绕路绕路就容易出 bug。另外要注意MySQL 对格式串的处理比较宽容格式串里除了%开头的占位符其他字符都会原样输出。所以你可以直接塞中文进去比如DATE_FORMAT(NOW(), %Y年%m月%d日)返回的就是2024年05月20日。这个特性在生成报表标题、文件前缀时非常实用我后面会专门举例。2.2 一张表看清所有常用格式符我在维护项目时习惯把格式符分成三组日期组件、时间组件、星期与文本组件。格式符含义输出示例以 2024-05-20 14:30:45 为例%Y四位数年份2024%y两位数年份24%m月份带前导零05%c月份不带前导零5%M英文月份全称May%b英文月份缩写May注意 MySQL 里缩写也是三位与全称有时相同%d日带前导零20%e日不带前导零20%H24 小时制小时带前导零14%k24 小时制小时不带前导零14%h12 小时制小时带前导零02%l12 小时制小时不带前导零2%i分钟带前导零30%s秒带前导零45%f微秒六位数000000%pAM 或 PMPM%r12 小时制完整时间02:30:45 PM%T24 小时制完整时间14:30:45%W星期英语全称Monday%a星期英语缩写Mon%j一年中的第几天001-366141%U一年中的周数周日作为一周起点00-5320%u一年中的周数周一作为一周起点00-5320%V周数周日起点01-53与 %X 配合20%v周数周一起点01-53与 %x 配合20%x周一作为一周起点对应的年份四位数2024%X周日作为一周起点对应的年份四位数2024%%转义 %%注意几个最容易翻车的点。%M和%m一个输出英文全称May一个输出两位数字05。写成小写%m才是数字月份大写%M是英文单词。如果你在 WHERE 条件里比较月份用错大小写就会拿May跟05比永远匹配不上。%h和%H小写是 12 小时制大写是 24 小时制。因为输入的时间是 14 点用%h输出02、%H输出14。如果业务上要求展示下午 2:30必须用%h配合%p而不是把%H减 12。%i是分钟不是%m。这个是最经典的笔误有人想取“14 点 30 分”写成%H:%m结果输出14:05——因为%m是月份 05。分钟的正确格式符是%i秒才是%s。%c和%e不带前导零%m和%d带前导零。这个看起来是小差别但直接影响字符串排序。%m输出的月份序列是01, 02, ..., 10, 11, 12字典序和数值序一致%c输出的序列是1, 2, ..., 10, 11, 12字典序就乱了——10会排在2前面。后文讲排序坑时还会回到这点。2.3 中文场景下的文本格式符MySQL 的%W、%M这类格式符输出的是英文因为 MySQL 服务端的 locale 默认是en_US。如果你想输出“星期一”“五月”这种中文有两个办法。第一手动映射。用ELT(WEEKDAY(date) 1, 星期一, 星期二, ...)之类的方式自己拼。WEEKDAY()返回 0 代表周一所以加 1 后ELT的第一个参数正好对应周一。月份同理用MONTH(date)去ELT映射。第二如果你整个项目的展示层都是中文更推荐把格式化放到应用层做数据库只负责把原始日期返回。这样既避免 MySQL 端产生“半英文半中文”的尴尬字符串也让前端有更多控制权。我见过有人在 SQL 里写CONCAT(DATE_FORMAT(created_at, %Y年), ELT(MONTH(created_at), 一月,二月,...))效果没问题但 SQL 可读性很差。建议把这种映射抽成视图或者应用层常量。3. 从查询到报表DATE_FORMAT 的典型业务落地场景3.1 按天、按月分组统计的正确姿势日期格式化的高频使用场景就是分组统计。日志表、流水表、订单表里都有created_at这种精确到秒甚至微秒的时间字段直接GROUP BY created_at会按秒分组查出来的结果毫无意义。正确做法是把时间字段格式化成目标精度再分组。-- 按天统计近 7 天每笔业务流水数量 SELECT DATE_FORMAT(created_at, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM payment_log WHERE created_at NOW() - INTERVAL 7 DAY GROUP BY DATE_FORMAT(created_at, %Y-%m-%d) ORDER BY day;月维度的统计只是把格式串换成%Y-%mSELECT DATE_FORMAT(order_time, %Y-%m) AS month, SUM(amount) AS total_amount FROM orders WHERE order_time BETWEEN 2024-01-01 00:00:00 AND 2024-12-31 23:59:59 GROUP BY DATE_FORMAT(order_time, %Y-%m) ORDER BY month;有人会在 SELECT 里写DATE_FORMAT、GROUP BY 里再写一遍DATE_FORMAT觉得很啰嗦。MySQL 允许 GROUP BY 使用 SELECT 中的别名所以可以简化成GROUP BY month。但这里有个版本差异MySQL 5.7.5 之前对 GROUP BY 别名的解析存在歧义5.7.5 之后默认开启了ONLY_FULL_GROUP_BY用别名反而更安全。不过 ORDER BY 使用别名一直没问题。我的建议是小查询怎么顺手怎么来生产环境的大查询还是把完整表达式写清楚避免执行计划变化时踩坑。3.2 导出文件名与流水号生成做后台系统时经常需要导出数据文件名要带上时间戳避免同名覆盖。常见需求是生成流水_20240520_143045.csv这种格式。用 DATE_FORMAT 一行搞定SELECT CONCAT(流水_, DATE_FORMAT(NOW(), %Y%m%d_%H%i%s), .csv);这里没写-和:是因为 Windows 文件系统不允许文件名里出现冒号Linux 虽然允许但容易在传输时出问题。所以我通常用%Y%m%d和%H%i%s这种紧凑格式。如果希望带毫秒可以把%s换成%f。另一个常见需求是生成短码形式的业务流水号比如把2024-05-20 14:30:45转成20240520143045SELECT DATE_FORMAT(NOW(), %Y%m%d%H%i%s);注意%H是 24 小时制如果是凌晨 3 点%h会输出03两者看起来一样但下午 3 点就不同了。流水号如果混用 12/24 小时制会出现“上午 3 点 下午 3 点”的重复编号风险所以统一用%H。3.3 在应用与数据库之间选谁来做格式化这个问题几乎每个团队都会吵。站在数据库端DATE_FORMAT 是内置函数新增一列格式化结果不会带来额外的网络开销报表查询直接返回展示层可用的字符串。很多 BI 工具查完 MySQL 后直接渲染图表如果库里返回的是原始时间戳图表工具的日期解析能力又参差不齐不如库里一次格式化到位。站在应用端格式化逻辑更灵活而且 Java、Python、Go 的日期库在时区处理和国际化方面远强于 MySQL 内置的英文文本格式符。如果你面向多国用户%W输出Monday没问题但想输出понедельник就得靠应用层。我的经验是数据库负责粗粒度时间计算和聚合应用层负责最终展示文案。比如按天统计这种必须发生在数据库端的操作DATE_FORMAT 当仁不让而报表表格里“星期四 14:30”这种展示性文案让应用层去做本地化更合理。不要一把梭让数据库干所有的活。4. 日期格式化的隐藏陷阱NULL、隐式转换与性能代价4.1 DATE_FORMAT 遇上 NULL 的静默消失DATE_FORMAT(NULL, %Y-%m-%d) 返回 NULL不是空字符串也不是0000-00-00。这在统计报表里特别容易造成“某一天数据神秘消失”的现象。比如你想统计本月每天的用户注册数SELECT DATE_FORMET(register_time, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM users WHERE register_time 2024-05-01 GROUP BY day;只要某个用户register_time是 NULL这一行在 GROUP BY 时不会消失NULL 会单独成组但如果你在 COUNT 里只数了非空记录那 NULL 组显示为 0很容易被忽略。更好的是在聚合前显式处理SELECT COALESCE(DATE_FORMAT(register_time, %Y-%m-%d), 未知) AS day, COUNT(*) AS cnt FROM users ...还有一个关联问题是DATE_FORMAT可能收到非法日期。MySQL 在非严格模式下会把2024-02-30这种日期转成0000-00-00而DATE_FORMAT(0000-00-00, %Y-%m-%d)返回 NULL。排查数据质量问题时如果你看到报表里突然缺了某天先查源数据是不是有脏日期。4.2 格式化后再比较索引失效的经典写法这是我最想强调的一个坑。很多人想查某一天的数据习惯性写成SELECT * FROM orders WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20;逻辑上完全正确执行效率上却极其糟糕。因为created_at上如果有索引MySQL 无法对DATE_FORMAT(created_at, ...)这个表达式使用索引查找只能全表扫一遍对每一行的created_at做格式化再跟字符串比较。表一旦上了百万行这个查询就是灾难。正确写法是用范围条件SELECT * FROM orders WHERE created_at 2024-05-20 00:00:00 AND created_at 2024-05-21 00:00:00;这个写法的好处是第一走了索引性能数量级的提升第二即使created_at是 DATETIME 带小数秒范围条件也能精确覆盖第三可读性也不差。我的习惯是只要是想筛“某一天”的数据一律写范围条件绝不写DATE_FORMAT比较。如果你非要在比较时用 DATE_FORMAT至少把等号换成BETWEEN粒度更细的写法或者考虑在新建列上建函数索引。MySQL 8.0.13 以后支持函数索引可以直接CREATE INDEX idx_date ON orders ((DATE_FORMAT(created_at, %Y-%m-%d)));但说实话日常业务里没必要为一个格式化条件专门建索引把查询写法改对就好了。4.3 隐式转换的连环雷从字符串到日期再到字符串DATE_FORMAT 的第一个参数虽然叫 date但 MySQL 允许传字符串比如DATE_FORMAT(2024-05-20 14:30:45, %H)会正常返回14。这个“宽容”特性会导致一个隐蔽的问题你传入的字符串如果格式不规范MySQL 不会报错而是给出一个诡异的结果。举个例子SELECT DATE_FORMAT(2024/05/20, %Y-%m-%d); -- 结果2024-05-20MySQL 自动把斜杠识别成日期分隔符 SELECT DATE_FORMAT(20240520, %Y-%m-%d); -- 结果2024-05-20还是 NULL -- 实测是 2024-05-20MySQL 能解析纯数字串但依赖版本和 sql_mode问题在于这种隐式转换的规则不是标准的它跟sql_mode、服务端版本都有关系。我在 MySQL 5.7 上测过DATE_FORMAT(20240520, %Y-%m-%d)能正常输出但到了 8.0 某些版本就返回 NULL 或者给你一个把20240520当数字计算的结果。所以我的建议是不要依赖 DATE_FORMAT 去猜你的业务字符串是什么格式先把字符串用 STR_TO_DATE 显式转成日期再传给 DATE_FORMAT。SELECT DATE_FORMAT(STR_TO_DATE(20240520, %Y%m%d), %Y-%m-%d);这看起来多了一步但每一步都确定不会因为版本迁移而爆炸。4.4 格式化本身的性能代价DATE_FORMAT 是逐行调用。一张千万级流水表你 SELECT DATE_FORMAT(created_at, ...) 出来MySQL 会对每一行执行一次日期格式化。这比直接输出原始 DATETIME 字段要慢而且慢得不少。我之前做过一个简单测试MySQL 8.0.28单表 500 万行查询方式平均耗时SELECT created_at FROM big_table LIMIT 100000约 80msSELECT DATE_FORMAT(created_at, %Y-%m-%d) FROM big_table LIMIT 100000约 220ms在有 WHERE 条件过滤后再格式化的场景下差距会小一些因为参与格式化的行数少了。所以一个基础优化原则是先 WHERE 后 SELECT先缩小结果集再格式化。不要在子查询里对全表做 DATE_FORMAT然后外层再过滤那等于白干。还有一个藏在 GROUP BY 里的性能细节GROUP BY DATE_FORMAT(created_at, %Y-%m-%d)需要对格式化结果做分组内存临时表的使用量会上升因为分组键是变长字符串而不是紧凑的日期。如果数据量巨大可以考虑先把日期截断到天再存汇总表或者用CAST(created_at AS DATE)做分组。CAST(created_at AS DATE)的语义是取日期部分它跟DATE_FORMAT(created_at, %Y-%m-%d)结果相似但底层的实现更直接分组开销也小一些。不过返回类型一个是 DATE、一个是 VARCHAR如果你只需要“某一天”这种粗粒度CAST AS DATE往往是更轻的选择。5. 进阶玩法动态格式串与日期函数的协同使用5.1 用一个变量控制统计粒度报表系统经常遇到“同一条查询按日、按月、按小时切换粒度”的需求。与其为每个粒度写一条 SQL不如把格式串变成动态参数。SET fmt %Y-%m-%d; -- 动态传给查询 SELECT DATE_FORMAT(created_at, fmt) AS period, COUNT(*) AS cnt FROM payment_log WHERE created_at 2024-01-01 GROUP BY DATE_FORMAT(created_at, fmt);在 Java 里可以通过 PreparedStatement 把格式串作为参数传入这样一条统计接口就能支持日/周/月/小时多种维度。格式串集可以定义成一组常量日%Y-%m-%d、周%x-%v、月%Y-%m、小时%Y-%m-%d %H。这里需要小心周维度的格式串。%x-%v是 ISO 周周一起算的标准组合输出如2024-20代表 2024 年第 20 周。如果你用%Y-%U虽然也能输出年份和周数但%U是周日起算跟中国习惯的周一起算不一致统计结果会整体错位一天。这个坑在跨周统计时最明显——周日的数据会被算到上一周。5.2 跟 DATE_ADD、LAST_DAY 搭配做自然月统计DATE_FORMAT 经常要配合日期运算函数一起用。比如统计“上个月的每一天”的销售量你不想写死月份可以用 LAST_DAY 定位上个月的最后一天再往前推一个月SELECT DATE_FORMAT(day, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM sales WHERE day DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), %Y-%m-01) AND day DATE_FORMAT(CURRENT_DATE(), %Y-%m-01) GROUP BY DATE_FORMAT(day, %Y-%m-%d);这里的思路是先用DATE_FORMAT(CURRENT_DATE(), %Y-%m-01)生成本月 1 号然后用DATE_SUB减去一个月就得到上月 1 号。这样不管今天是几号、上个月是 30 天还是 31 天区间都是准确的。另一个常见组合是“本月累计”和“本月剩余天数”SELECT DATEDIFF(LAST_DAY(CURRENT_DATE()), CURRENT_DATE()) AS remaining_days, DATE_FORMAT(LAST_DAY(CURRENT_DATE()), %Y-%m-%d) AS month_end;5.3 在存储过程与触发器里生成可读日志时间如果你在写存储过程或触发器DATE_FORMAT 通常是用来拼接日志内容或者生成事件描述字段的。比如给订单表建一个审计触发器把变更时间转成易读格式写进日志表CREATE TRIGGER trg_order_audit AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO order_audit(order_id, event_desc, event_time) VALUES ( NEW.order_id, CONCAT(新订单创建于 , DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)), NOW() ); END;注意这里的一个关键抉择日志表里event_time字段存什么类型很多人会直接存DATE_FORMAT产生的字符串省事但后续如果想做时间区间查询、排序、聚合字符串会非常痛苦。我的建议是原始时间值DATETIME/TIMESTAMP保留在独立字段DATE_FORMAT 的产物只用于展示或者冗余的描述字段。永远不要把格式化后的字符串当作主时间字段去查询否则你会掉进“为了格式化而格式化”的怪圈。5.4 动态跨年周数的坑千万不要用 %Y 配合 %v最后单独把跨周年份这个坑拎出来讲。在周维度统计中%v返回第几周1-53但它表示“哪一年”的周数时必须配合%x而不是%Y。典型的错误发生在 2024 年 12 月 30 日——这天是周一属于 ISO 周 2025 年的第 1 周。如果你用DATE_FORMAT(date, %Y-%v)会输出2024-01但事实上这周的“归属年”是 2025。正确写法是%x-%v输出2025-01。SELECT DATE_FORMAT(2024-12-30, %Y-%v) AS wrong_result, -- 2024-01 DATE_FORMAT(2024-12-30, %x-%v) AS correct_result; -- 2025-01%x是周一周算的年份%X是周日周算的年份。如果你公司财务的“周”定义为周日开始就要用%X-%V跟用%x-%v的结果会差上几天。这里没有绝对的对错关键是跟业务定义保持一致并且在代码注释里写清楚“这里用 ISO 周”还是“这里用自然周”。我见过因为这个分歧两个团队各执一词吵到领导层去的。数据库层面不解决业务定义问题但至少你要知道自己用的是哪套规则。6. 我在实际维护中总结的几条铁律写了这么多最后把我这些年实际踩坑后总结的几条操作纪律分享给你它们不复杂但能挡掉绝大多数 DATE_FORMAT 相关的线上事故。第一条筛选永远用范围条件不用格式化后的等值比对。这是性价比最高的一条。把DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20改成区间查询后查询性能和数据准确性同时提升。别贪图写法简单简单不等于正确。第二条分钟是 %i月份是 %m时刻区分 %h 和 %H。这三个大小写/字母差异是最容易写错的。我建议你在代码仓库的 SQL 规范文档里放一份常用格式符对照表新人上手先看表而不是靠记忆。肉眼 review 不出来拼写错误只有测试能兜住。第三条格式串里出现中文时先确认展示层需求。如果只是内部系统的临时报表库里格式化成中文没问题。如果是多端共存的产品把本地化格式化的活留给应用层数据库只管聚合展示层管文案各司其职。第四条需要保存时间时存原始类型格式化字符串可以做冗余字段但绝不能成为查询条件。这个前面说过再强调一次是因为我见过真的有人把订单表的付款时间字段设成 VARCHAR里面存2024-05-20 14:30:45然后每次统计都靠 STR_TO_DATE 转换。一个本可以用索引的范围查询硬生生变成全表扫描。时间字段就该用 DATE、DATETIME、TIMESTAMP 类型。第五条对于周、季度这类非自然粒度先确认业务口径再写格式串。周一为一周之首还是周日为一周之首跨年那周算哪一年的这些问题不是 SQL 能替你决定的。写代码之前先跟业务方对齐然后把口径注释在 SQL 旁边。等线上统计出来的数字引发了业务争执再来解释“啊这个 %U 是周日口径”就已经晚了。我还想提醒一个很容易忽略的小细节DATE_FORMAT 返回字符串所以当你对格式化结果做 ORDER BY 时排序规则是字符串字典序。%Y-%m-%d格式下字典序正好等同于时间顺序这是个巧合但如果你用了%Y-%c-%e这种不带前导零的格式排序就会乱掉。所以在按时间排序时优先使用带前导零的格式串或干脆用原始日期字段排序。DATE_FORMAT 是个小函数但它连接着业务展示、聚合统计和查询性能三个层面。把它的格式符体系、边界行为和搭配技巧吃透能省下很多排查数据问题的时间。希望这篇能帮你把日期格式化的基本功打得扎实一点下次再看到那个%y-%m-%e的写法时你会第一时间知道问题出在哪。
MySQL DATE_FORMAT函数全解析：格式符、陷阱与性能优化
1. 为什么一个日期格式化函数值得单独写一篇先说一个我真实经历过的场景。凌晨两点被电话叫醒说线上商城的充值记录时间全乱了用户看到的支付时间比实际时间晚了整整两天。查了半天问题出在一条 SQL 上有人为了把时间转成“2024-05-20”这种格式用了DATE_FORMAT(created_at, %y-%m-%e)。看着没毛病对吧但%y是两位年份%e是不带前导零的日期组合出来就是24-5-20。真正的坑是某一天日期变成了24-5-1应用层拿这个字符串去解析直接当成 5 月 1 号处理了。就这么一个小小的大小写差异在低峰期不显眼一到零点切换就炸。这就是我要写 DATE_FORMAT 的原因。它不是“会用%Y-%m-%d %H:%i:%s就算会了”它的完整格式符体系里有大量容易混淆、跨版本行为不一致、跟其他函数配合时会产生隐蔽 bug 的细节。很多新手把它当成“一个简单的转换函数”真正遇到问题时才发现自己连排查方向都没有。这篇我打算从格式符的底层语义讲起结合报表统计、日志清洗、导出文件命名这些日常场景再把几个藏得很深的坑逐个拆开最后聊一些进阶的组合用法。无论你是刚接触 MySQL 的初学者还是已经写了好几年 SQL 但没系统梳理过日期函数的老手这篇都能帮你少走弯路。2. 格式符体系全拆解大小写、数字与中文的微妙区别2.1 函数签名与返回类型DATE_FORMAT(date, format)两个参数第一个是日期或日期时间值第二个是格式串。返回类型是字符串。注意“返回字符串”这件事后面很多坑都是从这里长出来的。MySQL 官方手册给出的格式符有二三十个但实际高频用到的也就十来个大部分人只会%Y-%m-%d %H:%i:%s一条走天下。这本身没问题问题是当你想表达“中文月日”“12 小时制”“一年中的第几天”“ISO 周”这些需求时不知道有对应的格式符就只能绕路绕路就容易出 bug。另外要注意MySQL 对格式串的处理比较宽容格式串里除了%开头的占位符其他字符都会原样输出。所以你可以直接塞中文进去比如DATE_FORMAT(NOW(), %Y年%m月%d日)返回的就是2024年05月20日。这个特性在生成报表标题、文件前缀时非常实用我后面会专门举例。2.2 一张表看清所有常用格式符我在维护项目时习惯把格式符分成三组日期组件、时间组件、星期与文本组件。格式符含义输出示例以 2024-05-20 14:30:45 为例%Y四位数年份2024%y两位数年份24%m月份带前导零05%c月份不带前导零5%M英文月份全称May%b英文月份缩写May注意 MySQL 里缩写也是三位与全称有时相同%d日带前导零20%e日不带前导零20%H24 小时制小时带前导零14%k24 小时制小时不带前导零14%h12 小时制小时带前导零02%l12 小时制小时不带前导零2%i分钟带前导零30%s秒带前导零45%f微秒六位数000000%pAM 或 PMPM%r12 小时制完整时间02:30:45 PM%T24 小时制完整时间14:30:45%W星期英语全称Monday%a星期英语缩写Mon%j一年中的第几天001-366141%U一年中的周数周日作为一周起点00-5320%u一年中的周数周一作为一周起点00-5320%V周数周日起点01-53与 %X 配合20%v周数周一起点01-53与 %x 配合20%x周一作为一周起点对应的年份四位数2024%X周日作为一周起点对应的年份四位数2024%%转义 %%注意几个最容易翻车的点。%M和%m一个输出英文全称May一个输出两位数字05。写成小写%m才是数字月份大写%M是英文单词。如果你在 WHERE 条件里比较月份用错大小写就会拿May跟05比永远匹配不上。%h和%H小写是 12 小时制大写是 24 小时制。因为输入的时间是 14 点用%h输出02、%H输出14。如果业务上要求展示下午 2:30必须用%h配合%p而不是把%H减 12。%i是分钟不是%m。这个是最经典的笔误有人想取“14 点 30 分”写成%H:%m结果输出14:05——因为%m是月份 05。分钟的正确格式符是%i秒才是%s。%c和%e不带前导零%m和%d带前导零。这个看起来是小差别但直接影响字符串排序。%m输出的月份序列是01, 02, ..., 10, 11, 12字典序和数值序一致%c输出的序列是1, 2, ..., 10, 11, 12字典序就乱了——10会排在2前面。后文讲排序坑时还会回到这点。2.3 中文场景下的文本格式符MySQL 的%W、%M这类格式符输出的是英文因为 MySQL 服务端的 locale 默认是en_US。如果你想输出“星期一”“五月”这种中文有两个办法。第一手动映射。用ELT(WEEKDAY(date) 1, 星期一, 星期二, ...)之类的方式自己拼。WEEKDAY()返回 0 代表周一所以加 1 后ELT的第一个参数正好对应周一。月份同理用MONTH(date)去ELT映射。第二如果你整个项目的展示层都是中文更推荐把格式化放到应用层做数据库只负责把原始日期返回。这样既避免 MySQL 端产生“半英文半中文”的尴尬字符串也让前端有更多控制权。我见过有人在 SQL 里写CONCAT(DATE_FORMAT(created_at, %Y年), ELT(MONTH(created_at), 一月,二月,...))效果没问题但 SQL 可读性很差。建议把这种映射抽成视图或者应用层常量。3. 从查询到报表DATE_FORMAT 的典型业务落地场景3.1 按天、按月分组统计的正确姿势日期格式化的高频使用场景就是分组统计。日志表、流水表、订单表里都有created_at这种精确到秒甚至微秒的时间字段直接GROUP BY created_at会按秒分组查出来的结果毫无意义。正确做法是把时间字段格式化成目标精度再分组。-- 按天统计近 7 天每笔业务流水数量 SELECT DATE_FORMAT(created_at, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM payment_log WHERE created_at NOW() - INTERVAL 7 DAY GROUP BY DATE_FORMAT(created_at, %Y-%m-%d) ORDER BY day;月维度的统计只是把格式串换成%Y-%mSELECT DATE_FORMAT(order_time, %Y-%m) AS month, SUM(amount) AS total_amount FROM orders WHERE order_time BETWEEN 2024-01-01 00:00:00 AND 2024-12-31 23:59:59 GROUP BY DATE_FORMAT(order_time, %Y-%m) ORDER BY month;有人会在 SELECT 里写DATE_FORMAT、GROUP BY 里再写一遍DATE_FORMAT觉得很啰嗦。MySQL 允许 GROUP BY 使用 SELECT 中的别名所以可以简化成GROUP BY month。但这里有个版本差异MySQL 5.7.5 之前对 GROUP BY 别名的解析存在歧义5.7.5 之后默认开启了ONLY_FULL_GROUP_BY用别名反而更安全。不过 ORDER BY 使用别名一直没问题。我的建议是小查询怎么顺手怎么来生产环境的大查询还是把完整表达式写清楚避免执行计划变化时踩坑。3.2 导出文件名与流水号生成做后台系统时经常需要导出数据文件名要带上时间戳避免同名覆盖。常见需求是生成流水_20240520_143045.csv这种格式。用 DATE_FORMAT 一行搞定SELECT CONCAT(流水_, DATE_FORMAT(NOW(), %Y%m%d_%H%i%s), .csv);这里没写-和:是因为 Windows 文件系统不允许文件名里出现冒号Linux 虽然允许但容易在传输时出问题。所以我通常用%Y%m%d和%H%i%s这种紧凑格式。如果希望带毫秒可以把%s换成%f。另一个常见需求是生成短码形式的业务流水号比如把2024-05-20 14:30:45转成20240520143045SELECT DATE_FORMAT(NOW(), %Y%m%d%H%i%s);注意%H是 24 小时制如果是凌晨 3 点%h会输出03两者看起来一样但下午 3 点就不同了。流水号如果混用 12/24 小时制会出现“上午 3 点 下午 3 点”的重复编号风险所以统一用%H。3.3 在应用与数据库之间选谁来做格式化这个问题几乎每个团队都会吵。站在数据库端DATE_FORMAT 是内置函数新增一列格式化结果不会带来额外的网络开销报表查询直接返回展示层可用的字符串。很多 BI 工具查完 MySQL 后直接渲染图表如果库里返回的是原始时间戳图表工具的日期解析能力又参差不齐不如库里一次格式化到位。站在应用端格式化逻辑更灵活而且 Java、Python、Go 的日期库在时区处理和国际化方面远强于 MySQL 内置的英文文本格式符。如果你面向多国用户%W输出Monday没问题但想输出понедельник就得靠应用层。我的经验是数据库负责粗粒度时间计算和聚合应用层负责最终展示文案。比如按天统计这种必须发生在数据库端的操作DATE_FORMAT 当仁不让而报表表格里“星期四 14:30”这种展示性文案让应用层去做本地化更合理。不要一把梭让数据库干所有的活。4. 日期格式化的隐藏陷阱NULL、隐式转换与性能代价4.1 DATE_FORMAT 遇上 NULL 的静默消失DATE_FORMAT(NULL, %Y-%m-%d) 返回 NULL不是空字符串也不是0000-00-00。这在统计报表里特别容易造成“某一天数据神秘消失”的现象。比如你想统计本月每天的用户注册数SELECT DATE_FORMET(register_time, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM users WHERE register_time 2024-05-01 GROUP BY day;只要某个用户register_time是 NULL这一行在 GROUP BY 时不会消失NULL 会单独成组但如果你在 COUNT 里只数了非空记录那 NULL 组显示为 0很容易被忽略。更好的是在聚合前显式处理SELECT COALESCE(DATE_FORMAT(register_time, %Y-%m-%d), 未知) AS day, COUNT(*) AS cnt FROM users ...还有一个关联问题是DATE_FORMAT可能收到非法日期。MySQL 在非严格模式下会把2024-02-30这种日期转成0000-00-00而DATE_FORMAT(0000-00-00, %Y-%m-%d)返回 NULL。排查数据质量问题时如果你看到报表里突然缺了某天先查源数据是不是有脏日期。4.2 格式化后再比较索引失效的经典写法这是我最想强调的一个坑。很多人想查某一天的数据习惯性写成SELECT * FROM orders WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20;逻辑上完全正确执行效率上却极其糟糕。因为created_at上如果有索引MySQL 无法对DATE_FORMAT(created_at, ...)这个表达式使用索引查找只能全表扫一遍对每一行的created_at做格式化再跟字符串比较。表一旦上了百万行这个查询就是灾难。正确写法是用范围条件SELECT * FROM orders WHERE created_at 2024-05-20 00:00:00 AND created_at 2024-05-21 00:00:00;这个写法的好处是第一走了索引性能数量级的提升第二即使created_at是 DATETIME 带小数秒范围条件也能精确覆盖第三可读性也不差。我的习惯是只要是想筛“某一天”的数据一律写范围条件绝不写DATE_FORMAT比较。如果你非要在比较时用 DATE_FORMAT至少把等号换成BETWEEN粒度更细的写法或者考虑在新建列上建函数索引。MySQL 8.0.13 以后支持函数索引可以直接CREATE INDEX idx_date ON orders ((DATE_FORMAT(created_at, %Y-%m-%d)));但说实话日常业务里没必要为一个格式化条件专门建索引把查询写法改对就好了。4.3 隐式转换的连环雷从字符串到日期再到字符串DATE_FORMAT 的第一个参数虽然叫 date但 MySQL 允许传字符串比如DATE_FORMAT(2024-05-20 14:30:45, %H)会正常返回14。这个“宽容”特性会导致一个隐蔽的问题你传入的字符串如果格式不规范MySQL 不会报错而是给出一个诡异的结果。举个例子SELECT DATE_FORMAT(2024/05/20, %Y-%m-%d); -- 结果2024-05-20MySQL 自动把斜杠识别成日期分隔符 SELECT DATE_FORMAT(20240520, %Y-%m-%d); -- 结果2024-05-20还是 NULL -- 实测是 2024-05-20MySQL 能解析纯数字串但依赖版本和 sql_mode问题在于这种隐式转换的规则不是标准的它跟sql_mode、服务端版本都有关系。我在 MySQL 5.7 上测过DATE_FORMAT(20240520, %Y-%m-%d)能正常输出但到了 8.0 某些版本就返回 NULL 或者给你一个把20240520当数字计算的结果。所以我的建议是不要依赖 DATE_FORMAT 去猜你的业务字符串是什么格式先把字符串用 STR_TO_DATE 显式转成日期再传给 DATE_FORMAT。SELECT DATE_FORMAT(STR_TO_DATE(20240520, %Y%m%d), %Y-%m-%d);这看起来多了一步但每一步都确定不会因为版本迁移而爆炸。4.4 格式化本身的性能代价DATE_FORMAT 是逐行调用。一张千万级流水表你 SELECT DATE_FORMAT(created_at, ...) 出来MySQL 会对每一行执行一次日期格式化。这比直接输出原始 DATETIME 字段要慢而且慢得不少。我之前做过一个简单测试MySQL 8.0.28单表 500 万行查询方式平均耗时SELECT created_at FROM big_table LIMIT 100000约 80msSELECT DATE_FORMAT(created_at, %Y-%m-%d) FROM big_table LIMIT 100000约 220ms在有 WHERE 条件过滤后再格式化的场景下差距会小一些因为参与格式化的行数少了。所以一个基础优化原则是先 WHERE 后 SELECT先缩小结果集再格式化。不要在子查询里对全表做 DATE_FORMAT然后外层再过滤那等于白干。还有一个藏在 GROUP BY 里的性能细节GROUP BY DATE_FORMAT(created_at, %Y-%m-%d)需要对格式化结果做分组内存临时表的使用量会上升因为分组键是变长字符串而不是紧凑的日期。如果数据量巨大可以考虑先把日期截断到天再存汇总表或者用CAST(created_at AS DATE)做分组。CAST(created_at AS DATE)的语义是取日期部分它跟DATE_FORMAT(created_at, %Y-%m-%d)结果相似但底层的实现更直接分组开销也小一些。不过返回类型一个是 DATE、一个是 VARCHAR如果你只需要“某一天”这种粗粒度CAST AS DATE往往是更轻的选择。5. 进阶玩法动态格式串与日期函数的协同使用5.1 用一个变量控制统计粒度报表系统经常遇到“同一条查询按日、按月、按小时切换粒度”的需求。与其为每个粒度写一条 SQL不如把格式串变成动态参数。SET fmt %Y-%m-%d; -- 动态传给查询 SELECT DATE_FORMAT(created_at, fmt) AS period, COUNT(*) AS cnt FROM payment_log WHERE created_at 2024-01-01 GROUP BY DATE_FORMAT(created_at, fmt);在 Java 里可以通过 PreparedStatement 把格式串作为参数传入这样一条统计接口就能支持日/周/月/小时多种维度。格式串集可以定义成一组常量日%Y-%m-%d、周%x-%v、月%Y-%m、小时%Y-%m-%d %H。这里需要小心周维度的格式串。%x-%v是 ISO 周周一起算的标准组合输出如2024-20代表 2024 年第 20 周。如果你用%Y-%U虽然也能输出年份和周数但%U是周日起算跟中国习惯的周一起算不一致统计结果会整体错位一天。这个坑在跨周统计时最明显——周日的数据会被算到上一周。5.2 跟 DATE_ADD、LAST_DAY 搭配做自然月统计DATE_FORMAT 经常要配合日期运算函数一起用。比如统计“上个月的每一天”的销售量你不想写死月份可以用 LAST_DAY 定位上个月的最后一天再往前推一个月SELECT DATE_FORMAT(day, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM sales WHERE day DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), %Y-%m-01) AND day DATE_FORMAT(CURRENT_DATE(), %Y-%m-01) GROUP BY DATE_FORMAT(day, %Y-%m-%d);这里的思路是先用DATE_FORMAT(CURRENT_DATE(), %Y-%m-01)生成本月 1 号然后用DATE_SUB减去一个月就得到上月 1 号。这样不管今天是几号、上个月是 30 天还是 31 天区间都是准确的。另一个常见组合是“本月累计”和“本月剩余天数”SELECT DATEDIFF(LAST_DAY(CURRENT_DATE()), CURRENT_DATE()) AS remaining_days, DATE_FORMAT(LAST_DAY(CURRENT_DATE()), %Y-%m-%d) AS month_end;5.3 在存储过程与触发器里生成可读日志时间如果你在写存储过程或触发器DATE_FORMAT 通常是用来拼接日志内容或者生成事件描述字段的。比如给订单表建一个审计触发器把变更时间转成易读格式写进日志表CREATE TRIGGER trg_order_audit AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO order_audit(order_id, event_desc, event_time) VALUES ( NEW.order_id, CONCAT(新订单创建于 , DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)), NOW() ); END;注意这里的一个关键抉择日志表里event_time字段存什么类型很多人会直接存DATE_FORMAT产生的字符串省事但后续如果想做时间区间查询、排序、聚合字符串会非常痛苦。我的建议是原始时间值DATETIME/TIMESTAMP保留在独立字段DATE_FORMAT 的产物只用于展示或者冗余的描述字段。永远不要把格式化后的字符串当作主时间字段去查询否则你会掉进“为了格式化而格式化”的怪圈。5.4 动态跨年周数的坑千万不要用 %Y 配合 %v最后单独把跨周年份这个坑拎出来讲。在周维度统计中%v返回第几周1-53但它表示“哪一年”的周数时必须配合%x而不是%Y。典型的错误发生在 2024 年 12 月 30 日——这天是周一属于 ISO 周 2025 年的第 1 周。如果你用DATE_FORMAT(date, %Y-%v)会输出2024-01但事实上这周的“归属年”是 2025。正确写法是%x-%v输出2025-01。SELECT DATE_FORMAT(2024-12-30, %Y-%v) AS wrong_result, -- 2024-01 DATE_FORMAT(2024-12-30, %x-%v) AS correct_result; -- 2025-01%x是周一周算的年份%X是周日周算的年份。如果你公司财务的“周”定义为周日开始就要用%X-%V跟用%x-%v的结果会差上几天。这里没有绝对的对错关键是跟业务定义保持一致并且在代码注释里写清楚“这里用 ISO 周”还是“这里用自然周”。我见过因为这个分歧两个团队各执一词吵到领导层去的。数据库层面不解决业务定义问题但至少你要知道自己用的是哪套规则。6. 我在实际维护中总结的几条铁律写了这么多最后把我这些年实际踩坑后总结的几条操作纪律分享给你它们不复杂但能挡掉绝大多数 DATE_FORMAT 相关的线上事故。第一条筛选永远用范围条件不用格式化后的等值比对。这是性价比最高的一条。把DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-20改成区间查询后查询性能和数据准确性同时提升。别贪图写法简单简单不等于正确。第二条分钟是 %i月份是 %m时刻区分 %h 和 %H。这三个大小写/字母差异是最容易写错的。我建议你在代码仓库的 SQL 规范文档里放一份常用格式符对照表新人上手先看表而不是靠记忆。肉眼 review 不出来拼写错误只有测试能兜住。第三条格式串里出现中文时先确认展示层需求。如果只是内部系统的临时报表库里格式化成中文没问题。如果是多端共存的产品把本地化格式化的活留给应用层数据库只管聚合展示层管文案各司其职。第四条需要保存时间时存原始类型格式化字符串可以做冗余字段但绝不能成为查询条件。这个前面说过再强调一次是因为我见过真的有人把订单表的付款时间字段设成 VARCHAR里面存2024-05-20 14:30:45然后每次统计都靠 STR_TO_DATE 转换。一个本可以用索引的范围查询硬生生变成全表扫描。时间字段就该用 DATE、DATETIME、TIMESTAMP 类型。第五条对于周、季度这类非自然粒度先确认业务口径再写格式串。周一为一周之首还是周日为一周之首跨年那周算哪一年的这些问题不是 SQL 能替你决定的。写代码之前先跟业务方对齐然后把口径注释在 SQL 旁边。等线上统计出来的数字引发了业务争执再来解释“啊这个 %U 是周日口径”就已经晚了。我还想提醒一个很容易忽略的小细节DATE_FORMAT 返回字符串所以当你对格式化结果做 ORDER BY 时排序规则是字符串字典序。%Y-%m-%d格式下字典序正好等同于时间顺序这是个巧合但如果你用了%Y-%c-%e这种不带前导零的格式排序就会乱掉。所以在按时间排序时优先使用带前导零的格式串或干脆用原始日期字段排序。DATE_FORMAT 是个小函数但它连接着业务展示、聚合统计和查询性能三个层面。把它的格式符体系、边界行为和搭配技巧吃透能省下很多排查数据问题的时间。希望这篇能帮你把日期格式化的基本功打得扎实一点下次再看到那个%y-%m-%e的写法时你会第一时间知道问题出在哪。
