MySQL日期函数使用指南 where查询时间戳转换格式教程-mysql教程-PHP中文网

正确使用MySQL日期函数和数据类型可提升查询效率。应优先用UNIX_TIMESTAMP和FROM_UNIXTIME进行时间戳转换，DATE_FORMAT和STR_TO_DATE处理格式化；在WHERE子句中避免对列使用函数，以防索引失效；推荐使用DATETIME存储绝对时间，统一将时间转为UTC存储以应对跨时区问题，确保数据一致性与查询性能。

mysql日期函数使用指南 where查询时间戳转换格式教程

MySQL在处理日期和时间戳时，尤其是在

WHERE

登录后复制

子句中进行查询和格式转换，核心在于理解其内置函数和数据类型。正确运用

FROM_UNIXTIME

登录后复制

、

UNIX_TIMESTAMP

登录后复制

、

DATE_FORMAT

登录后复制

等，能让你高效且准确地筛选数据，同时避免常见的性能陷阱。这不仅是语法层面的掌握，更是对数据存储和查询优化逻辑的理解。

解决方案

在MySQL中，日期和时间戳的转换与查询是日常操作。我们主要围绕时间戳与日期时间的互转、日期格式化以及在

WHERE

登录后复制

子句中的高效查询来展开。

1. 时间戳与日期时间的互转

将日期时间转换为时间戳：
```
UNIX_TIMESTAMP(date)
```
登录后复制
这个函数会返回一个Unix时间戳（自1970年1月1日00:00:00 UTC以来的秒数）。例如：
```
SELECT UNIX_TIMESTAMP('2023-10-26 10:30:00');
```
登录后复制
可能会返回
```
1698306600
```
登录后复制
。
将时间戳转换为日期时间：
```
FROM_UNIXTIME(unix_timestamp[, format])
```
登录后复制
这是
```
UNIX_TIMESTAMP()
```
登录后复制
的反向操作。你可以选择性地指定输出格式。例如：
```
SELECT FROM_UNIXTIME(1698306600);
```
登录后复制
返回
```
2023-10-26 10:30:00
```
登录后复制
。如果需要特定格式：
```
SELECT FROM_UNIXTIME(1698306600, '%Y年%m月%d日 %H:%i:%s');
```
登录后复制
返回
```
2023年10月26日 10:30:00
```
登录后复制
。

2. 日期格式化与字符串解析

格式化日期为字符串：
```
DATE_FORMAT(date, format)
```
登录后复制
这个函数非常灵活，可以把任何日期或日期时间值格式化成你想要的字符串形式。例如：
```
SELECT DATE_FORMAT('2023-10-26 10:30:00', '%Y/%m/%d %H:%i');
```
登录后复制
返回
```
2023/10/26 10:30
```
登录后复制
。
将字符串解析为日期：
```
STR_TO_DATE(string, format)
```
登录后复制
当你从外部导入数据，或者需要将特定格式的日期字符串转换为MySQL能识别的日期类型时，这个函数就派上用场了。例如：
```
SELECT STR_TO_DATE('26-10-2023 10:30:00', '%d-%m-%Y %H:%i:%s');
```
登录后复制
返回
```
2023-10-26 10:30:00
```
登录后复制
。

WHERE

登录后复制

子句中的日期查询

在

WHERE

登录后复制

子句中进行日期查询，尤其是范围查询，是常见的需求。这里的关键是确保查询效率，尽量让索引发挥作用。

