Practical MySQL & MariaDB

MySQL 日期比较与操作：完整指南，附示例与性能技巧

SQL 基礎
目次

1. 介绍

处理 MySQL 中的日期是数据库操作中最重要的环节之一。例如，在按日期汇总销售数据或在特定时间段内搜索记录时，日期比较就显得尤为关键。

本文将从 MySQL 中日期的基本操作与比较讲起，直至高级用例和性能优化技巧，旨在为各层次的用户提供帮助，无论是初学者还是中级开发者。

2. MySQL 日期数据类型概览

日期数据类型及其特性

MySQL 提供以下三种主要的日期数据类型。下面对每种类型进行简要说明。

  1. DATE
  • 存储值：年-月-日（YYYY-MM-DD）
  • 特性：不包含时间信息，适用于管理单纯的日期。
  • 使用场景：生日、截止日期。
  1. DATETIME
  • 存储值：年-月-日 与 时间（YYYY-MM-DD HH:MM:SS）
  • 特性：包含时间信息，适用于记录精确的时间戳。
  • 使用场景：创建时间戳、最后更新时间戳。
  1. TIMESTAMP
  • 存储值：年-月-日 与 时间（YYYY-MM-DD HH:MM:SS）
  • 特性：受时区影响，适用于跨地区管理日期和时间。
  • 使用场景：日志数据、交易记录。

如何选择合适的数据类型

  • 如果不需要时间，选择 DATE。
  • 如果需要时间，根据应用的时区需求选择 DATETIME 或 TIMESTAMP。

示例查询

下面给出使用每种数据类型的示例。

CREATE TABLE events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE,
    event_datetime DATETIME,
    event_timestamp TIMESTAMP
);

3. 如何比较日期

使用基本比较运算符

在 MySQL 中，以下运算符用于日期比较。

  1. =（等于）
  • 检索与指定日期匹配的记录。
    SELECT * FROM events WHERE event_date = '2025-01-01';
  1. > / <（大于 / 小于）
  • 查找在指定日期之前或之后的记录。
    SELECT * FROM events WHERE event_date > '2025-01-01';
  1. <= / >=（小于等于 / 大于等于）
  • 在包含指定日期的范围内进行搜索。
    SELECT * FROM events WHERE event_date <= '2025-01-10';

使用 BETWEEN 进行范围查询

BETWEEN 运算符可以轻松指定日期范围。

SELECT * FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';

重要提示：

  • BETWEEN 包含起始值和结束值，请确保不会无意中将所需数据排除在范围之外。

4. 计算日期差异

使用 DATEDIFF 函数

DATEDIFF() 函数计算两个日期之间的差异（天数）。

SELECT DATEDIFF('2025-01-10', '2025-01-01') AS days_difference;

结果：

  • 9 天

使用 TIMESTAMPDIFF 函数

TIMESTAMPDIFF() 函数可按年、月、天或小时等特定单位计算差异。

SELECT TIMESTAMPDIFF(MONTH, '2025-01-01', '2025-12-31') AS months_difference;

结果：

  • 11 个月

5. 日期的加减

使用 INTERVAL 子句进行日期操作

在 MySQL 中，可以使用 INTERVAL 子句轻松对日期进行加减操作，从而创建检索特定时间段前后日期的查询。

向日期添加时间

使用 INTERVAL 向日期添加时间的示例：

SELECT DATE_ADD('2025-01-01', INTERVAL 7 DAY) AS plus_seven_days;

结果：
2025-01-08

从日期减去时间

可以使用相同的方式通过 INTERVAL 从日期中减去时间：

SELECT DATE_SUB('2025-01-01', INTERVAL 1 MONTH) AS minus_one_month;

结果:
2024-12-01

用例：提醒系统

以下是一个示例查询，用于检索恰好七天前发生的事件，可用于发送自动提醒通知。

SELECT event_name, event_date 
FROM events 
WHERE event_date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

