 函数详解：语法、格式符与性能优化)
搞数据库的兄弟十有八九都栽在日期格式化上过。“2024-01-05 14:30:00”这种值放进报表里要变成“2024年1月”或者接口要返回“01/05/2024”SQL 怎么写大多数人第一反应就是DATE_FORMAT()。这个函数确实是 MySQL 里处理日期显示格式最常用的工具没有之一。它做的事情很纯粹把一个 DATETIME / DATE / TIMESTAMP 类型的值按你指定的格式模板转换成对应的人类可读字符串。别小看这个转换报表统计、定时任务、数据导出、前端展示处处都离不开它。今天我就围绕DATE_FORMAT()函数把语法、格式符、实战场景和那些容易踩的坑一次性讲透。不管是刚入门的新手还是被日期格式化折磨过的老手这篇都值得你收藏备用。1. DATE_FORMAT() 是什么一个函数搞定日期显示问题1.1 语法与基本用法DATE_FORMAT()的语法非常简洁总共就两个参数DATE_FORMAT(date, format)date要格式化的日期或时间值可以是 DATETIME、DATE、TIMESTAMP 类型也可以是能隐式转换为日期的字符串比如2024-01-05。format格式化模板由一堆以%开头的格式符组成比如%Y-%m-%d。函数返回值是字符串类型比如你执行SELECT DATE_FORMAT(2024-01-05 14:30:00, %Y-%m-%d); -- 结果2024-01-05执行SELECT DATE_FORMAT(2024-01-05 14:30:00, %Y年%m月%d日 %H:%i:%s); -- 结果2024年01月05日 14:30:00就这么简单。但是真正用好它你需要把格式符记清楚尤其是大小写敏感的那几个。我见过太多人把%Y和%y搞混还有%m和%i分不清最后查出来的数据怎么看怎么别扭。1.2 为什么要在 SQL 层做格式化而不是扔给应用层有人可能会问日期格式化放在 Java、Python、PHP 这些业务代码里也能做为啥非要写在 SQL 里我的看法是很多场景下SQL 层格式化是更省事、更高效的选择。第一减少数据传输量。如果你只需要一个“2024-01”这样的月份字符串直接在 SQL 里转好返回的就是一个小字符串而不是一整个 DATETIME 值。特别是在查询几千上万条记录时网络传输的字节数能省不少。第二配合GROUP BY做统计时SQL 层格式化有天然优势。比如你想统计每个月的订单数直接SELECT DATE_FORMAT(order_time, %Y-%m) AS month, COUNT(*) FROM orders GROUP BY month;这时候你根本不需要把原始时间字段查出来再在代码里循环切分一个GROUP BY就搞定了。第三对报表工具、数据导出脚本来说SQL 直接输出格式化后的字段能省掉一层无关的转换逻辑。比如用 Navicat 导出 Excel或者用 Python 写脚本拉数拿到的就是最终展示格式省心。当然SQL 层格式化也不是万能的。如果你的格式化逻辑非常复杂或者需要在多种数据库之间迁移那可能放到应用层更合适。但对于 MySQL 单库项目DATE_FORMAT()就是最直接的方案。2. 格式符完全拆解从 %Y 到 %f别再用错大小写2.1 常用格式符速查表DATE_FORMAT()的核心就在格式符。我把开发中高频用到的格式符整理成了表格方便你查格式符含义示例基于 2024-01-05 14:30:45备注%Y四位年份2024常用%y两位年份24容易和 %Y 混淆%m两位月份01-1201注意是月份%c月份1-12无前导零1适合展示用%d两位日01-3105常用%e日1-31无前导零5适合展示用%H24小时制小时00-2314注意大写%h12小时制小时01-1202配合 %p 使用%i分钟00-5930容易和 %m 搞混%s秒00-5945也可用 %S%f微秒000000-999999000000MySQL 5.6%pAM 或 PMPM配合 %h 使用%r12小时时间格式02:30:45 PM相当于 %h:%i:%s %p%T24小时时间格式14:30:45相当于 %H:%i:%s%W星期英文全称Friday注意大小写%w星期数字0周日6周六5用来做周统计%a星期英文缩写Fri适合图表标签%M月份英文全称January注意和 %m 区分%b月份英文缩写Jan适合图表标签%j一年中的第几天001-366005不常用%D带序数词的日期5th如 1st, 2nd, 3rd%U一年中的第几周00-53周日为每周第一天01按周统计时用%u一年中的第几周00-53周一为每周第一天01业务上注意区分%x周所属的年份四位周一为一周开头2024常和 %v 配对%v一年中的第几周01-53周一为一周开头01和 %x 配合%X周所属的年份四位周日为一周开头2024常和 %V 配对%V一年中的第几周01-53周日为一周开头01和 %X 配合%%转义为字面量 %%比如 100%% 显示 100%别看这个表长实际开发中你常用到的无非就是%Y、%m、%d、%H、%i、%s外加偶尔用到的%W、%j。把这些记熟基本能覆盖 95% 的需求。2.2 组合出你想要的时间格式几个高频场景格式符可以自由组合MySQL 会按顺序把它们替换成真实值。我举个几个实际例子标准日期时间字符串SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2024-01-05 14:30:45中文习惯的日期格式SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 2024年01月05日只取年月用于月度统计SELECT DATE_FORMAT(NOW(), %Y-%m); -- 2024-01带星期几的展示SELECT DATE_FORMAT(NOW(), %Y-%m-%d %W); -- 2024-01-05 Friday按季度分组可以用月份整除SELECT CONCAT(YEAR(NOW()), -Q, QUARTER(NOW()));当然如果想用DATE_FORMAT实现可以换算成月但不推荐QUARTER()更清晰。时间只保留时分SELECT DATE_FORMAT(NOW(), %H:%i); -- 14:30这里要注意%H和%h的区别非常大。%H是 24 小时制下午两点会显示 14%h是 12 小时制下午两点会显示 02后面通常要跟%p区分上午下午。所以做 24 小时制系统千万别用%h。3. 实战案例日期格式化的三个典型场景3.1 按月份分组统计订单金额这是一个非常经典的需求统计每个月订单总金额。假设订单表orders有字段order_id、amount、order_time数据类型为 DATETIME。错误的写法是先把所有订单查出来然后在应用层逐个取年月做累加。数据量一大内存和 IO 都扛不住。正确的姿势是直接在 SQL 里用DATE_FORMAT()配合GROUP BYSELECT DATE_FORMAT(order_time, %Y-%m) AS month, SUM(amount) AS total_amount FROM orders WHERE order_time 2023-01-01 00:00:00 AND order_time 2025-01-01 00:00:00 GROUP BY month ORDER BY month;这里有几个细节值得说一下。第一GROUP BY month这个写法在 MySQL 里是允许的因为month是SELECT中的别名。但更严谨的写法是GROUP BY DATE_FORMAT(order_time, %Y-%m)避免某些 MySQL 模式下别名被解析成别的含义。我自己习惯写完整表达式可读性更好也不容易踩坑。第二排序用的是ORDER BY month。这里month是字符串但恰好是2024-01、2024-02这种格式字典序和时间顺序一致所以排序结果是正确的。如果你格式化出来的字符串不能按字典序对应时间顺序那就别用它排序老老实实按原始时间字段排序。第三建议在WHERE里过滤掉不需要的日期范围这样可以减少DATE_FORMAT()的计算量。如果order_time上有索引还能走索引范围扫描。3.2 用 DATE_FORMAT 做生日与到期提醒生日提醒和到期提醒是两类常见业务它们的共同点是只关心“月-日”不关心年份。比如会员表members有字段birthdayDATE你想找出 30 天内要过生日的人。这时候不能直接拿出生日和当前日期相减因为生日是每年的日期和当年日期差会绕弯子。更直观的做法是提取出生日期的%m-%d再和当前日期的%m-%d比较。SELECT name, birthday, DATE_FORMAT(birthday, %m-%d) AS birth_md FROM members WHERE DATE_FORMAT(birthday, %m-%d) BETWEEN DATE_FORMAT(CURDATE(), %m-%d) AND DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 30 DAY), %m-%d);这种写法有一点要注意如果 30 天后跨年了比如当前是 12 月 20 日那么DATE_FORMAT(CURDATE(), %m-%d)是12-2030 天后是01-19BETWEEN 12-20 AND 01-19查不到任何数据因为字符串比较时01-19小于12-20。解决方法是把条件拆开或者用DAYOFYEAR()。但这里我还是想提一句DATE_FORMAT在处理跨年提醒时并不完美往往需要额外判断。更稳妥的方案是用DAYOFYEAR-- 忽略闰年的极端情况基本可用 SELECT name, birthday FROM members WHERE DAYOFYEAR(birthday) BETWEEN DAYOFYEAR(CURDATE()) AND DAYOFYEAR(DATE_ADD(CURDATE(), INTERVAL 30 DAY));不过DAYOFYEAR在闰年时也会误差更合理的做法是用DATE_ADD每年替换年份再比较。总之业务里要灵活选择函数不是所有日期问题都靠DATE_FORMAT()硬扛。它负责展示和分组涉及日期区间计算时还是用DATE_ADD、DATEDIFF这类时间计算函数更可靠。3.3 格式化结果参与排序、比较时容易踩的坑DATE_FORMAT()返回的是字符串所以一旦你拿格式化结果去排序、比较就一定要考虑字符串语义和时间语义是否一致。比如有一张表events字段event_time是 DATETIME你想按“小时”排序SELECT event_id, DATE_FORMAT(event_time, %H:%i) AS time_str FROM events ORDER BY time_str;看起来没问题因为14:30按字典序排序和按时间排序是一样的。但如果你想按“月日”排序而且日期是%m-%d也没问题。换成%c月%e日这种格式就出事了1月5日会排在11月20日前面因为字符串比较从头开始“1”比“11”小不了多少但 “1” 和 “1” 相同第二位 “月” 比 “1” 大导致结果混乱。所以我的建议是需要排序、比较时尽量使用原始时间字段不要让格式化结果参与运算。如果必须按格式化结果排序请选择字典序和时间序一致的格式比如%Y-%m-%d %H:%i:%s或者转成时间戳。另外还有一个坑DATE_FORMAT()处理 NULL 值。如果传入的日期是 NULL返回结果也是 NULLSELECT DATE_FORMAT(NULL, %Y-%m-%d); -- NULL在GROUP BY中所有 NULL 日期会分成一组。有时候你希望把 NULL 显示成“未知”要用IFNULLSELECT IFNULL(DATE_FORMAT(order_time, %Y-%m), 未知) AS month FROM orders;这三个场景基本覆盖了DATE_FORMAT()的绝大多数应用。接下来聊一聊大家更容易忽略的性能问题。4. 常见问题与性能优化实录4.1 格式符写错结果变成了 NULL 或乱码经常有人在评论区问“为什么我DATE_FORMAT查出来是 NULL或者是一堆奇怪的字符” 99% 的情况是格式符写错了。DATE_FORMAT()对格式符的解析是严格区分的。遇到不认识的格式符MySQL 会把它当普通字符原样输出而不是报错。比如%Y和%y都合法但含义不同如果你把%i写成%M会输出月份英文名而不是分钟把%e写成%d会补零把%H写成%h会变成 12 小时制。最典型的问题是查出来全是00或者0000-00-00。这往往不是DATE_FORMAT()的问题而是源数据本身就不干净。比如字段类型是 VARCHAR里面存了0000-00-00或者日期字符串不符合 MySQL 的解析规则DATE_FORMAT()会返回 NULL。处理办法是在组装 SQL 前先分析源数据必要时用STR_TO_DATE()清洗。建议格式化模板中的非 ASCII 字符比如中文“年月日”、连字符-、冒号:在格式串里直接写就行MySQL 会原样保留。但要注意转义%如果要在格式串中输出百分号必须写成%%否则 MySQL 会尝试解析下一位字符作为格式符结果可能不是你想要的。4.2 索引失效别在 WHERE 条件里直接套 DATE_FORMAT这是性能方面最大的坑。假设你有一个高频查询SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-05;这个写法功能上没错但它让create_time上的索引直接失效。原因是 MySQL 必须对每一行的create_time先做DATE_FORMAT()运算得到字符串后才跟常量比较无法直接利用索引进行范围定位。数据量一大全表扫描性能就崩了。正确写法是使用范围查询SELECT * FROM orders WHERE create_time 2024-01-05 00:00:00 AND create_time 2024-01-06 00:00:00;这样在create_time字段上可以直接走索引而且效率通常更优。如果你确实需要按“某个月”查询可以写成SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;避免对列做函数运算是索引使用的基本常识。DATE_FORMAT()也不例外。如果条件里必须要用函数有些场景下你可以考虑 MySQL 5.7 的“函数索引”即将表达式创建为索引这样能缓解性能问题但大部分开发场景根本用不到。先用范围查询优化是最稳妥的方案。4.3 DATE_FORMAT 与 STR_TO_DATE、UNIX_TIMESTAMP 的配合DATE_FORMAT()是“日期转字符串”它的逆操作是STR_TO_DATE()也就是“字符串转日期”。两个函数经常配合使用。比如你有个字符串05/01/2024想让 MySQL 识别成日期可以用SELECT STR_TO_DATE(05/01/2024, %m/%d/%Y); -- 2024-05-01STR_TO_DATE()的格式符体系和DATE_FORMAT()完全一致只是作用方向相反。如果字符串和格式不匹配返回 NULL。还有一种常见需求把日期转成时间戳。MySQL 里可以用UNIX_TIMESTAMP(date)但它返回的是秒级时间戳如果你需要毫秒级可以用UNIX_TIMESTAMP(date) * 1000。反过来时间戳转日期用FROM_UNIXTIME()。DATE_FORMAT()和这几个函数联用能覆盖几乎所有的日期转换场景。举个例子如果你要按“天”统计某张表每天的数据但表中时间字段是毫秒级时间戳ts_ms你可以这样SELECT DATE_FORMAT(FROM_UNIXTIME(ts_ms / 1000), %Y-%m-%d) AS day, COUNT(*) FROM events GROUP BY day;这个用法在 IoT 设备上报、日志类数据中非常常见。但同样有性能问题对ts_ms做了函数运算不能走索引。如果表特别大建议在写入时额外增加一个日期字段或者用时间范围过滤把数据量缩小后再分组。4.4 时区问题为什么时间差了几个小时DATE_FORMAT()本身没有时区概念它只是把传入的时间值按格式输出。所以时区问题其实出现在“传入的时间值”上。MySQL 的NOW()、CURDATE()返回的是会话时区下的当前时间。如果你的应用服务器时区和数据库服务器时区不一致就会导致DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)返回的时间不是你本地时间。遇到这种问题先查一下会话时区SELECT global.time_zone, session.time_zone;如果是SYSTEM则跟随操作系统时区。如果你需要统一 UTC 或北京时间可以修改time_zone参数或者连接数据库时指定时区。不过这些和DATE_FORMAT()本身关系不大但却是实际排查中绕不开的一环。我见过好几个人半天查不出问题最后发现是 JDBC 连接串里没加serverTimezoneAsia/Shanghai导致日期显示差了 8 小时。4.5 EXPLAIN 验证你的优化是否真的有效我始终建议在涉及大表的查询上线前跑一下EXPLAIN。比如前面说的范围查询优化你应该看到类似这样EXPLAIN SELECT * FROM orders WHERE create_time 2024-01-05 00:00:00 AND create_time 2024-01-06 00:00:00;结果中key列如果能显示你定义的索引名就说明查询走了索引。如果你拿DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-05去 EXPLAINkey列通常为 NULLrows会很大。这就是最直观的证据。别嫌这两步麻烦生产环境里一个慢查询分分钟能把数据库压垮提前用EXPLAIN验证能省掉太多半夜被叫醒的尴尬。5. 我的实操心得与几条建议5.1 根据需求选择“格式化”还是“时间计算”用过一段时间后你会逐渐发现DATE_FORMAT()的定位是“展示层工具”而不是“计算层工具”。凡是需要比较大小、计算差值、按时间范围过滤的场景我都会优先考虑DATE_SUB、DATE_ADD、DATEDIFF、TIMESTAMPDIFF等函数。只有确定要输出格式化字符串的时候才让DATE_FORMAT()上场。举个例子你要找“最近 7 天注册的用户”用DATE_FORMAT(create_time, %Y-%m-%d) CURDATE()这种条件基本是自找麻烦。正确做法是SELECT * FROM users WHERE create_time DATE_SUB(CURDATE(), INTERVAL 7 DAY);DATE_FORMAT()不背这个锅但不要因为图省事而错误使用它。5.2 统一代码规范格式符大写、别名清晰项目里如果多人维护日期格式很容易出现风格不一致。有人用%Y-%m-%d有人用%Y/%m/%d还有人用%y-%M-%d。我自己的习惯是日期统一用%Y-%m-%d日期时间统一用%Y-%m-%d %H:%i:%s月份统一定义为%Y-%m所有格式符一律大写字母除了%i这种必须小写的这样一眼能看出是“分钟”另外SELECT中给格式化结果起别名时不要起date、time这类保留字容易引起混乱。用day_str、month_str、datetime_str这种后缀_str的方式一看就知道是字符串类型后续代码里也不会误用。5.3 日期格式化后的隐式转换风险DATE_FORMAT()返回的是字符串。如果你拿它和数字比较MySQL 会做隐式类型转换。比如SELECT DATE_FORMAT(2024-01-05, %Y) 1;结果是 2025因为2024被转成了数字 2024。这种隐式转换在报表计算里偶尔会导致“看起来没问题但结果很奇怪”的情况。遇到这类需求我建议显式转换SELECT CAST(DATE_FORMAT(2024-01-05, %Y) AS UNSIGNED) 1;或者干脆用YEAR()函数。写清楚类型代码的可读性和稳定性都会更好。5.4 最后一个小技巧排在%Y-%m-%d前面的DATE()可能更适合你如果你只是想取出日期部分不要年月日时分秒用DATE()比DATE_FORMAT()更轻量SELECT DATE(2024-01-05 14:30:45); -- 2024-01-05DATE()返回 DATE 类型不需要格式串也不容易出错。做GROUP BY时如果字段本来就是 DATE 类型也可以直接GROUP BY create_date不需要格式化。总之能用简单函数解决的事别整花活。DATE_FORMAT()是我在 MySQL 中使用频次最高的日期函数之一。它足够简单也足够灵活。这篇文章里的每个示例我都在实际环境里跑过包括那些“踩坑”的写法也是自己一步一步填过的坑。建议你收藏这份格式符表格下次写 SQL 忘了%i还是%s时翻出来看一眼就能解决。日期处理是数据库操作里的基本功把DATE_FORMAT()用好了报表统计和界面展示的效率都能提升一大截。
MySQL DATE_FORMAT() 函数详解：语法、格式符与性能优化
搞数据库的兄弟十有八九都栽在日期格式化上过。“2024-01-05 14:30:00”这种值放进报表里要变成“2024年1月”或者接口要返回“01/05/2024”SQL 怎么写大多数人第一反应就是DATE_FORMAT()。这个函数确实是 MySQL 里处理日期显示格式最常用的工具没有之一。它做的事情很纯粹把一个 DATETIME / DATE / TIMESTAMP 类型的值按你指定的格式模板转换成对应的人类可读字符串。别小看这个转换报表统计、定时任务、数据导出、前端展示处处都离不开它。今天我就围绕DATE_FORMAT()函数把语法、格式符、实战场景和那些容易踩的坑一次性讲透。不管是刚入门的新手还是被日期格式化折磨过的老手这篇都值得你收藏备用。1. DATE_FORMAT() 是什么一个函数搞定日期显示问题1.1 语法与基本用法DATE_FORMAT()的语法非常简洁总共就两个参数DATE_FORMAT(date, format)date要格式化的日期或时间值可以是 DATETIME、DATE、TIMESTAMP 类型也可以是能隐式转换为日期的字符串比如2024-01-05。format格式化模板由一堆以%开头的格式符组成比如%Y-%m-%d。函数返回值是字符串类型比如你执行SELECT DATE_FORMAT(2024-01-05 14:30:00, %Y-%m-%d); -- 结果2024-01-05执行SELECT DATE_FORMAT(2024-01-05 14:30:00, %Y年%m月%d日 %H:%i:%s); -- 结果2024年01月05日 14:30:00就这么简单。但是真正用好它你需要把格式符记清楚尤其是大小写敏感的那几个。我见过太多人把%Y和%y搞混还有%m和%i分不清最后查出来的数据怎么看怎么别扭。1.2 为什么要在 SQL 层做格式化而不是扔给应用层有人可能会问日期格式化放在 Java、Python、PHP 这些业务代码里也能做为啥非要写在 SQL 里我的看法是很多场景下SQL 层格式化是更省事、更高效的选择。第一减少数据传输量。如果你只需要一个“2024-01”这样的月份字符串直接在 SQL 里转好返回的就是一个小字符串而不是一整个 DATETIME 值。特别是在查询几千上万条记录时网络传输的字节数能省不少。第二配合GROUP BY做统计时SQL 层格式化有天然优势。比如你想统计每个月的订单数直接SELECT DATE_FORMAT(order_time, %Y-%m) AS month, COUNT(*) FROM orders GROUP BY month;这时候你根本不需要把原始时间字段查出来再在代码里循环切分一个GROUP BY就搞定了。第三对报表工具、数据导出脚本来说SQL 直接输出格式化后的字段能省掉一层无关的转换逻辑。比如用 Navicat 导出 Excel或者用 Python 写脚本拉数拿到的就是最终展示格式省心。当然SQL 层格式化也不是万能的。如果你的格式化逻辑非常复杂或者需要在多种数据库之间迁移那可能放到应用层更合适。但对于 MySQL 单库项目DATE_FORMAT()就是最直接的方案。2. 格式符完全拆解从 %Y 到 %f别再用错大小写2.1 常用格式符速查表DATE_FORMAT()的核心就在格式符。我把开发中高频用到的格式符整理成了表格方便你查格式符含义示例基于 2024-01-05 14:30:45备注%Y四位年份2024常用%y两位年份24容易和 %Y 混淆%m两位月份01-1201注意是月份%c月份1-12无前导零1适合展示用%d两位日01-3105常用%e日1-31无前导零5适合展示用%H24小时制小时00-2314注意大写%h12小时制小时01-1202配合 %p 使用%i分钟00-5930容易和 %m 搞混%s秒00-5945也可用 %S%f微秒000000-999999000000MySQL 5.6%pAM 或 PMPM配合 %h 使用%r12小时时间格式02:30:45 PM相当于 %h:%i:%s %p%T24小时时间格式14:30:45相当于 %H:%i:%s%W星期英文全称Friday注意大小写%w星期数字0周日6周六5用来做周统计%a星期英文缩写Fri适合图表标签%M月份英文全称January注意和 %m 区分%b月份英文缩写Jan适合图表标签%j一年中的第几天001-366005不常用%D带序数词的日期5th如 1st, 2nd, 3rd%U一年中的第几周00-53周日为每周第一天01按周统计时用%u一年中的第几周00-53周一为每周第一天01业务上注意区分%x周所属的年份四位周一为一周开头2024常和 %v 配对%v一年中的第几周01-53周一为一周开头01和 %x 配合%X周所属的年份四位周日为一周开头2024常和 %V 配对%V一年中的第几周01-53周日为一周开头01和 %X 配合%%转义为字面量 %%比如 100%% 显示 100%别看这个表长实际开发中你常用到的无非就是%Y、%m、%d、%H、%i、%s外加偶尔用到的%W、%j。把这些记熟基本能覆盖 95% 的需求。2.2 组合出你想要的时间格式几个高频场景格式符可以自由组合MySQL 会按顺序把它们替换成真实值。我举个几个实际例子标准日期时间字符串SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2024-01-05 14:30:45中文习惯的日期格式SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 2024年01月05日只取年月用于月度统计SELECT DATE_FORMAT(NOW(), %Y-%m); -- 2024-01带星期几的展示SELECT DATE_FORMAT(NOW(), %Y-%m-%d %W); -- 2024-01-05 Friday按季度分组可以用月份整除SELECT CONCAT(YEAR(NOW()), -Q, QUARTER(NOW()));当然如果想用DATE_FORMAT实现可以换算成月但不推荐QUARTER()更清晰。时间只保留时分SELECT DATE_FORMAT(NOW(), %H:%i); -- 14:30这里要注意%H和%h的区别非常大。%H是 24 小时制下午两点会显示 14%h是 12 小时制下午两点会显示 02后面通常要跟%p区分上午下午。所以做 24 小时制系统千万别用%h。3. 实战案例日期格式化的三个典型场景3.1 按月份分组统计订单金额这是一个非常经典的需求统计每个月订单总金额。假设订单表orders有字段order_id、amount、order_time数据类型为 DATETIME。错误的写法是先把所有订单查出来然后在应用层逐个取年月做累加。数据量一大内存和 IO 都扛不住。正确的姿势是直接在 SQL 里用DATE_FORMAT()配合GROUP BYSELECT DATE_FORMAT(order_time, %Y-%m) AS month, SUM(amount) AS total_amount FROM orders WHERE order_time 2023-01-01 00:00:00 AND order_time 2025-01-01 00:00:00 GROUP BY month ORDER BY month;这里有几个细节值得说一下。第一GROUP BY month这个写法在 MySQL 里是允许的因为month是SELECT中的别名。但更严谨的写法是GROUP BY DATE_FORMAT(order_time, %Y-%m)避免某些 MySQL 模式下别名被解析成别的含义。我自己习惯写完整表达式可读性更好也不容易踩坑。第二排序用的是ORDER BY month。这里month是字符串但恰好是2024-01、2024-02这种格式字典序和时间顺序一致所以排序结果是正确的。如果你格式化出来的字符串不能按字典序对应时间顺序那就别用它排序老老实实按原始时间字段排序。第三建议在WHERE里过滤掉不需要的日期范围这样可以减少DATE_FORMAT()的计算量。如果order_time上有索引还能走索引范围扫描。3.2 用 DATE_FORMAT 做生日与到期提醒生日提醒和到期提醒是两类常见业务它们的共同点是只关心“月-日”不关心年份。比如会员表members有字段birthdayDATE你想找出 30 天内要过生日的人。这时候不能直接拿出生日和当前日期相减因为生日是每年的日期和当年日期差会绕弯子。更直观的做法是提取出生日期的%m-%d再和当前日期的%m-%d比较。SELECT name, birthday, DATE_FORMAT(birthday, %m-%d) AS birth_md FROM members WHERE DATE_FORMAT(birthday, %m-%d) BETWEEN DATE_FORMAT(CURDATE(), %m-%d) AND DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 30 DAY), %m-%d);这种写法有一点要注意如果 30 天后跨年了比如当前是 12 月 20 日那么DATE_FORMAT(CURDATE(), %m-%d)是12-2030 天后是01-19BETWEEN 12-20 AND 01-19查不到任何数据因为字符串比较时01-19小于12-20。解决方法是把条件拆开或者用DAYOFYEAR()。但这里我还是想提一句DATE_FORMAT在处理跨年提醒时并不完美往往需要额外判断。更稳妥的方案是用DAYOFYEAR-- 忽略闰年的极端情况基本可用 SELECT name, birthday FROM members WHERE DAYOFYEAR(birthday) BETWEEN DAYOFYEAR(CURDATE()) AND DAYOFYEAR(DATE_ADD(CURDATE(), INTERVAL 30 DAY));不过DAYOFYEAR在闰年时也会误差更合理的做法是用DATE_ADD每年替换年份再比较。总之业务里要灵活选择函数不是所有日期问题都靠DATE_FORMAT()硬扛。它负责展示和分组涉及日期区间计算时还是用DATE_ADD、DATEDIFF这类时间计算函数更可靠。3.3 格式化结果参与排序、比较时容易踩的坑DATE_FORMAT()返回的是字符串所以一旦你拿格式化结果去排序、比较就一定要考虑字符串语义和时间语义是否一致。比如有一张表events字段event_time是 DATETIME你想按“小时”排序SELECT event_id, DATE_FORMAT(event_time, %H:%i) AS time_str FROM events ORDER BY time_str;看起来没问题因为14:30按字典序排序和按时间排序是一样的。但如果你想按“月日”排序而且日期是%m-%d也没问题。换成%c月%e日这种格式就出事了1月5日会排在11月20日前面因为字符串比较从头开始“1”比“11”小不了多少但 “1” 和 “1” 相同第二位 “月” 比 “1” 大导致结果混乱。所以我的建议是需要排序、比较时尽量使用原始时间字段不要让格式化结果参与运算。如果必须按格式化结果排序请选择字典序和时间序一致的格式比如%Y-%m-%d %H:%i:%s或者转成时间戳。另外还有一个坑DATE_FORMAT()处理 NULL 值。如果传入的日期是 NULL返回结果也是 NULLSELECT DATE_FORMAT(NULL, %Y-%m-%d); -- NULL在GROUP BY中所有 NULL 日期会分成一组。有时候你希望把 NULL 显示成“未知”要用IFNULLSELECT IFNULL(DATE_FORMAT(order_time, %Y-%m), 未知) AS month FROM orders;这三个场景基本覆盖了DATE_FORMAT()的绝大多数应用。接下来聊一聊大家更容易忽略的性能问题。4. 常见问题与性能优化实录4.1 格式符写错结果变成了 NULL 或乱码经常有人在评论区问“为什么我DATE_FORMAT查出来是 NULL或者是一堆奇怪的字符” 99% 的情况是格式符写错了。DATE_FORMAT()对格式符的解析是严格区分的。遇到不认识的格式符MySQL 会把它当普通字符原样输出而不是报错。比如%Y和%y都合法但含义不同如果你把%i写成%M会输出月份英文名而不是分钟把%e写成%d会补零把%H写成%h会变成 12 小时制。最典型的问题是查出来全是00或者0000-00-00。这往往不是DATE_FORMAT()的问题而是源数据本身就不干净。比如字段类型是 VARCHAR里面存了0000-00-00或者日期字符串不符合 MySQL 的解析规则DATE_FORMAT()会返回 NULL。处理办法是在组装 SQL 前先分析源数据必要时用STR_TO_DATE()清洗。建议格式化模板中的非 ASCII 字符比如中文“年月日”、连字符-、冒号:在格式串里直接写就行MySQL 会原样保留。但要注意转义%如果要在格式串中输出百分号必须写成%%否则 MySQL 会尝试解析下一位字符作为格式符结果可能不是你想要的。4.2 索引失效别在 WHERE 条件里直接套 DATE_FORMAT这是性能方面最大的坑。假设你有一个高频查询SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-05;这个写法功能上没错但它让create_time上的索引直接失效。原因是 MySQL 必须对每一行的create_time先做DATE_FORMAT()运算得到字符串后才跟常量比较无法直接利用索引进行范围定位。数据量一大全表扫描性能就崩了。正确写法是使用范围查询SELECT * FROM orders WHERE create_time 2024-01-05 00:00:00 AND create_time 2024-01-06 00:00:00;这样在create_time字段上可以直接走索引而且效率通常更优。如果你确实需要按“某个月”查询可以写成SELECT * FROM orders WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-02-01 00:00:00;避免对列做函数运算是索引使用的基本常识。DATE_FORMAT()也不例外。如果条件里必须要用函数有些场景下你可以考虑 MySQL 5.7 的“函数索引”即将表达式创建为索引这样能缓解性能问题但大部分开发场景根本用不到。先用范围查询优化是最稳妥的方案。4.3 DATE_FORMAT 与 STR_TO_DATE、UNIX_TIMESTAMP 的配合DATE_FORMAT()是“日期转字符串”它的逆操作是STR_TO_DATE()也就是“字符串转日期”。两个函数经常配合使用。比如你有个字符串05/01/2024想让 MySQL 识别成日期可以用SELECT STR_TO_DATE(05/01/2024, %m/%d/%Y); -- 2024-05-01STR_TO_DATE()的格式符体系和DATE_FORMAT()完全一致只是作用方向相反。如果字符串和格式不匹配返回 NULL。还有一种常见需求把日期转成时间戳。MySQL 里可以用UNIX_TIMESTAMP(date)但它返回的是秒级时间戳如果你需要毫秒级可以用UNIX_TIMESTAMP(date) * 1000。反过来时间戳转日期用FROM_UNIXTIME()。DATE_FORMAT()和这几个函数联用能覆盖几乎所有的日期转换场景。举个例子如果你要按“天”统计某张表每天的数据但表中时间字段是毫秒级时间戳ts_ms你可以这样SELECT DATE_FORMAT(FROM_UNIXTIME(ts_ms / 1000), %Y-%m-%d) AS day, COUNT(*) FROM events GROUP BY day;这个用法在 IoT 设备上报、日志类数据中非常常见。但同样有性能问题对ts_ms做了函数运算不能走索引。如果表特别大建议在写入时额外增加一个日期字段或者用时间范围过滤把数据量缩小后再分组。4.4 时区问题为什么时间差了几个小时DATE_FORMAT()本身没有时区概念它只是把传入的时间值按格式输出。所以时区问题其实出现在“传入的时间值”上。MySQL 的NOW()、CURDATE()返回的是会话时区下的当前时间。如果你的应用服务器时区和数据库服务器时区不一致就会导致DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)返回的时间不是你本地时间。遇到这种问题先查一下会话时区SELECT global.time_zone, session.time_zone;如果是SYSTEM则跟随操作系统时区。如果你需要统一 UTC 或北京时间可以修改time_zone参数或者连接数据库时指定时区。不过这些和DATE_FORMAT()本身关系不大但却是实际排查中绕不开的一环。我见过好几个人半天查不出问题最后发现是 JDBC 连接串里没加serverTimezoneAsia/Shanghai导致日期显示差了 8 小时。4.5 EXPLAIN 验证你的优化是否真的有效我始终建议在涉及大表的查询上线前跑一下EXPLAIN。比如前面说的范围查询优化你应该看到类似这样EXPLAIN SELECT * FROM orders WHERE create_time 2024-01-05 00:00:00 AND create_time 2024-01-06 00:00:00;结果中key列如果能显示你定义的索引名就说明查询走了索引。如果你拿DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-05去 EXPLAINkey列通常为 NULLrows会很大。这就是最直观的证据。别嫌这两步麻烦生产环境里一个慢查询分分钟能把数据库压垮提前用EXPLAIN验证能省掉太多半夜被叫醒的尴尬。5. 我的实操心得与几条建议5.1 根据需求选择“格式化”还是“时间计算”用过一段时间后你会逐渐发现DATE_FORMAT()的定位是“展示层工具”而不是“计算层工具”。凡是需要比较大小、计算差值、按时间范围过滤的场景我都会优先考虑DATE_SUB、DATE_ADD、DATEDIFF、TIMESTAMPDIFF等函数。只有确定要输出格式化字符串的时候才让DATE_FORMAT()上场。举个例子你要找“最近 7 天注册的用户”用DATE_FORMAT(create_time, %Y-%m-%d) CURDATE()这种条件基本是自找麻烦。正确做法是SELECT * FROM users WHERE create_time DATE_SUB(CURDATE(), INTERVAL 7 DAY);DATE_FORMAT()不背这个锅但不要因为图省事而错误使用它。5.2 统一代码规范格式符大写、别名清晰项目里如果多人维护日期格式很容易出现风格不一致。有人用%Y-%m-%d有人用%Y/%m/%d还有人用%y-%M-%d。我自己的习惯是日期统一用%Y-%m-%d日期时间统一用%Y-%m-%d %H:%i:%s月份统一定义为%Y-%m所有格式符一律大写字母除了%i这种必须小写的这样一眼能看出是“分钟”另外SELECT中给格式化结果起别名时不要起date、time这类保留字容易引起混乱。用day_str、month_str、datetime_str这种后缀_str的方式一看就知道是字符串类型后续代码里也不会误用。5.3 日期格式化后的隐式转换风险DATE_FORMAT()返回的是字符串。如果你拿它和数字比较MySQL 会做隐式类型转换。比如SELECT DATE_FORMAT(2024-01-05, %Y) 1;结果是 2025因为2024被转成了数字 2024。这种隐式转换在报表计算里偶尔会导致“看起来没问题但结果很奇怪”的情况。遇到这类需求我建议显式转换SELECT CAST(DATE_FORMAT(2024-01-05, %Y) AS UNSIGNED) 1;或者干脆用YEAR()函数。写清楚类型代码的可读性和稳定性都会更好。5.4 最后一个小技巧排在%Y-%m-%d前面的DATE()可能更适合你如果你只是想取出日期部分不要年月日时分秒用DATE()比DATE_FORMAT()更轻量SELECT DATE(2024-01-05 14:30:45); -- 2024-01-05DATE()返回 DATE 类型不需要格式串也不容易出错。做GROUP BY时如果字段本来就是 DATE 类型也可以直接GROUP BY create_date不需要格式化。总之能用简单函数解决的事别整花活。DATE_FORMAT()是我在 MySQL 中使用频次最高的日期函数之一。它足够简单也足够灵活。这篇文章里的每个示例我都在实际环境里跑过包括那些“踩坑”的写法也是自己一步一步填过的坑。建议你收藏这份格式符表格下次写 SQL 忘了%i还是%s时翻出来看一眼就能解决。日期处理是数据库操作里的基本功把DATE_FORMAT()用好了报表统计和界面展示的效率都能提升一大截。
