mysql date_format（长文讲解）

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战(已更新的所有项目都能学习) / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

• 新开坑项目:《Spring AI 项目实战》 正在持续爆肝中，基于 Spring AI + Spring Boot 3.x + JDK 21...， 点击查看 ;
• 《从零手撸：仿小红书（微服务架构）》 已完结，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;演示链接： http://116.62.199.48:7070 ;
• 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 90w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 3100+ 小伙伴加入学习 ，欢迎点击围观

前言

在数据库开发中，日期和时间的处理是一个高频需求。无论是统计用户注册时间、订单完成周期，还是生成报表，开发者都需要灵活地格式化日期数据。而 MySQL 的 DATE_FORMAT 函数正是解决这一问题的核心工具。对于编程初学者和中级开发者而言，掌握 DATE_FORMAT 的使用方法不仅能提升代码效率，还能让数据展示更直观易懂。本文将从基础语法到实战案例，结合形象比喻和代码示例，帮助读者系统掌握这一函数的精髓。

一、MySQL 中的日期与时间类型

在深入 DATE_FORMAT 之前，我们需要先了解 MySQL 中日期和时间的基本类型：

• DATE：存储日期，格式为 YYYY-MM-DD，例如 2023-10-05。
• TIME：存储时间，格式为 HH:MM:SS，例如 14:30:00。
• DATETIME：同时存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS。
• TIMESTAMP：与 DATETIME 类似，但支持时区转换和自动更新功能。

这些类型虽然能存储数据，但直接读取或展示时可能不够人性化。例如，显示 2023-10-05 14:30:00 可能不如 2023年10月5日 下午2:30 易于理解。此时，DATE_FORMAT 的作用就凸显出来了——它像一个“翻译器”，将原始日期数据转换为开发者或用户需要的格式。

二、DATE_FORMAT 的基础语法与核心参数

1. 函数的基本结构

DATE_FORMAT 的语法如下：

DATE_FORMAT(date, format)  

其中：

• date 是要格式化的日期值，可以是字段名、日期函数（如 NOW()）或字符串（如 '2023-10-05'）。
• format 是格式字符串，由普通字符和占位符（格式符）组成，例如 %Y-%m-%d。

2. 格式符的“词典”：关键占位符详解

格式符是 DATE_FORMAT 的灵魂，它决定了日期的输出方式。常见的格式符包括：

比喻： 将 DATE_FORMAT 比作一个模板，格式符就是模板中的“插槽”。例如，%Y-%m-%d 就像是一个模板，其中 %Y 插入年份，%m 插入月份，最终生成类似 2023-10-05 的结果。

三、实战案例：格式化日期的常见场景

1. 基础格式化：年月日的组合

需求： 将 DATETIME 类型的 created_at 字段格式化为 YYYY年MM月DD日。

SELECT DATE_FORMAT(created_at, '%Y年%m月%d日') AS formatted_date FROM users;  

输出示例：
| formatted_date | |----------------| | 2023年10月05日 |

技巧： 中文月份和日期的显示需要结合 %Y年%m月%d日，而 %m 和 %d 会自动补零，例如 10月 而非 月。

2. 时间的灵活处理：12小时制与 AM/PM

需求： 将时间 14:30:00 转换为 下午2:30。

SELECT DATE_FORMAT('14:30:00', '%W %h:%i %p') AS formatted_time;  

输出示例：
| formatted_time | |----------------| | 下午 02:30 PM |

解析：

• %W 表示星期名称（如 星期五），但此处输入是时间而非日期，因此可能返回空值或错误。
• %h 是12小时制小时，%i 是分钟，%p 是 AM 或 PM。

修正版：

SELECT CONCAT('下午 ', DATE_FORMAT('14:30:00', '%h:%i')) AS formatted_time;  

输出：
| formatted_time | |----------------| | 下午 02:30 |

3. 自定义分隔符与特殊符号

需求： 将日期 2023-10-05 格式化为 2023/10/05。

SELECT DATE_FORMAT('2023-10-05', '%Y/%m/%d') AS formatted_date;  

输出：
| formatted_date | |----------------| | 2023/10/05 |

技巧： 格式字符串中的普通字符（如 /）会被直接保留，因此可以自由组合符号。

四、进阶用法：与其它函数的结合

1. 处理时区差异

假设有一个 UTC 时间戳 2023-10-05 14:30:00，需要转换为北京时间（UTC+8）：

SELECT DATE_FORMAT(  
    UTC_TIMESTAMP() + INTERVAL 8 HOUR,  
    '%Y-%m-%d %H:%i'  
) AS beijing_time;  

输出：
| beijing_time | |-------------------| | 2023-10-06 22:30 |

解析：

• UTC_TIMESTAMP() 获取当前 UTC 时间。
• + INTERVAL 8 HOUR 将时间转换为北京时间。

2. 日期与文本的拼接

需求： 生成类似 订单于2023年10月5日14时30分创建 的描述。

SELECT CONCAT(  
    '订单于',  
    DATE_FORMAT(created_at, '%Y年%m月%d日%H时%i分'),  
    '创建'  
) AS description  
FROM orders  
WHERE order_id = 123;  

输出：
| description | |------------------------------| | 订单于2023年10月05日14时30分创建 |

五、常见问题与解决方案

1. 格式符不生效或输出为空

问题： 执行以下语句时返回 NULL：

SELECT DATE_FORMAT('2023-10-05', '%Y年%m月%d日');  

原因： 输入的日期格式不正确，或 MySQL 版本不支持中文占位符。

解决方案：

• 确保输入是有效日期，例如 '2023-10-05' 是合法的 DATE 格式。
• 如果需要中文月份，可改用 %M（如 十月）：

  SELECT DATE_FORMAT('2023-10-05', '%Y年%M月%d日');  
  

2. 处理不同数据类型的日期值

场景： 字段 log_time 是 VARCHAR 类型，存储 20231005 格式的日期。

解决方案： 先用 STR_TO_DATE 转换为日期，再格式化：

SELECT DATE_FORMAT(  
    STR_TO_DATE(log_time, '%Y%m%d'),  
    '%Y-%m-%d'  
) AS formatted_date  
FROM logs;  

六、性能优化与注意事项

1. 避免在 WHERE 子句中使用 DATE_FORMAT

直接在查询条件中使用 DATE_FORMAT 可能导致索引失效。例如：

-- 避免：  
SELECT * FROM orders  
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2023-10';  

-- 推荐：  
SELECT * FROM orders  
WHERE order_date BETWEEN '2023-10-01' AND '2023-10-31';  

2. 格式符的大小写敏感性

MySQL 的格式符通常使用大写字母（如 %Y），小写字母（如 %y）可能表示不同含义，需谨慎区分。

结论

通过本文，我们系统学习了 DATE_FORMAT 的基础语法、格式符用法、实战案例及常见问题。这一函数不仅是日期格式化的利器，更是提升数据可读性和开发效率的关键工具。无论是将日期转换为用户友好的中文格式，还是与时间函数结合处理时区问题，DATE_FORMAT 都能提供灵活且强大的支持。建议开发者在实际项目中多加练习，结合具体需求设计格式字符串，逐步掌握这一函数的全部潜力。

掌握 DATE_FORMAT 后，开发者可以更自信地应对涉及日期时间的复杂查询，为报表生成、日志分析等场景提供坚实的技术基础。希望本文能成为你 MySQL 学习旅程中的一个里程碑！