MySQL 中如何获取时间字段的年份和月份

很多业务表都会有 created_at、updated_at、pay_time、login_time 这类字段。取年份、月份这件事本身不难，难的是别把一个简单统计写成慢查询。MySQL 提供了 YEAR()、MONTH()、DATE_FORMAT()、EXTRACT() 等日期时间函数，官方文档也明确把它们归在 Date and Time Functions 下：YEAR() 用来返回年份，MONTH() 用来返回月份，DATE_FORMAT() 用来按格式输出日期字符串，EXTRACT() 用来提取日期的一部分。([MySQL开发者专区][1])

准备一张示例表

先用一张订单表举例。字段不多，但足够覆盖实际开发里最常见的查询场景。

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no VARCHAR(64) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    pay_time DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pay_time (pay_time),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入几条测试数据：

INSERT INTO orders (order_no, user_id, amount, pay_time, created_at) VALUES
('A001', 1001, 99.90,  '2024-01-15 10:20:30', '2024-01-15 10:21:00'),
('A002', 1002, 188.00, '2024-02-03 09:10:00', '2024-02-03 09:11:00'),
('A003', 1001, 59.90,  '2024-02-20 18:30:00', '2024-02-20 18:31:00'),
('A004', 1003, 299.00, '2025-01-08 14:05:00', '2025-01-08 14:06:00');

用 YEAR() 获取年份

最直接的写法是 YEAR(时间字段)：

SELECT
    id,
    order_no,
    pay_time,
    YEAR(pay_time) AS pay_year
FROM orders;

结果大概是这样：

id  order_no  pay_time             pay_year
1   A001      2024-01-15 10:20:30  2024
2   A002      2024-02-03 09:10:00  2024
3   A003      2024-02-20 18:30:00  2024
4   A004      2025-01-08 14:05:00  2025

YEAR(date) 返回日期中的年份。按照 MySQL 8.4 官方文档，它返回的年份范围通常是 1000 到 9999，如果是 “zero date” 会返回 0，传入 NULL 时返回 NULL。([MySQL开发者专区][1])

用 MONTH() 获取月份

获取月份用 MONTH()：

SELECT
    id,
    order_no,
    pay_time,
    MONTH(pay_time) AS pay_month
FROM orders;

MONTH(date) 返回 1 到 12，分别表示一月到十二月；如果日期里月份部分为 0，例如某些不完整日期，可能返回 0；传入 NULL 时返回 NULL。([MySQL开发者专区][1])

如果你只是展示月份，MONTH() 足够。如果你要做年月组合统计，单独取月份通常不够，因为不同年份的 2 月会混在一起。

同时获取年份和月份

常见写法有两种。

第一种是分别取年和月：

SELECT
    YEAR(pay_time) AS pay_year,
    MONTH(pay_time) AS pay_month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY YEAR(pay_time), MONTH(pay_time)
ORDER BY pay_year, pay_month;

这种结果适合给后端继续处理：

pay_year  pay_month  order_count  total_amount
2024      1          1            99.90
2024      2          2            247.90
2025      1          1            299.00

第二种是用 DATE_FORMAT() 拼成 yyyy-MM：

SELECT
    DATE_FORMAT(pay_time, '%Y-%m') AS pay_month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_FORMAT(pay_time, '%Y-%m')
ORDER BY pay_month;

DATE_FORMAT(date, format) 会根据格式字符串格式化日期。比如 %Y 表示四位年份，%m 表示两位数字月份，所以 DATE_FORMAT(pay_time, '%Y-%m') 会得到类似 2024-02 的字符串。([MySQL开发者专区][1])

这里有个小细节：DATE_FORMAT() 返回的是字符串。用 %Y-%m 排序通常没问题，因为 2024-01、2024-02、2025-01 这种格式天然符合时间顺序。但如果写成 %m-%Y，字符串排序就不再等于时间排序了。

用 EXTRACT() 获取年月

EXTRACT() 是更通用的写法：

SELECT
    EXTRACT(YEAR FROM pay_time) AS pay_year,
    EXTRACT(MONTH FROM pay_time) AS pay_month
FROM orders;

它也可以直接提取 YEAR_MONTH：

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

结果类似：

pay_year_month  order_count
202401          1
202402          2
202501          1

MySQL 文档里的例子也展示了 EXTRACT(YEAR FROM '2019-07-02') 返回 2019，EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03') 返回 201907。([MySQL开发者专区][1])

