MySQL按周按月统计业务数据：DATE_FORMAT函数使用详解
使用DATE_FORMAT函数按周按月统计时需注意多个易错点。按月统计可用`%Y-%m`格式。按周推荐使用ISO标准`%x-%v`格式，以避免跨年周归属错误。GROUPBY子句中不能直接使用SELECT定义的别名，需重复表达式或使用子查询。在WHERE条件中对字段使用DATE_FORMAT函数会导致索引失效，应改为范围查询。跨年周统计时，应使用`%x-%v`
在数据统计工作中，按周或按月进行分组是极为常见的需求。然而，即便是这种基础操作，在MySQL中也存在不少容易忽视的问题。许多开发者习惯直接使用DATE_FORMAT函数，却可能遭遇分组错误、性能急剧下降，甚至跨年数据被归入错误年份的困扰。本文将逐一剖析这些常见陷阱，并提供相应的解决方案。
正确使用DATE_FORMAT函数提取周与月
首先，提取月份相对直接，使用DATE_FORMAT(date_col, '%Y-%m')通常即可满足需求。但当涉及到“周”这个维度时，情况就变得复杂了。MySQL默认的周定义与多数业务场景不符——它默认将周日作为一周的起始，并且将包含1月1日的那一周定义为第一周（当使用%U格式符时）。
这导致了一个典型问题：当你编写DATE_FORMAT(created_at, '%Y-%U')以期按周统计时，可能会发现2023年1月1日（一个周日）被划分到了“2022-52”周。原因在于，按照%U的规则，2022年12月26日至2023年1月1日被视为同一周。
因此，更推荐采用ISO周标准，该标准规定周一为每周起点，且第一周必须包含该年的第一个星期四。对应的格式符组合是%x-%v（%x代表ISO年份，%v代表ISO周数）。但需注意，对于像2023年12月31日这样的日期，此组合返回的结果可能是“2024-01”。
- 按自然月统计：直接使用
DATE_FORMAT(created_at, '%Y-%m')，可获得如'2023-12'格式的结果。 - 按ISO周统计（推荐）：使用
DATE_FORMAT(created_at, '%x-%v')，能有效避免跨年时周份归属的混乱。 - 若业务要求以周日为起点：可考虑使用
STR_TO_DATE(CONCAT(YEARWEEK(created_at, 1), ' Monday'), '%x%v %W')这类方法反推周一日期再进行分组，但需权衡其对性能的影响。
GROUP BY子句中无法直接使用DATE_FORMAT的别名
这是另一个常见误区。许多开发者倾向于这样编写，认为清晰便捷：
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) FROM orders GROUP BY month执行时却会报错：Unknown column 'month' in 'group statement'。其根源在于，MySQL在执行GROUP BY子句时，尚无法识别SELECT列表中定义的列别名。该别名通常要到后续的ORDER BY阶段才可用，在GROUP BY和HAVING子句中均无效。
- 正确写法一（重复表达式）：在
GROUP BY后完整重复表达式：GROUP BY DATE_FORMAT(created_at, '%Y-%m')。 - 正确写法二（使用子查询）：若表达式复杂，可借助子查询：
SELECT month, COUNT(*) FROM (SELECT DATE_FORMAT(created_at, '%Y-%m') AS month FROM orders) t GROUP BY month。
在WHERE条件中对DATE_FORMAT结果过滤可能引发全表扫描
这是影响性能的关键点。如果使用如下写法：
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2023-12'那么，即使created_at字段上建有索引，MySQL优化器也无法利用。因为对列应用函数会破坏索引原有的顺序性，数据库只能被迫进行全表扫描，当数据量增大时，查询性能将显著下降。
- 应改写为范围查询：这是根本的解决方案。将上述条件改写为：
WHERE created_at >= '2023-12-01' AND created_at < '2024-01-01'。 - 按ISO周过滤的注意事项：需要预先计算目标周的起止日期。例如，查询2023年第50周（ISO周）的数据，需确定其对应2023年12月11日至17日。可使用
STR_TO_DATE('202350 Monday', '%x%v %W')来构造周一的日期作为起始点。 - 临时解决方案（MySQL 5.7+）：若无法修改查询语句，可考虑创建生成列索引。例如：
ALTER TABLE orders ADD COLUMN ym CHAR(7) STORED AS (DATE_FORMAT(created_at, '%Y-%m')), ADD INDEX idx_ym(ym)。这会在生成列ym上建立索引，但会增加存储和维护成本。
跨年周统计：YEAR()与WEEK()组合的潜在问题
部分开发者习惯使用CONCAT(YEAR(created_at), '-', WEEK(created_at, 1))来拼接周标识符。但这里存在一个陷阱：对于2023年12月31日，WEEK(..., 1)（以周日为起点）返回1，拼接后得到'2023-1'。然而，按照ISO标准或许多业务逻辑，这一天应属于2024年的第1周。这正是推荐使用%x和%v这类语义绑定的格式符的原因。
%x与%v是天然组合：%x返回ISO年份，%v返回ISO周数，两者严格对应，能妥善解决跨年归属问题。WEEK(created_at, 3)虽然也支持以周一为起点（mode=3），但其返回的周数所对应的年份仍是YEAR()函数的结果，同样无法处理跨年周。- 通过简单验证即可明了：执行
SELECT '2023-12-31', DATE_FORMAT('2023-12-31', '%x-%v'), WEEK('2023-12-31', 3), YEAR('2023-12-31')。结果将显示，前两项返回'2024-01'，而后两项分别返回1和2023。
归根结底，真正的挑战往往不在于SQL语法，而在于业务的时间定义与数据库默认行为之间存在的“间隙”。即便仅有一日之差，周的归属也可能跨越年份，最终导致统计结果出现偏差。因此，在编写SQL之前，首先明确业务层面“周”和“年”的精确定义，是避免后续返工的关键。