MySQL函数：`EXTRACT()`从日期中提取特定的部分，如年、月、日或小时。

MySQL日期提取函数：EXTRACT() 的深度解析与应用

大家好，今天我们深入探讨 MySQL 中一个非常实用且强大的日期时间函数：EXTRACT()。 它可以从日期或日期时间表达式中提取特定的部分，例如年、月、日、小时、分钟、秒等。 理解和熟练运用 EXTRACT() 函数，能极大地提升我们处理日期时间数据的灵活性和效率。

1. EXTRACT() 函数的基本语法

EXTRACT() 函数的基本语法如下：

EXTRACT(unit FROM date)

其中：

• unit：指定要提取的日期时间部分。可以是以下值之一：

  • YEAR：年份
  • MONTH：月份
  • DAY：日期
  • HOUR：小时
  • MINUTE：分钟
  • SECOND：秒
  • MICROSECOND：微秒
  • QUARTER：季度 (1-4)
  • WEEK：一年中的第几周 (1-53)
  • DAYOFYEAR：一年中的第几天 (1-366)
  • DAYOFWEEK：一周中的第几天 (1-7，Sunday=1, Saturday=7)
  • WEEKDAY：一周中的第几天 (0-6，Monday=0, Sunday=6)
  • YEAR_MONTH：年和月 (YYYYMM)
  • YEAR_WEEK：年和周 (YYYYWW)
  • HOUR_MINUTE：小时和分钟 (HHMM)
  • HOUR_SECOND：小时和秒 (HHSS)
  • MINUTE_SECOND：分钟和秒 (MMSS)
  • DAY_HOUR：日和小时 (DDHH)
  • DAY_MINUTE：日和分钟 (DDHHMM)
  • DAY_SECOND：日和秒 (DDHHMMSS)

• date：要从中提取日期时间部分的日期或日期时间表达式。它可以是 DATE、DATETIME、TIMESTAMP 或 TIME 类型的值，也可以是返回这些类型值的表达式或函数。

2. EXTRACT() 函数的常见用法示例

为了更好地理解 EXTRACT() 函数，我们通过一些具体的示例来展示其用法。

2.1 提取年份、月份和日期

假设我们有一个名为 orders 的表，其中包含一个 order_date 列（DATE 类型），存储订单日期。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

INSERT INTO orders (order_id, order_date) VALUES
(1, '2023-10-26'),
(2, '2023-11-15'),
(3, '2024-01-05');

我们可以使用 EXTRACT() 函数提取订单的年份、月份和日期：

SELECT
    order_id,
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day
FROM
    orders;

查询结果如下：

order_id	order_date	order_year	order_month	order_day
1	2023-10-26	2023	10	26
2	2023-11-15	2023	11	15
3	2024-01-05	2024	1	5

2.2 提取小时、分钟和秒

如果 orders 表包含一个 order_datetime 列（DATETIME 类型），存储订单的日期和时间，我们可以提取小时、分钟和秒：

ALTER TABLE orders ADD COLUMN order_datetime DATETIME;
UPDATE orders SET order_datetime = CASE order_id
    WHEN 1 THEN '2023-10-26 10:30:45'
    WHEN 2 THEN '2023-11-15 15:45:00'
    WHEN 3 THEN '2024-01-05 08:15:30'
END;

SELECT
    order_id,
    order_datetime,
    EXTRACT(HOUR FROM order_datetime) AS order_hour,
    EXTRACT(MINUTE FROM order_datetime) AS order_minute,
    EXTRACT(SECOND FROM order_datetime) AS order_second
FROM
    orders;

查询结果如下：

order_id	order_datetime	order_hour	order_minute	order_second
1	2023-10-26 10:30:45	10	30	45
2	2023-11-15 15:45:00	15	45	0
3	2024-01-05 08:15:30	8	15	30

2.3 提取季度和周数

SELECT
    order_id,
    order_date,
    EXTRACT(QUARTER FROM order_date) AS order_quarter,
    EXTRACT(WEEK FROM order_date) AS order_week
FROM
    orders;

查询结果如下：

order_id	order_date	order_quarter	order_week
1	2023-10-26	4	43
2	2023-11-15	4	46
3	2024-01-05	1	1

2.4 提取年和月、年和周

SELECT
    order_id,
    order_date,
    EXTRACT(YEAR_MONTH FROM order_date) AS order_year_month,
    EXTRACT(YEAR_WEEK FROM order_date) AS order_year_week
FROM
    orders;

查询结果如下：

order_id	order_date	order_year_month	order_year_week
1	2023-10-26	202310	202343
2	2023-11-15	202311	202346
3	2024-01-05	202401	202401

2.5 与 WHERE 子句结合使用

EXTRACT() 函数通常与 WHERE 子句结合使用，以过滤特定日期时间范围的数据。

例如，要查找 2023 年 10 月的所有订单：

SELECT
    order_id,
    order_date
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2023 AND EXTRACT(MONTH FROM order_date) = 10;

查询结果如下：

order_id	order_date
1	2023-10-26

3. EXTRACT() 函数与其它日期函数对比

MySQL 提供了多种日期时间函数，EXTRACT() 只是其中之一。 了解 EXTRACT() 与其他相关函数之间的区别，有助于我们选择最合适的工具来完成任务。

3.1 EXTRACT() vs. YEAR(), MONTH(), DAY() 等

YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND() 这些函数分别用于提取日期时间的特定部分。 它们是 EXTRACT() 的简化版本，每个函数只提取一个特定的部分。

例如，以下两个查询是等效的：

SELECT EXTRACT(YEAR FROM order_date) FROM orders;
SELECT YEAR(order_date) FROM orders;

虽然 YEAR(), MONTH() 等函数更简洁，但 EXTRACT() 更加通用，可以提取更多不同的日期时间部分，例如 QUARTER、WEEK、YEAR_MONTH 等。 当需要提取单个日期时间部分时，可以使用 YEAR(), MONTH() 等函数。 当需要提取多个日期时间部分或需要提取 QUARTER、WEEK 等特殊部分时，EXTRACT() 更加方便。

3.2 EXTRACT() vs. DATE_PART()

在某些其他 SQL 数据库系统（例如 PostgreSQL）中，存在一个名为 DATE_PART() 的函数，其功能与 MySQL 的 EXTRACT() 函数非常相似。 但是，MySQL 中没有 DATE_PART() 函数，而是使用 EXTRACT() 函数来实现相同的功能。

4. EXTRACT() 函数的性能考虑

在使用 EXTRACT() 函数时，需要注意性能问题，尤其是在处理大量数据时。 如果在 WHERE 子句中使用 EXTRACT() 函数，可能会导致 MySQL 无法使用索引，从而降低查询性能。

例如，以下查询可能会导致性能问题：

SELECT
    order_id,
    order_date
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2023;

为了优化性能，可以考虑以下方法：

• 创建索引： 如果经常需要根据年份、月份等提取的日期时间部分进行查询，可以考虑在相应的列上创建索引。 但是，即使创建了索引，MySQL 也可能无法使用它，具体取决于查询的复杂性和数据分布。

• 使用范围查询： 可以将 EXTRACT() 函数转换为范围查询，以便 MySQL 可以使用索引。 例如，可以将上面的查询改写为：

  SELECT
      order_id,
      order_date
  FROM
      orders
  WHERE
      order_date >= '2023-01-01' AND order_date < '2024-01-01';

  这种方式通常比使用 EXTRACT() 函数更有效率，因为它允许 MySQL 使用 order_date 列上的索引。

• 预先计算并存储： 如果某些提取的日期时间部分需要频繁使用，可以考虑预先计算并将它们存储在单独的列中。 这样可以避免在每次查询时都进行计算，从而提高查询性能。例如，可以在 orders 表中添加 order_year 和 order_month 列，并在插入或更新数据时计算并存储相应的年份和月份。

5. EXTRACT() 函数的类型转换

EXTRACT() 函数的返回值类型是 BIGINT (长整型)。如果需要其他类型，需要进行显式类型转换。

例如，如果需要将提取的月份转换为字符串类型：

SELECT CAST(EXTRACT(MONTH FROM order_date) AS CHAR) FROM orders;

6. 使用场景案例分析

下面我们通过几个更复杂的场景案例来展示 EXTRACT() 函数的实际应用。

6.1 按月统计订单数量

假设我们需要按月统计订单数量。 可以使用 EXTRACT() 函数提取月份，并使用 GROUP BY 子句进行分组：

SELECT
    EXTRACT(YEAR_MONTH FROM order_date) AS order_month,
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    order_month
ORDER BY
    order_month;

6.2 查找特定工作日的订单

假设我们需要查找所有在星期五 (Friday) 下的订单。

SELECT
    order_id,
    order_date
FROM
    orders
WHERE
    EXTRACT(DAYOFWEEK FROM order_date) = 6; -- Friday is 6

6.3 计算每个月的平均订单金额 (假设有一个order_amount列)

假设 orders 表中有一个 order_amount 列，存储订单金额。 我们可以使用 EXTRACT() 函数提取月份，并使用 AVG() 函数计算每个月的平均订单金额：

ALTER TABLE orders ADD COLUMN order_amount DECIMAL(10, 2);
UPDATE orders SET order_amount = CASE order_id
    WHEN 1 THEN 100.00
    WHEN 2 THEN 150.50
    WHEN 3 THEN 200.75
END;

SELECT
    EXTRACT(YEAR_MONTH FROM order_date) AS order_month,
    AVG(order_amount) AS average_order_amount
FROM
    orders
GROUP BY
    order_month
ORDER BY
    order_month;

7. 注意事项与最佳实践

• NULL 值处理： 如果 date 参数为 NULL，EXTRACT() 函数将返回 NULL。
• 数据类型一致性： 确保 date 参数的数据类型与 unit 参数兼容。 例如，不能从 TIME 类型的值中提取年份。
• 时区： EXTRACT() 函数不受时区影响，它提取的是日期时间值在数据库中存储的原始部分。如果需要考虑时区，需要使用其他日期时间函数进行转换。
• 性能优化： 在处理大量数据时，务必关注性能问题，并采取相应的优化措施，例如创建索引、使用范围查询或预先计算并存储提取的日期时间部分。
• 代码可读性： 为了提高代码的可读性，建议使用有意义的别名，并添加适当的注释。

8. 总结与应用场景

EXTRACT() 函数是 MySQL 中一个非常实用的日期时间函数，它提供了一种灵活的方式来从日期或日期时间表达式中提取特定的部分。 通过掌握 EXTRACT() 函数的语法、用法和注意事项，可以更加高效地处理日期时间数据，并构建更加强大的应用程序。 掌握该函数对数据分析和报表生成至关重要。