1. 为什么 DATE_FORMAT() 几乎天天都要用做 MySQL 开发的同行应该都有体会业务方丢过来的报表需求十有八九长这样“把订单表按天统计一下”“这个月的成交额跟上周对比一下”“运营要看每个小时的注册趋势”。如果表里的时间字段是datetime类型直接GROUP BY create_time会按“年月日时分秒”全量分组一分钟一个组根本没法看。这时候第一个想到的函数基本就是DATE_FORMAT()它能将日期时间值重新组装成任意你想要的字符串格式按天、按月、按小时、按季度聚合都比喝凉水还简单。但这只是最浅的一层价值。在实际项目里DATE_FORMAT() 的使用场景要宽得多。比如接口返回给前端的时间格式要求去掉时分秒比如导出 Excel 时日期列必须显示成“2024-07-01”而不是“2024-07-01 14:23:05”再比如做月度账单对账时需要把datetime字段归一化成“2024-07”这种月份键去关联其他表。这些看起来琐碎却能省下大量应用层代码的活儿DATE_FORMAT() 都能在 SQL 层面直接解决。我最早接触这个函数是帮运营临时拉一份“按小时维度看促销活动效果”的数据当时写了三行代码用 Java 循环去格式化时间再分组跑了几百万行数据慢得被业务方一直催。后来一个老同事瞄了一眼直接扔了一行 SQL 过来SELECT DATE_FORMAT(create_time, %Y-%m-%d %H:00:00) AS hour_key, COUNT(*) FROM orders WHERE create_time 2024-06-01 GROUP BY hour_key;就是加了一个 DATE_FORMAT()整个任务从“跑几分钟还不一定完”变成了“秒出结果”。那是我第一次真正意识到SQL 里能解决的事情不要拿到应用层去做否则你不仅要写更多代码还要承受传输和计算带来的额外开销。从那以后 DATE_FORMAT() 就成了我工具箱里的常客。需要说明的是网上很多教程把 DATE_FORMAT() 简单等同于“格式化日期给用户看”这其实是把它的能力用窄了。它最核心的生产力在于把连续的时间轴上的一段区间折叠成一个离散的业务周期。按小时、按天、按周、按月、按季度只要你把格式符写对分组、聚合、关联都能用同一套逻辑复用。这也是为什么它在我做报表类项目、数据分析类项目时出现频率极高。2. DATE_FORMAT() 的基础用法与格式符全解2.1 函数语法与第一个 Hello WorldDATE_FORMAT() 的语法非常简单DATE_FORMAT(date, format)第一个参数是日期时间表达式可以是datetime字段、date字段也可以是一个合法的日期字符串比如2024-07-01、20240701还可以是NOW()、CURDATE()这样的日期函数。第二个参数则是格式符字符串决定输出结果的形状。一个最基础的例子SELECT DATE_FORMAT(2024-07-01 14:23:05, %Y-%m-%d) AS day_key; -- 输出: 2024-07-01再试一下格式化成中文习惯的带时分秒形式SELECT DATE_FORMAT(2024-07-01 14:23:05, %Y年%m月%d日 %H时%i分%s秒) AS formatted; -- 输出: 2024年07月01日 14时23分05秒看到格式符的威力了吧%Y是四位年份%m是两位月份%d是两位日期%H是24小时制的小时%i是分钟%s是秒。你可以在格式串里穿插任意分隔符包括中文、横杠、斜杠、冒号MySQL 会原样保留。用生活化的类比来理解formatted就是一个打印模板date是被打印的原材料格式符就是模板上的占位符。你把布料剪成什么形状缝出来就是什么衣服——DATE_FORMAT() 就是把时间这块“布料”按照你的模板刻出指定的形状。2.2 核心格式符对照表很多人用 DATE_FORMAT() 时容易记混格式符尤其%i是分钟而不是%m%H和%h就差大小写但含义不同。我整理了一份工作中最高频使用的对照表格式符含义输出示例以 2024-07-01 14:23:05 为例%Y四位年份2024%y两位年份24%m两位月份01-1207%c月份1-12无前导零7%d两位日期01-3101%e日期1-31无前导零1%H24小时制小时00-2314%h12小时制小时01-1202%i分钟00-5923%s秒00-5905%W星期名全称Monday%a星期名缩写Mon%w一周中的第几天0周日1%j一年中的第几天001-366183%U一年中的第几周周日为每周第一天26%u一年中的第几周周一为每周第一天27%pAM 或 PMPM%T时间24小时制hh:mm:ss14:23:05%r时间12小时制hh:mm:ss AM/PM02:23:05 PM%M月份全名July%b月份缩写Jul这里重点记两个容易踩坑的点%i是分钟不是月份。月份用小写%m。这是初学者最容易搞反的一对。大小写%H和%h结果完全不同%H是 00-23 的24小时制%h是 01-12 的12小时制。如果你在报表里把下午 14 点显示成 02 点先看看是不是用了小写%h。2.3 没有前导零的场景怎么处理有些格式符自带前导零比如%m输出07、%e输出7。这在排序时如果不注意会出问题——如果按字符串排序%e输出的格式会导致“10”排在“2”前面。所以当你把 DATE_FORMAT() 的结果用于排序或分组时优先使用带前导零的格式符保证字典序和时间顺序一致。举个例子下面这条 SQL 的输出顺序就不符合直觉SELECT DATE_FORMAT(create_time, %e日) AS day, COUNT(*) FROM orders WHERE create_time 2024-07-01 GROUP BY day ORDER BY day;因为day是字符串按字典序排出来是1日、10日、11日、…、19日、2日、20日……。改成%d就能避免这个坑SELECT DATE_FORMAT(create_time, %d日) AS day, COUNT(*) FROM orders WHERE create_time 2024-07-01 GROUP BY day ORDER BY day;这类细节在数据量小的时候无所谓一旦写进定时报表或者数据看板发现的人一定会回来吐槽你。3. 按天 / 按月 / 按小时聚合格式符在分组统计中的实战3.1 按天统计与 KV 拆解最经典的需求是“按天统计订单量”。假设订单表orders里有create_time字段类型datetime一条 SQL 搞定SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day_key, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time 2024-06-01 AND create_time 2024-07-01 GROUP BY day_key ORDER BY day_key;这条 SQL 做了三件事先把每个时间戳折叠成“天”级别的字符串再进行分组聚合最后按天排序。要注意WHERE条件里用的是create_time 2024-06-01 AND create_time 2024-07-01这比BETWEEN 2024-06-01 AND 2024-06-30 23:59:59更安全后者容易漏掉 23:59:59.999 这种边界情况。关于别名能不能用于 GROUP BY的问题MySQL 在这点上比较宽松GROUP BY day_key可以直接引用SELECT里的别名。其他数据库比如 SQL Server 和 Oracle 不一定允许但如果你主用 MySQL这个习惯可以保留。3.2 按小时聚合促销大屏背后的 SQL运营看板里最常要的“按小时看流量趋势”格式符同样好使SELECT DATE_FORMAT(create_time, %Y-%m-%d %H:00:00) AS hour_key, COUNT(*) AS pv, COUNT(DISTINCT user_id) AS uv FROM visit_log WHERE create_time 2024-07-01 00:00:00 AND create_time 2024-07-02 00:00:00 GROUP BY hour_key;这里的技巧是把小时格式化成2024-07-01 14:00:00这种“整点键”既保留了时间顺序又把 14:23:05、14:45:12 这些乱七八糟的分钟级时间全部折叠到 14 点这个桶里。按小时聚合时我还习惯在GROUP BY里直接用DATE_FORMAT(create_time, %Y-%m-%d %H)因为 MySQL 对GROUP BY后面用表达式是支持的但为了可读性还是建议放在SELECT里取名后在GROUP BY引用。这个场景有一个性能注意点如果visit_log的数据量在千万级以上DATE_FORMAT()在SELECT里做一次函数计算还好但如果写进GROUP BY并在WHERE中对时间列做函数包裹比如写成WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-07-01索引就废了。后面我会专门讲性能优化这里先不展开。3.3 按周聚合与跨周归属问题按周统计是最容易出 bug 的聚合之一因为不同业务对“一周从哪天开始”的定义不一致。DATE_FORMAT() 提供了%U周日为一周起点和%u周一为一周起点两个格式符使用时一定要先确认业务口径。-- 周一对账报表 SELECT DATE_FORMAT(create_time, %x-W%u) AS week_key, SUM(amount) AS weekly_amount FROM orders WHERE create_time 2024-01-01 GROUP BY week_key ORDER BY week_key;这里的%x是四位年份周所在的年份配合%u使用。如果你只用%u而不用%x跨年时会出现“2024年第1周”和“2025年第1周”都是“第1周”的混乱。比如 2024 年 12 月 30 日属于 2025 年的第 1 周如果你只输出01排序和分组都会错位。3.4 按月聚合与月初对齐按月聚合最简单也是我见人用得最多的SELECT DATE_FORMAT(create_time, %Y-%m) AS month_key, SUM(amount) AS month_amount FROM orders WHERE create_time 2024-01-01 AND create_time 2025-01-01 GROUP BY month_key;输出结果是2024-01、2024-02这种月份键。用这种格式做关联特别方便比如订单表和回款表都是datetime字段要按月份关联两边都先DATE_FORMAT(..., %Y-%m)出一个 month_key再 JOIN就能把“当月下单金额”和“当月回款金额”并排放到一行里这是做月度经营看板非常常用的写法。4. 字符串拼接、排序与条件过滤DATE_FORMAT() 的高级玩法4.1 日期格式化后用于字符串拼接DATE_FORMAT() 返回的是字符串所以在需要拼接、比较、拼接别名时都很好用。比如生成导出文件的文件名前缀SELECT CONCAT(order_, DATE_FORMAT(NOW(), %Y%m%d_%H%i%s), .csv) AS file_name; -- 输出: order_20240701_142305.csv比如把时间格式化后放进接口返回的 JSON 里不用在 Java 端再写一遍SimpleDateFormatSELECT id, DATE_FORMAT(create_time, %Y-%m-%d %H:%i) AS create_time_str FROM orders WHERE id 12345;这样应用层直接取字符串省掉一层转换。不过要提醒一句格式化后的时间字符串在传输层失去了时间语义如果前端需要做时间比较或计算时区偏移建议不要这么做保持datetime原值返回更稳妥。如果是给电子表格导数据、给运营肉眼看的场景提前格式化又香又省事。4.2 用 DATE_FORMAT() 做日期的相等判断有时你需要查“某一天的所有订单”如果字段是datetime直接WHERE create_time 2024-07-01是查不到数据的因为create_time几乎不可能是2024-07-01 00:00:00。常见的两种写法-- 写法一范围查询推荐可走索引 WHERE create_time 2024-07-01 AND create_time 2024-07-02 -- 写法二格式化后相等判断不推荐不走索引 WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-07-01写法二看起来简洁但对create_time套了函数MySQL 无法使用该字段的索引数据量一大就会全表扫描。数据量在万级以下无所谓百万级以上就有明显体感差异。能用范围查询就尽量用范围查询这也是老生常谈的索引经验了。4.3 在 ORDER BY 中使用 DATE_FORMAT() 的注意事项ORDER BY 后面直接写 DATE_FORMAT() 其实不太常见因为日期时间字段本身就可以排序。但有时你确实需要“按格式化后的值排序”比如按%y两位年份排让 2023 排在 2024 后面或者按%w星期几的数字排实现“周一到周日”的业务顺序。这种需求一般出现在排班表和教室课表之类的地方。这里我要给出一个反直觉的提醒在 ORDER BY 里用 DATE_FORMAT() 通常不是性能问题但可能是逻辑错误。比如SELECT user_id, DATE_FORMAT(register_time, %m) AS reg_month FROM users ORDER BY reg_month;这个结果按“月份字符串”排序10会排在2前面。要按“一月到十二月”的真实月份顺序应该用数值类型排序或者用%c再把结果转成数字。如果你非要用格式符排序建议排序列保持%Y-%m-%d这种“从大到小”的结构字典序也正好等于时间序。5. 性能与索引为什么有人用 DATE_FORMAT() 把 SQL 写废了5.1 函数包裹索引列导致索引失效这是 DATE_FORMAT() 使用的最大坑没有之一。如果你在WHERE条件中对索引列套了 DATE_FORMAT()MySQL 的优化器基本无法利用索引进行范围扫描。比如SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-07-01;这条 SQL 的create_time即使建了索引也走不上因为 MySQL 要对每一行的create_time先做格式化再比较索引里的有序结构帮不上忙。你观察执行计划的话大概率看到type: ALL也就是全表扫描。正确写法是把条件改成范围SELECT * FROM orders WHERE create_time 2024-07-01 AND create_time 2024-07-02;如果业务上确实经常按“天”查询与其在WHERE里用函数不如考虑在表里冗余一个dt字段date类型插入时由程序写入当天日期再给dt建索引。空间换性能在大表场景下往往是最稳妥的做法。5.2 DATE_FORMAT() 在 SELECT 和 GROUP BY 中的性能影响很多人在SELECT和GROUP BY里用 DATE_FORMAT() 时担心性能其实这里的关键不在于“函数贵不贵”而在于你要处理多少行、结果要返回多少行。如果查询先在WHERE阶段用时间范围过滤掉绝大部分数据比如只留下最近一天的数据再对这少量行做 DATE_FORMAT()性能完全可以接受。反之如果全表几千万行都进入SELECT和GROUP BY阶段即使每次格式化只要几微秒累加起来也是秒级甚至分钟级的开销。我遇到过一个真实案例某个报表 SQL 对一张 5000 万行的流水表做按月统计SQL 写成了SELECT DATE_FORMAT(create_time, %Y-%m) AS month_key, SUM(amount) FROM transactions GROUP BY month_key;没有时间过滤优化器直接对全表做聚合。执行时间接近 6 分钟。后来在WHERE加了最近 36 个月的范围条件执行时间降到 10 秒以内。所以优化 DATE_FORMAT() 的第一步不是换函数而是尽量在 WHERE 阶段缩小数据范围。5.3 替代方案对比DATE()、DATE_FORMAT() 与范围查询针对“按天/按月分组”这个需求除了 DATE_FORMAT()还有几个常见替代方案方案示例可走索引优缺点范围查询 GROUP BY 原字段WHERE create_time ? AND create_time ?是性能最优但要在应用层计算边界值DATE_FORMAT()GROUP BY DATE_FORMAT(create_time, %Y-%m)否表达直观灵活度高数据量大时性能一般DATE() 函数GROUP BY DATE(create_time)否语法简洁但只能精确到天无法组装自定义格式冗余日期列GROUP BY dt是性能最好但需要写入端配合维护冗余字段生成列 索引ALTER TABLE ... ADD COLUMN month_key ... GENERATED ALWAYS AS ...是MySQL 5.7 支持兼顾灵活性和性能但改动 DDL 成本高我个人在中等数据量千万以下场景直接用 DATE_FORMAT() 最舒服省代码、可读性强。但在大数据量统计场景会优先选择“范围查询 冗余日期列/生成列 索引”的组合把性能大头交给索引把格式化留给少量数据。6. 真实项目中的坑与解决经验边界条件、时区、NULL 与前后端一致性6.1 时区带来的“日子不对”问题使用 DATE_FORMAT() 显示本地日期时很多人忽略了一个细节MySQL 返回的时间是会话时区下的时间。如果你的应用服务器和 MySQL 服务器的时区不一致那么NOW()和CURRENT_TIMESTAMP的结果会偏最终 DATE_FORMAT() 出来的“今天”可能不是业务上的今天。比较典型的场景是服务器用 UTC应用代码用北京时间orders.create_time存的是北京时间还是 UTC 时间取决于写入时用的连接时区。如果你在 MySQL 客户端里执行SELECT NOW()发现跟服务器本地时间差 8 小时就要检查time_zone设置-- 查看当前时区 SELECT global.time_zone, session.time_zone;处理方式有两种统一连接时区或在 SQL 里显式转换。显式转换的话可以用CONVERT_TZ()SELECT DATE_FORMAT(CONVERT_TZ(create_time, 00:00, 08:00), %Y-%m-%d) AS beijing_day FROM orders;不过我的建议是从架构层面统一约定数据库连接串里加serverTimezoneAsia/ShanghaiJDBC或connectionTimeZone08:00MySQL Connector/J 8.x让应用写入和查询都在固定时区下进行。SQL 里到处转时区看着酷维护起来只想哭。6.2 NULL 与非法日期的处理DATE_FORMAT() 的入参如果是NULL返回结果也是NULL。这在GROUP BY分组时会形成一组NULL的行。很多人在报表里没注意这个导致结果里出现一个“无日期”分组看着莫名其妙。遇到这种情况要么在源头上避免NULL时间字段要么在 SQL 里显式处理SELECT DATE_FORMAT(COALESCE(create_time, 1970-01-01), %Y-%m) AS month_key, COUNT(*) FROM orders GROUP BY COALESCE(create_time, 1970-01-01);或者干脆过滤掉 NULLSELECT DATE_FORMAT(create_time, %Y-%m) AS month_key, COUNT(*) FROM orders WHERE create_time IS NOT NULL GROUP BY month_key;另外如果你传入一个明显非法的日期字符串比如2024-13-45MySQL 的行为在不同版本和不同模式下有差异。严格模式下会报错或返回 NULL非严格模式下可能返回 NULL 或产生怪异结果。所以我建议在数据仓库抽取层、ETL 层就把脏数据清洗掉不要指望 DATE_FORMAT() 帮你兜底。6.3 前后端格式一致性从源头避免“时间显示差 8 小时”这个坑我已经见过不下五次后端把datetime以 ISO 格式返回给前端前端用new Date(2024-07-01T14:23:05)解析时默认按浏览器本地时区解析如果用户在北京就显示14:23如果用户在伦敦就显示07:23。然后用户投诉说“系统时间错误”。这类问题的根源不是 DATE_FORMAT()而是后端返回了不带时区的 ISO 字符串。解决方案有几个后端统一返回时间戳毫秒或秒前端new Date(timestamp)自己处理显示。后端返回YYYY-MM-DD HH:mm:ss这种不带T的字符串前端不要new Date()直接解析而是按字符串显示。后端用 DATE_FORMAT() 将时间格式化成yyyy-MM-dd HH:mm:ss给导出 Excel 或 PDF 报表的场景因为报表通常不带交互时区这种格式最不会出错。我在项目里的习惯是API 交互用 ISO 标准格式或纯时间戳报表导出、Excel 下载、邮件推送这些生成后就不需要再做时区计算的场景直接 DATE_FORMAT() 成纯字符串。这样两边都能对得上。如果你把报表数据也从接口返回建议约定好统一字符串格式避免前端再用Date对象去解析。7. 与其他日期函数组合的使用心得7.1 配合 DATE_ADD() 生成连续的日期序列生成一张“过去 30 天每天的订单量”报表时最怕的是“没有订单的那天没有记录”导致折线图缺洞。传统写法是GROUP BY day没有数据的天就不会出现在结果集里。要补全连续日期序列可以借助DATE_ADD()和递归/数字表WITH RECURSIVE date_range AS ( SELECT DATE_SUB(CURDATE(), INTERVAL 29 DAY) AS dt UNION ALL SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_range WHERE dt CURDATE() ) SELECT DATE_FORMAT(dr.dt, %Y-%m-%d) AS day_key, COALESCE(SUM(o.amount), 0) AS day_amount FROM date_range dr LEFT JOIN orders o ON DATE_FORMAT(o.create_time, %Y-%m-%d) DATE_FORMAT(dr.dt, %Y-%m-%d) WHERE o.create_time DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND o.create_time DATE_ADD(CURDATE(), INTERVAL 1 DAY) GROUP BY dr.dt ORDER BY dr.dt;注意左表dr.dt是date类型o.create_time是datetime类型直接等值比较会匹配不上所以这里两边都做 DATE_FORMAT() 到天再关联。这种写法在数据量小时挺好用但性能一般。如果数据量大更优的方案是先把dr.dt转成datetime范围再关联LEFT JOIN orders o ON o.create_time dr.dt AND o.create_time DATE_ADD(dr.dt, INTERVAL 1 DAY)这个版本不仅结果一致而且能走create_time索引在百万级数据上优势明显。记住DATE_FORMAT() 解决的是“显示/分组”的问题范围条件解决的是“查询/关联”的问题两者分工明确。7.2 与 DATEDIFF()、TIMESTAMPDIFF() 组合实现周同比月同比在做报表时“本周 vs 上周”“本月 vs 上月”是高频需求。有人喜欢在代码里计算时间点再传参但我更愿意在 SQL 里统一处理保证口径一致SELECT DATE_FORMAT(create_time, %Y-%m) AS month_key, SUM(amount) AS current_month_amount, SUM(CASE WHEN create_time DATE_SUB(DATE_FORMAT(NOW(), %Y-%m-01), INTERVAL 1 MONTH) AND create_time DATE_FORMAT(NOW(), %Y-%m-01) THEN amount ELSE 0 END) AS last_month_amount FROM orders WHERE create_time DATE_SUB(DATE_FORMAT(NOW(), %Y-%m-01), INTERVAL 2 MONTH) AND create_time DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-01), INTERVAL 1 MONTH) GROUP BY month_key;这里用DATE_FORMAT(NOW(), %Y-%m-01)巧妙地拿到“本月第一天”再配合DATE_SUB()拿到“上个月同期”比手动拼字符串更健壮。有些同事喜欢在应用层算时间但每次业务口径一变就要发版SQL 统一收口则只需改一条语句省事太多。7.3 在存储过程中使用 DATE_FORMAT() 做定时报表落表很多系统的定时任务会用 MySQL 存储过程生成报表数据。DATE_FORMAT() 在里面扮演的角色通常是“生成报表唯一键”保证同一天同一指标只留一条CREATE PROCEDURE sp_generate_daily_report() BEGIN DECLARE v_date VARCHAR(10); SET v_date DATE_FORMAT(NOW(), %Y-%m-%d); DELETE FROM daily_report_summary WHERE stat_date v_date; INSERT INTO daily_report_summary (stat_date, order_cnt, amount_sum) SELECT DATE_FORMAT(create_time, %Y-%m-%d), COUNT(*), SUM(amount) FROM orders WHERE create_time CONCAT(v_date, 00:00:00) AND create_time DATE_ADD(CONCAT(v_date, 00:00:00), INTERVAL 1 DAY) GROUP BY DATE_FORMAT(create_time, %Y-%m-%d); END;这种模式的好处是报表表daily_report_summary的stat_date是唯一键重复跑任务不会攒出脏数据。v_date变量用 DATE_FORMAT() 生成为2024-07-01这种字符串再拼接成带时分秒的查询条件简洁清晰还避免了函数套索引列的问题因为范围条件可以直接落到create_time索引上。8. 迁移兼容性与版本差异别让开发环境把坑留到生产8.1 MySQL 5.7 vs 8.0 的行为差异DATE_FORMAT() 在 MySQL 5.7 和 8.0 之间的总体差异不大但有几个细节需要注意MySQL 8.0 对非法日期的容忍度更低。如果你传2024-02-30这种不存在的日期在严格模式下会直接报错。5.7 在非严格模式下可能返回 NULL 或者一个诡异的日期。8.0 的%U、%u周数与 5.7 的结果一致性没问题但如果你同时用了%x年份和%v周要注意%v是周表的“周编号”配合%x使用时必须保证%x的值来自同一周否则结果不准确。一个我在升级中遇到的实例公司系统从 5.7 升级到 8.0 后有一条历史 SQL 用DATE_FORMAT(create_time, %Y-%m-%d) 2024-02-30查数据原本在 5.7 下返回 NULL 集合在 8.0 严格模式下直接抛了异常。排查半天才找到原因。这类兼容性坑虽然不常见但在版本升级排查时值得记住如果一个查询在 5.7 能跑、8.0 报错先检查涉及日期的函数和非法日期输入。8.2 与 MariaDB 的兼容性小抄MariaDB 也支持 DATE_FORMAT()大部分行为与 MySQL 相同毕竟血统同源。但 MariaDB 在%U/%u以及某些日期时间类型上的细微差异偶有出现。如果你的代码要在两套库间迁移建议写个简单的回归测试把常用格式符逐一输出对比一下。比如跑一条SELECT DATE_FORMAT(2024-01-07, %Y-%m-%d) AS a, DATE_FORMAT(2024-01-07, %U) AS week_sun, DATE_FORMAT(2024-01-07, %u) AS week_mon;在 MySQL 和 MariaDB 各跑一遍看结果是否一致。实测下来大部分一致但比 5.7 更早的 MariaDB 10.2 里%U的行为可能与预期稍有偏差。跨库迁移不留心生产环境早晚给你上一课。8.3 不同数据库的替代写法参考如果你哪天从 MySQL 迁去别的数据库DATE_FORMAT() 的写法需要对应替换。我整理了一张常用日期格式化在各数据库的对照表场景MySQLPostgreSQLSQL ServerOracle日期转字符串DATE_FORMAT(d, %Y-%m-%d)TO_CHAR(d, YYYY-MM-DD)FORMAT(d, yyyy-MM-dd) 或 CONVERT(varchar, d, 23)TO_CHAR(d, YYYY-MM-DD)时间转字符串DATE_FORMAT(d, %H:%i:%s)TO_CHAR(d, HH24:MI:SS)CONVERT(varchar, d, 108)TO_CHAR(d, HH24:MI:SS)年/月/日拆分YEAR(d) / MONTH(d) / DAY(d)EXTRACT(YEAR FROM d) 等YEAR(d) / MONTH(d) / DAY(d)EXTRACT(YEAR FROM d) / TO_CHAR按天分组DATE_FORMAT(d, %Y-%m-%d)DATE(d)CAST(d AS DATE)TRUNC(d)这个表不是让你背的而是跨库开发或迁移评审时有个对照思路。核心点是函数本身不是银弹理解“我想得到什么形状的日期字符串”然后在对应数据库找等价函数就行。9. 我踩过的几个典型 DATE_FORMAT() 坑一次说清楚9.1 把%i写成%m分钟变月份这个坑我刚开始用时踩过后台日志里十几万条记录的时间全部显示成2024-07-01 07202305这样的怪东西。排查时发现是格式串写成了%Y-%m-%d %m:%s%m把分钟位置渲染成了月份。正确是%Y-%m-%d %H:%i:%s。后面我每次写完 DATE_FORMAT()都会先跑一条SELECT DATE_FORMAT(NOW(), 格式串)验证一下输出这个习惯帮我挡了很多低级错误。9.2GROUP BY别名与ORDER BY别名在不同模式下的差异MySQL 允许GROUP BY和ORDER BY使用SELECT中的别名但如果你同时启用了ONLY_FULL_GROUP_BY模式就要求SELECT里的非聚合列必须严格出现在GROUP BY中。比如SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day_key, COUNT(*) FROM orders GROUP BY day_key;这条在ONLY_FULL_GROUP_BY下是合法的因为day_key是GROUP BY的表达式。但如果你在SELECT里加了create_time源字段而没有加进GROUP BY就会报错。细节上建议团队统一 SQL 规范所有分组字段在SELECT和GROUP BY中保持一致减少环境差异带来的坑。9.3 格式串里的中文与转义DATE_FORMAT() 的格式串可以直接包含中文比如%Y年%m月%d日。这在导出报表时很常见。但如果格式串里要包含百分号本身需要两个百分号%%转义。比如你想输出“完成率 90%”这种字符串写法是SELECT CONCAT(完成率 , 90, %%);在 DATE_FORMAT() 的格式串中也一样。我见过有人写%单百分号导致输出错乱因为 MySQL 尝试把%后跟随的字符当作格式符解析。需要输出普通百分号时记得用%%。9.4 处理“月末最后一天”的边界SELECT DATE_FORMAT(sale_date, %Y-%m-%d) FROM sales WHERE DATE_FORMAT(sale_date, %Y-%m-%d) DATE_FORMAT(LAST_DAY(2024-02-01), %Y-%m-%d);这种需求本质上不是 DATE_FORMAT() 的问题而是“取某月最后一天”的问题。更简洁可靠的写法是WHERE sale_date LAST_DAY(2024-02-01) AND sale_date DATE_ADD(LAST_DAY(2024-02-01), INTERVAL 1 DAY);如果你需要按“每个月最后一个工作日”统计建议不要用 SQL 硬算直接在日历表里维护一个is_last_workday标记位。日历表是解决这类复杂日期业务的利器比在查询里写一堆日期函数可靠太多还能让 SQL 读起来跟业务口径一一对应。10. 一个完整案例从零写一个“本月订单每日趋势”报表最后用一个完整案例把这篇文章的内容串起来。假设需求是统计本月每天的下单量、下单额、下单人数并输出给前端展示前端要求日期格式为YYYY-MM-DD且没有订单的天也要补 0最终按日期正序排列。第一步生成这个月每天的日期序列WITH RECURSIVE date_range AS ( SELECT DATE_FORMAT(CURDATE(), %Y-%m-01) AS dt UNION ALL SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_range WHERE dt LAST_DAY(CURDATE()) ) SELECT * FROM date_range;第二步关联订单表并做汇总。因为日期序列是date类型订单表是datetime类型关联时用“范围”而非“格式化相等”保证索引可用WITH RECURSIVE date_range AS ( SELECT DATE_FORMAT(CURDATE(), %Y-%m-01) AS dt UNION ALL SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_range WHERE dt LAST_DAY(CURDATE()) ) SELECT DATE_FORMAT(dr.dt, %Y-%m-%d) AS day_key, COUNT(o.id) AS order_cnt, COALESCE(SUM(o.amount), 0) AS amount_sum, COUNT(DISTINCT o.user_id) AS user_cnt FROM date_range dr LEFT JOIN orders o ON o.create_time dr.dt AND o.create_time DATE_ADD(dr.dt, INTERVAL 1 DAY) GROUP BY dr.dt ORDER BY dr.dt;第三步检查空值。如果订单表里user_id有空值COUNT(DISTINCT o.user_id)不会计入 NULL这通常符合业务预期。但如果要统计“有订单的用户数”建议先去掉异常的测试单比如AND o.status paidLEFT JOIN orders o ON o.create_time dr.dt AND o.create_time DATE_ADD(dr.dt, INTERVAL 1 DAY) AND o.status paid这样一条 SQL 就把日期序列、分组聚合、空值补零、正序输出全部搞定了。我在实际做报表看板时经常把这段 SQL 封装成视图或者放进定时任务落到一张汇总表前端查询直接从汇总表取数性能和可读性都好。结尾写到这里关于 DATE_FORMAT() 的实战经验基本都倒出来了。我还是那句话这个函数看着简单真正的价值在“你怎么用它去折叠时间、分组统计、处理边界”这些细节里。踩过几回坑之后我现在写任何带日期的 SQL都会先问自己三个问题这个时间条件能不能改写成范围查询、分组键的字符串排序是否等于时间排序、时区口径全链路是否统一。把这三个问题答清楚DATE_FORMAT() 用起来基本就不会翻车了。最后再分享一个小技巧每次写完含 DATE_FORMAT() 的 SQL先跑一条SELECT DATE_FORMAT(NOW(), 你写的格式串)验证下输出长啥样。这个动作十秒钟都不到能帮你拦下一大半“看着没问题、跑出来全不对”的尴尬。