MySQL DATE_FORMAT() 函數完全指南：按月/按周彙總統計實戰

MySQL DATE_FORMAT() 函數完全指南：按月/按周彙總統計實戰

`DATE_FORMAT(date, format)` 是 MySQL 日期格式化與提取的核心函數，廣泛用於報表統計、時間維度分組。本文整理完整格式符號表與實戰範例。

完整格式符號對照表

常用格式化範例

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- 2024-01-15 14:30:45
SELECT DATE_FORMAT(NOW(), '%Y%m%d');             -- 20240115 (檔名用)
SELECT DATE_FORMAT(NOW(), '%b %d, %Y');          -- Jan 15, 2024
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');      -- Monday, January 15, 2024

實戰：按周/按月彙總統計

按周統計 (ISO 8601 週一為週首)

SELECT 
    DATE_FORMAT(`order_create_time`, '%x-%v') AS year_week,  -- 2024-03
    SUM(`total_amount`) AS total_sales,
    COUNT(*) AS order_count
FROM `orders`
WHERE `order_create_time` >= '2024-01-01'
GROUP BY year_week
ORDER BY year_week;

結果範例：

按月統計

SELECT 
    DATE_FORMAT(`order_create_time`, '%Y-%m') AS year_month,
    SUM(`total_amount`) AS total_sales
FROM `orders`
WHERE YEAR(`order_create_time`) = 2024
GROUP BY year_month
ORDER BY year_month;

按日統計

SELECT 
    DATE_FORMAT(`order_create_time`, '%Y-%m-%d') AS day,
    SUM(`total_amount`) AS daily_sales
FROM `orders`
WHERE `order_create_time` BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY day
ORDER BY day;

效能優化關鍵點

虛擬欄位索引優化範例 (5.7+)

ALTER TABLE `orders` 
ADD COLUMN `order_year_week` VARCHAR(7) 
GENERATED ALWAYS AS (DATE_FORMAT(`order_create_time`, '%x-%v')) VIRTUAL,
ADD INDEX `idx_year_week` (`order_year_week`);

-- 查詢自動命中索引
SELECT `order_year_week`, SUM(`total_amount`)
FROM `orders`
WHERE `order_year_week` BETWEEN '2024-01' AND '2024-12'
GROUP BY `order_year_week`;

常見陷阱

• ❌ `WHERE DATE_FORMAT(date, '%Y-%m') = '2024-01'` → 全表掃描
• ✅ `WHERE date >= '2024-01-01' AND date < '2024-02-01'` → 範圍查詢命中索引
• ❌ `%u` (週一週首) 搭配 `%Y` → 跨年周歸屬錯誤
• ✅ 統一使用 `%x-%v` (ISO 8601: 年-周，週一週首)