精确到天的查询（针对
```
DATETIME
```
登录后复制
或
DATE
登录后复制
类型）：如果你只想查询某一天的所有记录，最直接的方法是：
```
SELECT * FROM your_table WHERE your_datetime_column >= '2023-10-26 00:00:00' AND your_datetime_column < '2023-10-27 00:00:00';
```
登录后复制
这种写法对索引非常友好，推荐使用。当然，如果你的列本身就是
```
DATE
```
登录后复制
类型，直接
```
WHERE your_date_column = '2023-10-26';
```
登录后复制
即可。
针对时间戳字段的范围查询： 如果你的日期存储为
```
UNIX_TIMESTAMP
```
登录后复制
（整数类型），那么查询时也应该将比较值转换为时间戳：
```
SELECT * FROM your_table WHERE timestamp_column >= UNIX_TIMESTAMP('2023-10-26 00:00:00') AND timestamp_column < UNIX_TIMESTAMP('2023-10-27 00:00:00');
```
登录后复制
或者，如果你知道具体的秒数，直接用数字比较性能会更好：
```
SELECT * FROM your_table WHERE timestamp_column >= 1698307200 AND timestamp_column < 1698393600;
```
登录后复制
(这里1698307200是2023-10-26 00:00:00的时间戳，1698393600是2023-10-27 00:00:00的时间戳)。
避免在列上使用函数进行查询： 一个常见的错误是在
```
WHERE
```
登录后复制
子句的列上直接使用日期函数，比如：
```
SELECT * FROM your_table WHERE DATE(your_datetime_column) = '2023-10-26';
```
登录后复制
或者针对时间戳：
```
SELECT * FROM your_table WHERE FROM_UNIXTIME(timestamp_column, '%Y-%m-%d') = '2023-10-26';
```
登录后复制
这些写法虽然能得到正确结果，但会使得MySQL无法使用
```
your_datetime_column
```
登录后复制
或
```
timestamp_column
```
登录后复制
上的索引，导致全表扫描，性能极差。

在

WHERE

登录后复制

子句中进行日期转换会影响性能吗？

是的，绝大多数情况下，在

WHERE

登录后复制

子句的列上使用函数进行日期转换会严重影响查询性能。这是一个非常常见的性能陷阱，我看到很多新手甚至是一些经验不足的开发者都会不小心踩进去。

简单来说，当你在

WHERE

登录后复制

子句的左侧（也就是你正在查询的那个列）应用任何函数时，MySQL的优化器就很难使用该列上已有的索引了。它会怎么做呢？它不得不对表中的每一行数据都执行一遍你写的那个函数计算，然后把计算结果拿来和你的查询条件进行比较。这本质上就是一次全表扫描（Full Table Scan），即使你的表有几十万、上百万甚至上亿条数据，它也得老老实实地一行一行算，一行一行比。这就像你在一个没有目录的图书馆里找一本书，你得把每一本书都翻开看一眼书名才知道是不是你要找的。

举个例子，假设你有一个

orders

登录后复制

表，里面有个

order_time

登录后复制

字段是

DATETIME

登录后复制

类型，并且上面有索引。如果你写：

SELECT * FROM orders WHERE DATE(order_time) = '2023-10-26';

登录后复制

MySQL会把

orders

登录后复制

表里每一行的

order_time

登录后复制

都通过

DATE()

登录后复制

函数转换成日期，然后再和

'2023-10-26'

登录后复制

比较。这个过程，索引是帮不上忙的。

而如果你改成这样：

SELECT * FROM orders WHERE order_time >= '2023-10-26 00:00:00' AND order_time < '2023-10-27 00:00:00';

登录后复制

这时，

order_time

登录后复制

列本身没有被函数处理，MySQL可以直接利用

order_time

登录后复制

上的索引进行范围查找，效率会高出几个数量级。它就像在图书馆里直接根据书名首字母的索引找到对应的书架区域，然后只在那一小块区域里查找。

当然，也有一些特殊情况或者说折衷方案。比如，如果你的数据量非常小，或者这个查询不频繁且对性能要求不高，那么偶尔使用函数可能问题不大。但对于核心业务查询或大数据量表，这绝对是需要规避的。在MySQL 8.0及更高版本中，可以考虑使用函数索引（Functional Index）或虚拟列（Generated Column）来解决这类问题，但这需要提前规划和额外的维护成本，通常不如直接改写查询条件来得简单有效。

吉卜力风格图片在线生成

将图片转换为吉卜力艺术风格的作品

121

查看详情

MySQL中的日期时间类型（DATE, DATETIME, TIMESTAMP）该怎么选？

在MySQL里，处理日期和时间，我们主要会遇到三种数据类型：

DATE

登录后复制

、

DATETIME

登录后复制

和

TIMESTAMP

登录后复制

。它们各自有不同的特点和适用场景，理解这些差异对于设计高效且准确的数据库至关重要。

