MySQL日期与时间戳互转方法 where条件查询优化方案

正确做法是转换查询条件值而非索引列，避免在WHERE中对索引列使用函数，应使用FROM_UNIXTIME或UNIX_TIMESTAMP转换比较值以利用索引，提升查询效率。

在MySQL中，日期与时间戳之间的转换其实非常直接，通常通过

UNIX_TIMESTAMP()

FROM_UNIXTIME()

WHERE

解决方案

日期时间与时间戳的互转：

• 日期时间转时间戳： 使用

  UNIX_TIMESTAMP(date_expression)

  登录后复制
  。例如，

  SELECT UNIX_TIMESTAMP('2023-10-26 10:30:00');

  登录后复制
  会返回一个整数时间戳。如果你有一个

  DATETIME

  登录后复制
  或

  TIMESTAMP

  登录后复制
  类型的列，直接传入即可。
• 时间戳转日期时间： 使用

  FROM_UNIXTIME(unix_timestamp)

  登录后复制
  。例如，

  SELECT FROM_UNIXTIME(1678886400);

  登录后复制
  会返回一个

  DATETIME

  登录后复制
  格式的字符串，通常是

  YYYY-MM-DD HH:MM:SS

  登录后复制
  。

WHERE

避免在

WHERE

created_at

DATETIME

WHERE UNIX_TIMESTAMP(created_at) > 1678886400

示例：

假设你有一个

orders

created_at

• 错误示范 (会使索引失效):

  SELECT * FROM orders WHERE UNIX_TIMESTAMP(created_at) > 1678886400;

  登录后复制

  这条查询会让MySQL对

  created_at

  登录后复制
  列的每一行都执行

  UNIX_TIMESTAMP()

  登录后复制
  函数，然后进行比较，这等同于全表扫描。

• 正确示范 (利用索引):

  SELECT * FROM orders WHERE created_at > FROM_UNIXTIME(1678886400);

  登录后复制

  这里，我们把时间戳

  1678886400

  登录后复制
  转换成

  DATETIME

  登录后复制
  格式，然后与

  created_at

  登录后复制
  列直接比较。由于

  created_at

  登录后复制
  列没有被函数处理，MySQL可以有效地使用其上的索引进行快速查找。

同样的道理，如果你有一个

timestamp_col

• 错误示范 (会使索引失效):

  SELECT * FROM my_table WHERE FROM_UNIXTIME(timestamp_col) > '2023-03-15 00:00:00';

  登录后复制

• 正确示范 (利用索引):

  SELECT * FROM my_table WHERE timestamp_col > UNIX_TIMESTAMP('2023-03-15 00:00:00');

  登录后复制

为什么在WHERE条件中使用函数会影响查询性能？

这其实是个经典问题，也是很多初学者甚至经验丰富的开发者偶尔会忽略的细节。简单来说，当你对一个被索引的列应用函数时，MySQL的查询优化器就无法利用该列上的索引了。索引，尤其是B-tree索引，其核心在于数据是有序存储的。例如，一个日期列的索引是按照日期大小排序的。当你用

UNIX_TIMESTAMP(date_column)

date_column

想象一下，你有一本按照姓氏首字母排序的电话簿（这就是索引），现在你突然想找一个名字，但你只知道这个名字的MD5哈希值。你不可能通过哈希值去查这本按姓氏排序的电话簿，对吧？你只能把电话簿里所有的名字都计算一遍MD5哈希值，然后逐个比较。这就是全表扫描的代价。MySQL在遇到这种情况时，不得不放弃使用索引，转而执行全表扫描，然后对每一行数据执行你的函数，再进行比较，这效率自然就下去了。特别是在数据量很大的时候，这种性能损耗是灾难性的。

如何正确利用索引优化日期/时间戳的范围查询？

除了上面提到的转换比较值，针对日期/时间戳的范围查询，我们还有一些非常实用的策略来确保索引的有效利用。我的经验告诉我，范围查询是这类数据类型最常见的查询模式，所以掌握好这部分至关重要。

