MySQL 文章歸檔 SQL 查詢：按年月、年月日、非時間戳格式分組統計

部落格、CMS 系統常需「文章歸檔」功能：依發布時間分組統計文章數，生成側邊欄歸檔清單。本文提供針對 Unix 時間戳 與 DATETIME/TIMESTAMP 兩種儲存格式的完整 SQL 查詢範例，並示範格式化輸出與分頁限制。

儲存格式為 Unix 時間戳（INT）

按年月分組（最常用的歸檔格式）

SELECT 
    FROM_UNIXTIME(pubtime, '%Y-%m') AS pubtime, 
    COUNT(*) AS cnt 
FROM articles 
GROUP BY FROM_UNIXTIME(pubtime, '%Y-%m') 
ORDER BY pubtime DESC;

輸出範例：

按年月日分組（精細統計）

SELECT 
    FROM_UNIXTIME(pubtime, '%Y-%m-%d') AS pubtime, 
    COUNT(*) AS cnt 
FROM articles 
GROUP BY FROM_UNIXTIME(pubtime, '%Y-%m-%d') 
ORDER BY pubtime DESC;

儲存格式為 DATETIME / TIMESTAMP（WordPress 等常見）

使用 DATE_FORMAT() 直接格式化欄位，無需轉換。

按日期分組統計（基本版）

SELECT 
    DATE_FORMAT(`post_date`, '%Y%m%d') AS pubtime, 
    COUNT(*) AS cnt 
FROM wp_posts 
WHERE `post_status` = 'publish' 
GROUP BY DATE_FORMAT(`post_date`, '%Y%m%d') 
ORDER BY `ID` DESC;

帶格式化顯示名稱與限制筆數（側邊欄歸檔常用）

SELECT 
    DATE_FORMAT(`post_date`, '%Y%m%d') AS pubtime,
    DATE_FORMAT(`post_date`, '%m 月 %d 日') AS shijian,  -- 顯示用「01 月 15 日」
    COUNT(*) AS cnt 
FROM wp_posts 
WHERE `post_status` = 'publish' 
GROUP BY DATE_FORMAT(`post_date`, '%Y%m%d') 
ORDER BY `ID` DESC 
LIMIT 0, 7;  -- 只取近 7 筆

輸出範例：

PHP 整合範例：生成歸檔側邊欄 HTML

<?php
function getArchiveList(PDO $pdo, int $limit = 12): array
{
    $sql = "
        SELECT 
            DATE_FORMAT(`post_date`, '%Y-%m') AS ym,
            COUNT(*) AS cnt
        FROM wp_posts
        WHERE `post_status` = 'publish'
        GROUP BY DATE_FORMAT(`post_date`, '%Y-%m')
        ORDER BY ym DESC
        LIMIT :limit
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 使用
$archives = getArchiveList($pdo, 12);
?>
<aside class="widget-archive">
    <h3>文章歸檔</h3>
    <ul>
        <?php foreach ($archives as $row): ?>
            <li>
                <a href="/archive/<?= $row['ym'] ?>">
                    <?= date('Y 年 m 月', strtotime($row['ym'] . '-01')) ?> (<?= $row['cnt'] ?>)
                </a>
            </li>
        <?php endforeach; ?>
    </ul>
</aside>

效能優化關鍵點

1. 避免在 GROUP BY 使用函數：上述寫法在大量資料下會全表掃描。建議新增冗餘欄位 pub_year_month CHAR(7)（如 '2024-01'），寫入時同步填入，查詢直接 GROUP BY pub_year_month 即可用上索引。
2. 建立複合索引：INDEX idx_status_ym (post_status, pub_year_month) 覆蓋 WHERE 與 GROUP BY。
3. 分區表：超大量資料（千萬列以上）可考慮按 RANGE (YEAR(post_date)) 分區。

常見日期格式化代碼速查

總結

歸檔查詢核心在於 FROM_UNIXTIME()（時間戳）或 DATE_FORMAT()（日期時間型別）搭配 GROUP BY。實務上強烈建議新增冗餘格式化欄位並建立索引，避免函數導致全表掃描，確保歸檔側邊欄毫秒級回應。