MySQL时间戳与日期互转实战 where条件查询效率提升方法-mysql教程-PHP中文网

正确做法是将查询条件转换为与索引列相同的数据类型，避免在索引列上使用函数，从而确保索引有效，提升查询性能。

mysql时间戳与日期互转实战 where条件查询效率提升方法

MySQL中处理时间戳和日期格式的互转，核心在于使用

UNIX_TIMESTAMP()

登录后复制

和

FROM_UNIXTIME()

登录后复制

这两个函数。但更关键的是，在进行

WHERE

登录后复制

条件查询时，我们必须避免在索引列上直接使用这些转换函数，否则会导致索引失效，查询效率直线下降。正确的做法是，将查询条件（输入值）转换为与列数据类型一致的格式，从而让索引能派上用场，显著提升查询性能。

解决方案

在MySQL中，时间戳（通常是

INT

登录后复制

或

BIGINT

登录后复制

类型，存储的是自1970年1月1日00:00:00 UTC以来的秒数）和日期/时间（

DATE

登录后复制

、

DATETIME

登录后复制

、

TIMESTAMP

登录后复制

类型）之间的转换是日常操作。

日期/时间转时间戳： 使用
```
UNIX_TIMESTAMP(date_expression)
```
登录后复制
。例如：
```
SELECT UNIX_TIMESTAMP('2023-10-26 10:30:00');
```
登录后复制
会返回
```
1698306600
```
登录后复制
。或者针对
```
DATETIME
```
登录后复制
列：
```
SELECT UNIX_TIMESTAMP(create_time) FROM your_table;
```
登录后复制
时间戳转日期/时间： 使用
```
FROM_UNIXTIME(unix_timestamp [, format])
```
登录后复制
。例如：
```
SELECT FROM_UNIXTIME(1698306600);
```
登录后复制
会返回
```
2023-10-26 10:30:00
```
登录后复制
。你也可以指定格式：
```
SELECT FROM_UNIXTIME(1698306600, '%Y-%m-%d %H:%i:%s');
```
登录后复制

实战中，我们经常会遇到这样的场景：数据库里有个字段

record_time

登录后复制

，存储的是

INT

登录后复制

类型的时间戳，但业务方希望按日期范围查询，比如查询2023年10月26日当天的数据。

错误且低效的做法：

SELECT *
FROM your_table
WHERE FROM_UNIXTIME(record_time, '%Y-%m-%d') = '2023-10-26';

登录后复制

或者更常见的范围查询：

SELECT *
FROM your_table
WHERE FROM_UNIXTIME(record_time) BETWEEN '2023-10-26 00:00:00' AND '2023-10-26 23:59:59';

登录后复制

这样的查询，即使

record_time

登录后复制

列上有索引，MySQL查询优化器也无法有效利用这个索引。因为

FROM_UNIXTIME()

登录后复制

函数作用在了列上，数据库需要对表中的每一行数据都执行这个函数，然后才能进行比较，这本质上就是一次全表扫描。

正确且高效的做法：

将查询条件（日期字符串）转换为时间戳，然后用时间戳进行比较。

SELECT *
FROM your_table
WHERE record_time >= UNIX_TIMESTAMP('2023-10-26 00:00:00')
  AND record_time <= UNIX_TIMESTAMP('2023-10-26 23:59:59');

登录后复制

这样，

record_time

登录后复制

列上的索引就能被充分利用，查询效率会得到质的提升。这背后其实藏着一个数据库优化的黄金法则：永远不要在
WHERE
登录后复制
子句的索引列上使用函数。

为什么在WHERE子句中直接转换日期/时间戳会拖慢查询？

这事儿吧，说起来简单，做起来就容易踩坑。我们都知道，给数据库表加索引是为了加快查询速度，特别是针对

WHERE

登录后复制

子句中的条件。索引就像一本书的目录，让你能快速找到想要的内容，而不是一页一页地翻。

但当你像这样写SQL的时候：

WHERE FROM_UNIXTIME(indexed_column) = 'some_date'

登录后复制

，你就等于告诉数据库：“嘿，别看目录了，你得把这本书每一页的内容都读一遍，然后把每一页的日期都转换一遍，最后再看看是不是我要的日期。” 这就是所谓的“索引失效”或者“全表扫描”。

原因很简单：数据库的索引是建立在原始列值上的。当你对列值应用了一个函数，比如

FROM_UNIXTIME()

登录后复制

，数据库就无法直接使用索引树去查找匹配的值了。它不知道

FROM_UNIXTIME(indexed_column)

登录后复制

的结果会是什么，因为它没有为这个函数的结果建立索引。所以，它唯一的选择就是逐行计算函数结果，然后进行比较。对于小表来说可能感觉不出来，但数据量一上去，几百万、几千万行，那真是灾难性的慢。

提升查询效率的实战技巧：转换查询值而非列

这个技巧说白了，就是“以其人之道还治其人之身”，但不是对着索引列下手。我们要做的是，确保

WHERE

登录后复制

子句中，索引列始终保持“纯净”，不被任何函数污染。所有的转换操作，都应该施加在你的查询条件上。

来看几个具体的例子，假设我们有一个

orders

登录后复制

表，里面有个

created_at

登录后复制

字段：

叮当好记-AI音视频转图文

AI音视频转录与总结，内容学习效率 x10！

193

查看详情

场景一：

created_at