首先，继续强调核心原则：确保索引列本身不被函数处理。

对于日期范围查询，

BETWEEN

美间AI

美间AI：让设计更简单

45

查看详情

示例：查询某个日期区间内的订单

假设我们要查询2023年10月1日到2023年10月31日之间的所有订单。

• 使用

  DATETIME

  登录后复制
  或

  TIMESTAMP

  登录后复制
  列 (推荐):

  SELECT *
  FROM orders
  WHERE created_at BETWEEN '2023-10-01 00:00:00' AND '2023-10-31 23:59:59';

  登录后复制

  或者更精确地处理日期边界：

  SELECT *
  FROM orders
  WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-11-01 00:00:00';

  登录后复制

  这种写法对于

  DATETIME

  登录后复制
  和

  TIMESTAMP

  登录后复制
  类型非常友好，并且能够充分利用

  created_at

  登录后复制
  列上的索引。

• 使用

  INT

  登录后复制
  或

  BIGINT

  登录后复制
  存储时间戳的列:

  SELECT *
  FROM my_table
  WHERE timestamp_col BETWEEN UNIX_TIMESTAMP('2023-10-01 00:00:00') AND UNIX_TIMESTAMP('2023-10-31 23:59:59');

  登录后复制

  同样，也可以使用

  >=

  登录后复制
  和

  <

  登录后复制
  的组合：

  SELECT *
  FROM my_table
  WHERE timestamp_col >= UNIX_TIMESTAMP('2023-10-01 00:00:00') AND timestamp_col < UNIX_TIMESTAMP('2023-11-01 00:00:00');

  登录后复制

  这两种方法都将日期字符串转换为时间戳，确保了

  timestamp_col

  登录后复制
  列在比较时保持其原始的、可被索引利用的形式。

此外，如果你的日期列是

DATE

SELECT * FROM daily_records WHERE record_date = '2023-10-26';

或者查询一个日期范围：

SELECT * FROM daily_records WHERE record_date BETWEEN '2023-10-01' AND '2023-10-31';

这些操作都能很好地利用

record_date

除了索引，还有哪些策略可以提升日期/时间相关查询的效率？

仅仅依赖索引有时还不够，尤其是在处理海量数据或面临复杂查询场景时。我个人在实践中，除了优化索引的使用，还会考虑以下几点来进一步提升日期/时间相关查询的效率：

1. 数据分区 (Partitioning): 当你的表非常庞大，比如历史数据按日期持续增长，那么数据分区就成了非常有力的工具。你可以根据日期字段（例如

created_at

比如，一个按年份分区的

orders

2. 覆盖索引 (Covering Index): 如果你的查询只需要从表中获取日期/时间相关的列以及其他几个被索引的列，那么创建一个覆盖索引会非常高效。覆盖索引是指一个索引包含了查询所需的所有列，这样MySQL就无需回表（即无需访问实际的数据行）就能获取所有需要的数据。

例如，如果你经常查询

created_at

user_id

user_id

(created_at, user_id)

3. 适当的冗余字段 (Denormalization): 在某些极端情况下，为了优化查询，你可能会考虑在表中增加一些冗余的日期相关字段。比如，如果你的

created_at

DATETIME

created_year

created_month

created_day

这是一种典型的空间换时间策略。虽然增加了存储空间和数据写入时的维护成本（你需要在插入或更新时同步这些冗余字段），但对于读密集型应用来说，它能极大地简化查询并提高性能，因为它避免了在查询时对

created_at

YEAR()

MONTH()

DAY()

这些策略都是在索引优化基础之上，根据具体业务场景和数据量级来选择性使用的。没有银弹，但结合起来，往往能解决大部分日期/时间查询的性能瓶颈。

以上就是MySQL日期与时间戳互转方法 where条件查询优化方案的详细内容，更多请关注php中文网其它相关文章！