基于当前日期的计算

  • CURDATE() : 返回当前日期（YYYY-MM-DD）。
  • NOW() : 返回当前日期和时间（YYYY-MM-DD HH:MM:SS）.

示例：计算今天起一周后的日期。

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week;

6. 实用查询示例

检索特定日期的记录

检索与指定日期匹配的事件。

SELECT * 
FROM events 
WHERE event_date = '2025-01-01';

检索日期范围内的数据

示例：在一周范围内搜索事件。

SELECT * 
FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-07';

按日期聚合数据

此查询统计每个月注册的事件数量。

SELECT MONTH(event_date) AS event_month, COUNT(*) AS total_events 
FROM events 
GROUP BY MONTH(event_date);

示例结果:

event_monthtotal_events
110
215

7. 性能优化

使用索引的高效搜索

在日期列上设置索引可以显著提升查询性能。

创建索引

以下 SQL 在 event_date 列上创建索引。

CREATE INDEX idx_event_date ON events(event_date);

检查索引的效果

您可以使用 EXPLAIN 检查查询执行计划。

EXPLAIN SELECT * 
FROM events 
WHERE event_date = '2025-01-01';

使用函数时的重要注意事项

在 WHERE 子句中使用函数可能会导致索引失效。

错误示例

在以下查询中，DATE() 函数阻止了索引的使用。

SELECT * 
FROM events 
WHERE DATE(event_date) = '2025-01-01';

改进示例

建议直接比较值，而不使用函数。

SELECT * 
FROM events 
WHERE event_date >= '2025-01-01' 
  AND event_date < '2025-01-02';

8. 常见问题解答 (FAQ)

Q1: DATE 与 DATETIME 有何区别？

  • A1:
  • DATE：仅存储日期（ YYYY-MM-DD ）。当不需要时间信息时使用。
  • DATETIME：同时存储日期和时间（ YYYY-MM-DD HH:MM:SS ）。当需要事件的精确时间戳时使用。

示例：

CREATE TABLE examples (
    example_date DATE,
    example_datetime DATETIME
);

Q2: 使用 BETWEEN 指定日期范围时需要注意什么？

  • A2:
  • 由于 BETWEEN 包含起始和结束日期，如果结束日期未包含时间值，可能无法检索到所有预期的记录。

示例：

-- This query may not retrieve records such as "2025-01-31 23:59:59"
SELECT * 
FROM events 
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';

改进：
显式将结束时间设为 23:59:59，或使用忽略时间部分的比较方式。

SELECT * 
FROM events 
WHERE event_date >= '2025-01-01' AND event_date < '2025-02-01';

Q3: 如何处理时区差异？

  • A3： 在 MySQL 中，TIMESTAMP 类型受时区影响。相反，DATETIME 类型存储固定值，不受时区影响。

检查当前时区设置：

SHOW VARIABLES LIKE 'time_zone';

更改时区设置：

SET time_zone = '+09:00'; -- Japan Standard Time (JST)

Q4: 如何检索过去 30 天的数据？

  • A4： 您可以将 CURDATE() 或 NOW() 与 INTERVAL 结合使用，以检索过去 30 天的数据。

示例：

SELECT * 
FROM events 
WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Q5: 如何提升日期比较的性能？

  • A5：
  • 创建索引： 在日期列上设置索引。
  • 避免使用函数： 在 WHERE 子句中使用函数可能会禁用索引，因此请改用直接比较。

9. 摘要

本文要点

本文对 MySQL 中的日期操作和比较技术进行了全面说明。关键要点如下：

  1. 了解日期数据类型（DATE、DATETIME、TIMESTAMP）的特性及正确用法。
  2. 基本比较方法（ =、>、<、BETWEEN 等）。
  3. 计算日期差异（ DATEDIFF()、TIMESTAMPDIFF()）。
  4. 通过索引和优化查询设计提升性能。

我们希望本指南能帮助您高效处理 MySQL 中的日期操作，并为实际应用开发出实用、优化的查询。