这类写法适合做报表统计，尤其是你希望年月是一个数字值，而不是字符串值的时候。

查询某一年、某一月的数据

很多人会这么写：

SELECT *
FROM orders
WHERE YEAR(pay_time) = 2024
  AND MONTH(pay_time) = 2;

能查，语义也直观。

但在数据量大时，这种写法不太建议直接放在线上高频查询里。问题不在函数本身，而在你对列做了函数计算。pay_time 上虽然有索引，但条件变成了 YEAR(pay_time)、MONTH(pay_time) 的计算结果，优化器很难像普通范围条件那样直接利用 idx_pay_time 做高效范围扫描。

更稳的写法是时间范围查询：

SELECT *
FROM orders
WHERE pay_time >= '2024-02-01 00:00:00'
  AND pay_time <  '2024-03-01 00:00:00';

查 2024 年全年：

SELECT *
FROM orders
WHERE pay_time >= '2024-01-01 00:00:00'
  AND pay_time <  '2025-01-01 00:00:00';

这个写法看起来没那么“优雅”，但它通常更适合索引。线上查询里，时间字段有索引时，优先考虑这种左闭右开的范围条件。

统计每月数据时怎么写更合适

如果只是离线报表、后台管理页、数据量不大，下面这种写法够用了：

SELECT
    DATE_FORMAT(pay_time, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_FORMAT(pay_time, '%Y-%m')
ORDER BY month;

如果是大表，并且经常按月份统计，可以考虑两种优化方向。

第一种，在查询条件里仍然使用时间范围，减少扫描数据量：

SELECT
    DATE_FORMAT(pay_time, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE pay_time >= '2024-01-01 00:00:00'
  AND pay_time <  '2025-01-01 00:00:00'
GROUP BY DATE_FORMAT(pay_time, '%Y-%m')
ORDER BY month;

第二种，给年月单独建字段。比如订单写入时直接存一个 pay_month：

CREATE TABLE order_month_stats (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    pay_month CHAR(7) NOT NULL COMMENT '格式：yyyy-MM',
    order_count INT NOT NULL DEFAULT 0,
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id),
    UNIQUE KEY uk_pay_month (pay_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这不是为了“省一个函数”，而是为了让查询模型更明确。报表类系统里，明细表和统计表分开，往往比在明细表上临时扫全量更可靠。

生成列和函数索引可以用吗

可以，但要看版本和场景。

MySQL 支持生成列，生成列的值可以通过列定义中的表达式计算出来。官方文档给出的定义就是：generated column 的值由列定义里的表达式计算。([MySQL开发者专区][2])

比如可以给订单表加一个年月生成列：

ALTER TABLE orders
ADD COLUMN pay_year_month INT
    GENERATED ALWAYS AS (EXTRACT(YEAR_MONTH FROM pay_time)) STORED,
ADD KEY idx_pay_year_month (pay_year_month);

之后查询某个月可以写：

SELECT *
FROM orders
WHERE pay_year_month = 202402;

MySQL 的 CREATE INDEX 语法里，索引的 key part 可以是列名，也可以是表达式；文档语法中写的是 key_part: {col_name [(length)] | (expr)} [ASC | DESC]。([MySQL开发者专区][3])

所以在支持表达式索引的版本里，也可以考虑类似这样的写法：

CREATE INDEX idx_pay_year_month_expr
ON orders ((EXTRACT(YEAR_MONTH FROM pay_time)));

不过实际项目里我更偏向先用范围查询。如果业务确实高频按年月检索，再考虑生成列或表达式索引。原因很简单：索引不是免费的，写入、更新、存储空间都会跟着变。

几种写法怎么选

如果只是展示年份，直接用：

YEAR(pay_time)

如果只是展示月份，直接用：

MONTH(pay_time)

如果要展示成 2024-02：

DATE_FORMAT(pay_time, '%Y-%m')

如果要得到数字型年月：

EXTRACT(YEAR_MONTH FROM pay_time)

如果要查某个月的数据，优先写范围条件：

WHERE pay_time >= '2024-02-01 00:00:00'
  AND pay_time <  '2024-03-01 00:00:00'

这才是实际开发里最容易被忽略的地方：取年月的函数很简单，真正影响性能的是你把它放在了 SELECT、GROUP BY，还是放在了 WHERE 条件的时间列上。