mysql如何格式化日期查询-mysql教程-PHP中文网

mysql如何格式化日期查询

P粉602998670

发布： 2025-09-17 14:58:01

原创

786人浏览过

MySQL通过DATE_FORMAT()和STR_TO_DATE()实现日期格式化与解析，前者将日期转为指定格式字符串，后者将字符串按格式转为日期。常用格式符如%Y、%m、%d等。查询时应避免在WHERE中对日期字段使用函数以确保索引有效，推荐用BETWEEN进行范围查询。处理NULL值可用IFNULL或COALESCE，自定义格式可结合CASE语句实现。为提升性能，建议创建索引、预计算格式化结果或使用缓存，并通过EXPLAIN分析执行计划优化查询。

mysql如何格式化日期查询

MySQL格式化日期查询，本质上就是将存储在数据库中的日期数据，按照你想要的格式展示出来，或者根据特定格式的日期字符串进行查询。这涉及到两个主要方向：一是将日期格式化成字符串，二是将字符串转换成日期。

解决方案：

MySQL提供了

DATE_FORMAT()

登录后复制

函数来格式化日期成字符串，以及

STR_TO_DATE()

登录后复制

函数将字符串转换成日期。

格式化日期为字符串（DATE_FORMAT()）
```
DATE_FORMAT(date, format)
```
登录后复制
函数接受两个参数：
```
date
```
登录后复制
是你要格式化的日期字段或表达式，
```
format
```
登录后复制
是指定输出格式的字符串。

例如，假设你有一个名为
```
orders
```
登录后复制
的表，其中包含一个
```
order_date
```
登录后复制
字段，你想要将日期格式化为
```
YYYY-MM-DD
```
登录后复制
的形式：
```
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;
```
登录后复制
常用的格式化符号包括：
- ```
%Y
```
  登录后复制
  : 四位年份
- ```
%Y
```
  登录后复制
  : 两位年份
- ```
%m
```
  登录后复制
  : 两位月份 (01-12)
- ```
%d
```
  登录后复制
  : 两位日期 (01-31)
- ```
%H
```
  登录后复制
  : 24小时制小时 (00-23)
- ```
%H
```
  登录后复制
  : 12小时制小时 (01-12)
- ```
%i
```
  登录后复制
  : 分钟 (00-59)
- ```
%s
```
  登录后复制
  : 秒 (00-59)
- ```
%p
```
  登录后复制
  : AM 或 PM
你可以根据需要组合这些符号，比如
```
%Y年%m月%d日 %H:%i:%s
```
登录后复制
。
将字符串转换为日期（STR_TO_DATE()）
```
STR_TO_DATE(str, format)
```
登录后复制
函数将字符串
```
str
```
登录后复制
按照指定的
```
format
```
登录后复制
格式转换为日期。

例如，如果你的
```
orders
```
登录后复制
表中有一个
```
order_date_string
```
登录后复制
字段，存储的是
```
DD/MM/YYYY
```
登录后复制
格式的日期字符串，你想查询 2023 年 10 月份的订单：
```
SELECT * FROM orders
WHERE STR_TO_DATE(order_date_string, '%d/%m/%Y') BETWEEN '2023-10-01' AND '2023-10-31';
```
登录后复制
这里，
```
STR_TO_DATE()
```
登录后复制
将
```
order_date_string
```
登录后复制
转换为日期类型，然后就可以进行日期范围的比较了。
日期范围查询优化

在进行日期范围查询时，尽量避免在
```
WHERE
```
登录后复制
子句中对日期字段进行函数操作，这会导致无法使用索引，降低查询效率。

比格设计
比格设计是135编辑器旗下一款一站式、多场景、智能化的在线图片编辑器

124

查看详情

例如，不要这样写：
```
SELECT * FROM orders WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 10;
```
登录后复制
而应该使用日期范围：
```
SELECT * FROM orders WHERE order_date BETWEEN '2023-10-01' AND '2023-10-31';
```
登录后复制
如果需要更精确的查询，可以考虑使用时间戳（
```
TIMESTAMP
```
登录后复制
）类型，并使用 UNIX 时间戳进行比较。

为什么我的日期格式化查询总是出错？

常见错误包括：

格式字符串不匹配：
```
DATE_FORMAT()
```
登录后复制
和
```
STR_TO_DATE()
```
登录后复制
的格式字符串必须与日期数据的实际格式完全匹配，否则会返回
```
NULL
```
登录后复制
或错误的结果。仔细检查你的格式字符串是否正确。
日期数据类型错误： 确保你正在处理的是日期或日期时间类型的数据。如果字段是字符串类型，你需要先使用
```
STR_TO_DATE()
```
登录后复制
将其转换为日期类型。
时区问题： MySQL 服务器的时区设置可能会影响日期格式化的结果。确保你的应用程序和数据库服务器使用相同的时区。可以使用
```
SET time_zone = '+08:00';
```
登录后复制
设置时区。
NULL 值： 如果日期字段包含
```
NULL
```
登录后复制
值，
```
DATE_FORMAT()
```
登录后复制
会返回
```
NULL
```
登录后复制
。可以使用
```
IFNULL()
```
登录后复制
或
```
COALESCE()
```
登录后复制
函数处理
```
NULL
```
登录后复制
值。

如何在查询中使用自定义日期格式？

你可以根据自己的需求自定义日期格式。例如，要将日期格式化为 "October 26th, 2023" 的形式，可以使用以下 SQL：

SELECT DATE_FORMAT(order_date, '%M %D, %Y') AS formatted_date FROM orders;

登录后复制

注意

%d

登录后复制

格式化符号会添加日期后缀（如 1st, 2nd, 3rd, 4th）。

另外，你也可以结合使用

CASE

登录后复制

语句和

DATE_FORMAT()

登录后复制

来实现更复杂的日期格式化逻辑。例如，根据月份显示不同的文本：

SELECT
    CASE
        WHEN MONTH(order_date) = 1 THEN CONCAT('January ', DAY(order_date), 'th, ', YEAR(order_date))
        WHEN MONTH(order_date) = 2 THEN CONCAT('February ', DAY(order_date), 'th, ', YEAR(order_date))
        ...
        ELSE DATE_FORMAT(order_date, '%M %D, %Y')
    END AS formatted_date
FROM orders;

登录后复制

这种方法虽然比较繁琐，但可以实现高度定制化的日期格式。

如何优化包含日期格式化的复杂查询？

复杂查询中包含日期格式化可能会影响性能，尤其是在数据量大的情况下。优化方法包括：

避免在
WHERE
登录后复制
子句中使用函数：尽量使用日期范围查询，而不是对日期字段进行函数操作。
创建索引： 在经常用于查询的日期字段上创建索引，可以显著提高查询速度。
预先计算： 如果你需要频繁地对同一日期字段进行格式化，可以考虑创建一个新的字段来存储格式化后的日期字符串，并在数据插入或更新时进行计算。
使用缓存： 如果查询结果不经常变化，可以使用缓存技术（如 Redis）来缓存格式化后的日期数据，减少数据库的访问次数。
分析查询计划： 使用
```
EXPLAIN
```
登录后复制
命令分析查询计划，找出性能瓶颈，并进行相应的优化。比如，确保 MySQL 使用了正确的索引。