mysql进阶三

DATE_FORMAT() 在生产中最常见的用途就是 按天、按月、按小时统计报表。

比如有一张订单表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    create_time DATETIME
);

数据：

id	user_id	amount	create_time
1	1001	100.00	2026-01-10 10:20:00
2	1002	200.00	2026-01-15 15:30:00
3	1003	300.00	2026-02-03 09:00:00
4	1004	150.00	2026-02-20 18:00:00
5	1005	400.00	2026-03-01 20:00:00

1. 统计每个月的订单数量

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

结果：

month	order_count
2026-01	2
2026-02	2
2026-03	1

2. 统计每个月的销售额（最常见）

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

结果：

month	total_amount
2026-01	300.00
2026-02	450.00
2026-03	400.00

3. 统计月活用户（MAU）

登录表：

CREATE TABLE login_log (
    user_id BIGINT,
    login_time DATETIME
);

SQL：

SELECT
    DATE_FORMAT(login_time,'%Y-%m') AS month,
    COUNT(DISTINCT user_id) AS mau
FROM login_log
GROUP BY DATE_FORMAT(login_time,'%Y-%m')
ORDER BY month;

结果：

month	mau
2026-01	12000
2026-02	15800
2026-03	17300

生产中：

MAU = Monthly Active User（月活）

运营天天看的指标。

4. 月报 + 环比增长

例如：

WITH monthly_report AS (
    SELECT
        DATE_FORMAT(create_time,'%Y-%m') AS month,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY DATE_FORMAT(create_time,'%Y-%m')
)
SELECT
    month,
    total_amount,
    LAG(total_amount) OVER (ORDER BY month) AS previous_month,
    ROUND(
        (
            total_amount -
            LAG(total_amount) OVER (ORDER BY month)
        ) /
        LAG(total_amount) OVER (ORDER BY month) * 100,
        2
    ) AS growth_rate
FROM monthly_report;

结果：

month	销售额	上月销售额	环比
2026-01	300	-	NULL
2026-02	450	300	50.00%
2026-03	400	450	-11.11%

生产中：

运营月报基本都是这么做的。

5. 月报查询的性能问题

很多人直接写：

SELECT
    DATE_FORMAT(create_time,'%Y-%m'),
    COUNT(*)
FROM orders
GROUP BY DATE_FORMAT(create_time,'%Y-%m');

如果：

orders：5000万数据
create_time 有索引

那么：

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

会导致：

索引失效

因为对索引列进行了函数运算。

正确做法（推荐）

先限定时间范围：

SELECT
    DATE_FORMAT(create_time,'%Y-%m') AS month,
    COUNT(*) AS cnt
FROM orders
WHERE create_time >= '2026-01-01'
  AND create_time < '2027-01-01'
GROUP BY DATE_FORMAT(create_time,'%Y-%m');

这样：

WHERE 使用 create_time 索引

↓

过滤出 2026 年数据

↓

再做 DATE_FORMAT 分组

性能会好很多。

6. 超大表（月报）的终极方案

如果订单表：

10亿条+

一般不会实时跑：

GROUP BY DATE_FORMAT(...)

而是：

建立统计表：

CREATE TABLE order_month_report (
    report_month CHAR(7),
    order_count BIGINT,
    total_amount DECIMAL(18,2),
    PRIMARY KEY(report_month)
);

每天定时汇总：

INSERT INTO order_month_report(...)
ON DUPLICATE KEY UPDATE ...

查询时：

SELECT *
FROM order_month_report;

几乎毫秒级。

DATE_FORMAT 常用格式

格式	结果	说明
`%Y`	2026	年份
`%m`	06	月份
`%d`	14	日期
`%H`	13	小时
`%i`	30	分钟
`%s`	45	秒
`%Y-%m`	2026-06	月报
`%Y-%m-%d`	2026-06-14	日报
`%Y-%m-%d %H`	2026-06-14 13	小时报

所以，在生产中：

日报：

GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')

月报：

GROUP BY DATE_FORMAT(create_time,'%Y-%m')

小时报：

GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d %H')

但如果数据量很大（千万级以上），通常会演进为：

实时统计 SQL
    ↓
离线统计任务
    ↓
汇总表（月报表、日报表）
    ↓
运营后台查询汇总表

这才是真正的生产级方案。

日思日睿