登录后复制

是
INT
登录后复制
类型（存储时间戳），你想查询某一天的订单。

低效写法（避免）：

-- 这样写，created_at上的索引就废了
SELECT order_id, created_at
FROM orders
WHERE FROM_UNIXTIME(created_at, '%Y-%m-%d') = '2023-10-26';

登录后复制

高效写法（推荐）：

-- 将日期字符串转换为时间戳范围
SELECT order_id, created_at
FROM orders
WHERE created_at >= UNIX_TIMESTAMP('2023-10-26 00:00:00')
  AND created_at < UNIX_TIMESTAMP('2023-10-27 00:00:00'); -- 注意这里用 < 下一天的0点，更精确

登录后复制

或者如果你只关心某个精确的时间点：

SELECT order_id, created_at
FROM orders
WHERE created_at = UNIX_TIMESTAMP('2023-10-26 10:30:00');

登录后复制

场景二：

created_at

登录后复制

是
DATETIME
登录后复制
类型，但你得到了一个时间戳作为查询条件。

低效写法（避免）：

-- 这样写，created_at上的索引也废了
SELECT order_id, created_at
FROM orders
WHERE UNIX_TIMESTAMP(created_at) = 1698306600;

登录后复制

高效写法（推荐）：

-- 将时间戳转换为DATETIME类型进行比较
SELECT order_id, created_at
FROM orders
WHERE created_at = FROM_UNIXTIME(1698306600);

登录后复制

对于范围查询：

SELECT order_id, created_at
FROM orders
WHERE created_at >= FROM_UNIXTIME(1698306600) -- 2023-10-26 10:30:00
  AND created_at < FROM_UNIXTIME(1698307200); -- 2023-10-26 10:40:00

登录后复制

通过这些例子可以看出，核心思路就是：让数据库列保持原样，让外部的查询条件去适应列的类型。 这样，数据库的优化器就能愉快地使用你为该列创建的索引了。

除了转换，还有哪些优化WHERE条件查询的通用策略？

说实话，时间戳与日期互转只是冰山一角，提升

WHERE

登录后复制

条件查询效率的方法还有不少，它们共同构成了数据库优化的基石。

1. 合理使用索引： 这几乎是老生常谈，但却是最重要的。

单列索引： 确保你经常用于
```
WHERE
```
登录后复制
条件的列都有索引。
复合索引： 当你的
```
WHERE
```
登录后复制
条件中经常出现多个列的组合查询时（比如
```
WHERE status = 'active' AND user_id = 123
```
登录后复制
），考虑创建复合索引（
```
INDEX (status, user_id)
```
登录后复制
）。需要注意的是，复合索引的顺序很重要，通常将区分度高（唯一值多）的列放在前面，或者将最常用于等值查询的列放在前面。
覆盖索引： 如果你的查询只需要索引中的列，那么MySQL可以直接从索引中获取数据，而不需要回表（访问实际的数据行），这会大大加快查询速度。比如
```
SELECT user_id, status FROM users WHERE status = 'active'
```
登录后复制
，如果有一个
```
INDEX (status, user_id)
```
登录后复制
，那么这个查询就可能被覆盖。

2. 选择正确的数据类型： 数据类型对查询效率和存储空间都有影响。

精确匹配： 比如存储IP地址，用
```
INT UNSIGNED
```
登录后复制
比
```
VARCHAR
```
登录后复制
效率更高，因为整数比较更快，占用空间也小。
日期/时间类型： 除非有特殊需求（如需要存储毫秒级精度且MySQL版本不支持），
```
DATETIME
```
登录后复制
或
```
TIMESTAMP
```
登录后复制
通常优于
```
VARCHAR
```
登录后复制
存储日期字符串。它们有专门的日期函数支持，且占用空间固定。

3. 避免全表扫描的操作： 除了前面提到的在索引列上使用函数，还有一些操作也容易导致全表扫描：

LIKE '%keyword'
登录后复制
：如果你的
```
LIKE
```
登录后复制
查询以通配符开头，索引通常无法使用。尽量使用
```
LIKE 'keyword%'
```
登录后复制
。
OR
登录后复制
条件：某些情况下，
```
OR
```
登录后复制
条件会导致索引失效。考虑使用
```
UNION ALL
```
登录后复制
来拆分查询，或者确保
```
OR
```
登录后复制
两边的条件都有索引且优化器能有效利用。
NOT IN
登录后复制
和
!=
登录后复制
：这些操作有时也可能导致索引失效。在可能的情况下，尝试转换为
```
IN
```
登录后复制
或
```
BETWEEN
```
登录后复制
等正向查询。
隐式类型转换： 如果你用一个字符串去比较一个数字列，MySQL可能会进行隐式转换，这同样可能导致索引失效。确保比较两边的数据类型一致。

4. 优化子查询和连接：

EXISTS
登录后复制
vs
IN
登录后复制
：在某些情况下，
```
EXISTS
```
登录后复制
比
```
IN
```
登录后复制
更高效，尤其当子查询返回大量结果时。
连接顺序： MySQL的优化器会尝试找到最佳的连接顺序，但有时手动优化（比如使用
```
STRAIGHT_JOIN
```
登录后复制
）或确保小表先连接大表可以提升性能。
避免笛卡尔积： 确保所有连接都有正确的
```
ON
```
登录后复制
条件，避免产生过大的中间结果集。