MySQL时间处理函数大全 where条件中日期转换最佳实践

在mysql中处理时间数据时，最核心的实践是避免在where条件中对索引列使用函数，以确保索引有效利用，提升查询性能。1. 应将外部输入值转换为与列匹配的格式，而非对列应用函数，例如使用str_to_date()转换输入字符串；2. 查询特定日期时应采用范围查询，如created_at >= '2023-10-26 00:00:00' and created_at < '2023-10-27 00:00:00'，以保持索引可用；3. 对于需按年、月等部分查询的场景，推荐使用生成列（generated columns）并为其建立索引，或在应用层处理及冗余存储日期部分；4. 常用日期函数包括now()、curdate()、date_format()、str_to_date()、date_add()、datediff()等，应仅在必要时用于格式化或计算，避免在where子句中作用于索引列；5. 在索引列上使用函数会导致谓词不可sargable，迫使mysql执行全表扫描，严重降低性能，因此必须坚持“列裸奔”原则，确保查询条件可利用索引进行高效查找，从而保障大数据量下的响应速度。

在MySQL中处理时间数据，尤其是在

WHERE

解决方案

我们在编写SQL查询时，特别是涉及日期时间字段的筛选，常常会不自觉地在

WHERE

DATE_FORMAT(created_at, '%Y-%m-%d') = '2023-10-26'

更优、更推荐的方案是保持索引列的“裸奔”状态，也就是不对它做任何函数处理。如果你想查询某个特定日期的数据，而你的

created_at

DATETIME

TIMESTAMP

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

这种写法让MySQL可以直接利用

created_at

'2023/10/26'

STR_TO_DATE()

SELECT *
FROM your_table
WHERE date_column = STR_TO_DATE('2023/10/26', '%Y/%m/%d');

这里，

date_column

MySQL中常用的日期时间函数有哪些，它们各自的用途是什么？

MySQL提供了非常丰富的日期时间处理函数，它们在数据查询、格式化、计算等方面都扮演着重要角色。了解并熟练使用这些函数，能帮助我们更灵活地处理时间相关的数据。

• NOW()

  登录后复制
  : 返回当前的日期和时间，格式为

  'YYYY-MM-DD HH:MM:SS'

  登录后复制
  。

  SELECT NOW(); -- 示例: '2023-10-26 10:30:00'

  登录后复制

• CURDATE()

  登录后复制
  : 返回当前日期，格式为

  'YYYY-MM-DD'

  登录后复制
  。

  SELECT CURDATE(); -- 示例: '2023-10-26'

  登录后复制

• CURTIME()

  登录后复制
  : 返回当前时间，格式为

  'HH:MM:SS'

  登录后复制
  。

  SELECT CURTIME(); -- 示例: '10:30:00'

  登录后复制

• DATE_FORMAT(date, format)

  登录后复制
  : 将日期或日期时间值格式化为字符串。这是最常用的格式化函数之一，但如前所述，不建议在

  WHERE

  登录后复制
  条件中对列使用。

  SELECT DATE_FORMAT('2023-10-26 10:30:00', '%Y年%m月%d日 %H时%i分');
  -- 结果: '2023年10月26日 10时30分'

  登录后复制

• STR_TO_DATE(string, format)

  登录后复制
  : 将字符串转换为日期或日期时间值。这个函数在处理外部传入的日期字符串时非常有用。

  SELECT STR_TO_DATE('26-10-2023', '%d-%m-%Y'); -- 结果: '2023-10-26'

  登录后复制

• DATE_ADD(date, INTERVAL expr unit)

  登录后复制
  /

  DATE_SUB(date, INTERVAL expr unit)

  登录后复制
  : 用于在日期或日期时间值上增加或减去一个时间间隔。

  unit

  登录后复制
  可以是

  DAY

  登录后复制
  ,

  MONTH

  登录后复制
  ,

  YEAR

  登录后复制
  ,

  HOUR

  登录后复制
  ,

  MINUTE

  登录后复制
  ,

  SECOND

  登录后复制
  等。

  SELECT DATE_ADD('2023-10-26', INTERVAL 7 DAY); -- 结果: '2023-11-02'
  SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR); -- 结果: 当前时间减去1小时

  登录后复制

• DATEDIFF(expr1, expr2)

  登录后复制
  : 计算两个日期之间的天数差。

  SELECT DATEDIFF('2023-10-31', '2023-10-26'); -- 结果: 5

  登录后复制

• TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

  登录后复制
  : 计算两个日期时间表达式之间的差值，单位由

  unit

  登录后复制
  指定。比

  DATEDIFF

  登录后复制
  更灵活，可以计算秒、分钟、小时等差值。

  SELECT TIMESTAMPDIFF(HOUR, '2023-10-26 10:00:00', '2023-10-26 12:30:00'); -- 结果: 2

  登录后复制

• YEAR(date)

  登录后复制
  /

  MONTH(date)

  登录后复制
  /

  DAY(date)

  登录后复制
  /

  HOUR(time)

  登录后复制
  /

  MINUTE(time)

  登录后复制
  /

  SECOND(time)

  登录后复制
  : 用于从日期或时间值中提取特定的部分。

  SELECT YEAR('2023-10-26'); -- 结果: 2023
  SELECT MONTH('2023-10-26'); -- 结果: 10

  登录后复制

这些函数构成了MySQL时间处理的基础工具集，理解它们的用途是编写高效SQL的关键。

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