DATE： 顾名思义，它只存储日期部分，格式是
```
'YYYY-MM-DD'
```
登录后复制
。如果你只需要记录某年某月某日，而不需要精确到小时、分钟、秒的时间信息，
```
DATE
```
登录后复制
就是最合适的选择。比如记录一个用户的生日、商品的生产日期或者某个事件发生的日期。它占3个字节。
DATETIME： 这个类型存储日期和时间，格式是
```
'YYYY-MM-DD HH:MM:SS'
```
登录后复制
。它的范围非常广，从
```
'1000-01-01 00:00:00'
```
登录后复制
到
```
'9999-12-31 23:59:59'
```
登录后复制
。
```
DATETIME
```
登录后复制
存储的是一个绝对值，它不受MySQL服务器时区设置的影响。也就是说，你存进去是什么时间，取出来就是什么时间。它占用8个字节。
TIMESTAMP：
```
TIMESTAMP
```
登录后复制
也存储日期和时间，格式和
```
DATETIME
```
登录后复制
一样。但它的范围相对较窄，从
```
'1970-01-01 00:00:01' UTC
```
登录后复制
到
```
'2038-01-19 03:14:07' UTC
```
登录后复制
。
```
TIMESTAMP
```
登录后复制
最独特的特性是它的时区感知能力：当你存储一个
```
TIMESTAMP
```
登录后复制
值时，MySQL会自动将其从当前会话的时区转换为UTC（协调世界时）进行存储；当你检索它时，又会自动从UTC转换回当前会话的时区。它占用4个字节。此外，
```
TIMESTAMP
```
登录后复制
字段还可以设置
```
ON UPDATE CURRENT_TIMESTAMP
```
登录后复制
等属性，用于自动更新记录的修改时间。

怎么选？

这真的是一个需要根据具体业务场景来判断的问题。

只关心日期，不关心时间： 毫无疑问，选
```
DATE
```
登录后复制
。简单明了，节省空间。
需要精确到秒的时间，且不希望数据库自动处理时区，或者你的应用本身就统一处理时区逻辑： 选
```
DATETIME
```
登录后复制
。我个人在很多业务场景下更倾向于使用
```
DATETIME
```
登录后复制
。因为它存储的是“所见即所得”的绝对时间，不会因为服务器或会话时区设置的改变而出现意料之外的结果。如果涉及到跨时区，我宁愿在应用层统一将时间转换为UTC后存储，读取时再转换回用户所在时区，这样逻辑更清晰，也避免了
```
TIMESTAMP
```
登录后复制
的2038年问题。
需要记录事件发生的时间，并且希望MySQL能自动处理时区转换，或者需要利用其自动更新的特性： 选
```
TIMESTAMP
```
登录后复制
。但你必须非常清楚它的时区转换机制，并且要考虑到2038年溢出问题（虽然现在看来还很遥远，但对于需要长期运行的系统，这是个隐患）。它的小体积（4字节）在某些极端场景下也是一个优势。

我的经验是，对于大多数内部系统或者不涉及复杂国际化时区转换的业务，

DATETIME

登录后复制

通常是一个安全且直观的选择。如果你真的需要处理多时区，我更倾向于在应用层统一将所有时间转换为UTC并以

DATETIME

登录后复制

存储，或者明确地将时区信息也存储下来，而不是完全依赖

TIMESTAMP

登录后复制

的自动转换特性。

如何处理跨时区日期数据？

处理跨时区日期数据是构建全球化应用时一个绕不开的难题，它远不止是简单地存取一个日期那么简单，涉及到数据的一致性、准确性和用户体验。我的看法是，核心挑战在于确保无论用户来自哪个时区，他们看到的时间都是对的，并且数据在存储层保持统一和无歧义。

这里有几种常见的策略，每种都有其优缺点：

统一存储UTC时间（最推荐） 这是我个人最推崇的做法。无论用户在哪个时区提交数据，在写入数据库之前，都将该时间点转换为UTC时间（协调世界时）进行存储。当你从数据库中取出数据时，再将UTC时间转换回用户当前会话所在的时区进行显示。
- 优点： 数据在数据库层面是统一且无歧义的，避免了时区转换的混乱。所有时间点都有一个全球统一的基准。这

以上就是MySQL日期函数使用指南 where查询时间戳转换格式教程的详细内容，更多请关注php中文网其它相关文章！