这个问题，我个人认为，是MySQL优化中最容易被忽视但又最致命的坑之一。简单来说，它与数据库索引的工作原理息息相关。

当你在一个列上创建了索引，比如

created_at

created_at

WHERE created_at = '2023-10-26'

然而，一旦你引入了函数，比如

WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2023-10-26'

DATE_FORMAT(created_at, '%Y-%m-%d')

created_at

1. 进行全表扫描（Full Table Scan）：遍历表中的每一行数据。
2. 为每一行执行函数计算：对每一行的

   created_at

   登录后复制
   值都执行

   DATE_FORMAT()

   登录后复制
   函数。
3. 比较计算结果：将函数计算出的结果与你提供的

   '2023-10-26'

   登录后复制
   进行比较。

这个过程，我们称之为“索引失效”或“谓词不可Sargable”（Non-Sargable Predicate）。Sargable是一个术语，指的是那些能够利用索引进行高效查找的谓词（WHERE子句中的条件）。当你在索引列上应用函数时，这个谓词就变得不可Sargable了。在数据量小的时候，你可能感觉不到什么，但一旦表中有数十万、数百万甚至更多行数据时，这种查询的性能会急剧下降，可能从毫秒级直接跳到秒级甚至分钟级，这对于生产环境来说是灾难性的。

因此，核心原则就是：不要在

WHERE

如何在MySQL中高效地查询特定日期范围或日期部分？

理解了函数对索引的影响后，我们就能更好地设计高效的日期查询。关键在于将条件转化为可以直接利用索引的范围查询。

ViiTor实时翻译

AI实时多语言翻译专家！强大的语音识别、AR翻译功能。

116

查看详情

1. 查询特定日期（例如，某一天的数据）

如果你的日期时间列是

DATETIME

TIMESTAMP

-- 查找 2023年10月26日 00:00:00 到 2023年10月26日 23:59:59 之间的所有记录
SELECT *
FROM your_table
WHERE created_at >= '2023-10-26 00:00:00'
  AND created_at < '2023-10-27 00:00:00'; -- 注意这里是小于下一天的开始

这种写法非常高效，因为

created_at

2. 查询特定月份或年份的数据

类似地，查询某个月份或年份的数据也应该转换为范围查询：

-- 查询 2023年10月 的所有记录
SELECT *
FROM your_table
WHERE created_at >= '2023-10-01 00:00:00'
  AND created_at < '2023-11-01 00:00:00'; -- 小于下个月的开始

-- 查询 2023年 的所有记录
SELECT *
FROM your_table
WHERE created_at >= '2023-01-01 00:00:00'
  AND created_at < '2024-01-01 00:00:00';

3. 处理复杂日期部分查询（当范围查询不适用时）

有时候，你可能真的需要根据年份、月份等特定部分进行查询，例如，查找所有创建于每年的10月26日的记录。在这种情况下，简单的范围查询就很难直接满足了。

• 方案一：生成列（Generated Columns - MySQL 5.7+） 这是非常优雅且高效的解决方案。你可以创建一个虚拟列，它存储了原始日期列的某个函数计算结果，然后给这个虚拟列添加索引。

  -- 假设你的表是 `orders`，有一个 `order_time` DATETIME 列
  ALTER TABLE orders
  ADD COLUMN order_year INT AS (YEAR(order_time)) VIRTUAL,
  ADD COLUMN order_month INT AS (MONTH(order_time)) VIRTUAL,
  ADD COLUMN order_day INT AS (DAY(order_time)) VIRTUAL;
  
  -- 为这些生成列添加索引
  CREATE INDEX idx_order_year ON orders (order_year);
  CREATE INDEX idx_order_month ON orders (order_month);
  CREATE INDEX idx_order_day ON orders (order_day);

  登录后复制

  现在，你可以这样查询，而且索引是生效的：

  SELECT *
  FROM orders
  WHERE order_year = 2023 AND order_month = 10 AND order_day = 26;

  登录后复制

  VIRTUAL

  登录后复制
  生成列不占用物理存储空间，只在查询时计算；

  STORED

  登录后复制
  生成列则会占用物理空间并持久化计算结果，写入时开销大但读取更快。根据你的读写模式选择。

• 方案二：应用程序层面处理或数据冗余 如果你的MySQL版本不支持生成列，或者你觉得生成列不适合你的场景，你可以考虑在应用程序层面处理日期逻辑，或者在表中增加冗余字段（例如

  order_year

  登录后复制
  、

  order_month

  登录后复制
  ）来存储日期部分，并在数据插入/更新时同步这些字段的值。当然，这会增加数据冗余和维护成本，但能确保查询效率。

4. 转换外部输入值

再次强调，如果你从用户界面或其他系统接收到一个非标准格式的日期字符串，务必在将其用于

WHERE

STR_TO_DATE()

-- 用户输入 '26/10/2023'
SET @user_input_date_str = '26/10/2023';
SELECT *
FROM your_table
WHERE date_column = STR_TO_DATE(@user_input_date_str, '%d/%m/%Y');

这里的

date_column

总之，高效的日期时间查询策略核心在于：避免对索引列应用函数，转而使用范围查询，或者通过生成列等方式将函数结果预计算并索引。 这能确保你的数据库在处理大量时间序列数据时依然保持高性能。

以上就是MySQL时间处理函数大全 where条件中日期转换最佳实践的详细内容，更多请关注php中文网其它相关文章！