PHP MySQL 分頁原理與實現完整指南（含 LIMIT 優化與頁碼導航）

核心原理：LIMIT 子句

SELECT * FROM table LIMIT offset, count

• offset：起始行號（從 0 開始）
• count：每頁顯示行數

頁碼 page（從 1 開始）轉換公式：

$offset = ($page - 1) * $pageSize;

完整實現代碼

<?php
// 1. 獲取並驗證頁碼
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$pageSize = 10;

// 2. 數據庫連接 (PDO 推薦)
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 3. 獲取總記錄數
$total = $pdo->query("SELECT COUNT(*) FROM `table`")->fetchColumn();
$pageCount = (int)ceil($total / $pageSize);

// 4. 邊界檢查
if ($page > $pageCount) { $page = $pageCount; }
if ($page < 1) { $page = 1; }

// 5. 計算偏移量並查詢
$offset = ($page - 1) * $pageSize;
$stmt = $pdo->prepare("SELECT * FROM `table` ORDER BY `id` DESC LIMIT :offset, :size");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':size', $pageSize, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 6. 輸出數據
foreach ($rows as $row) {
    echo htmlspecialchars($row['name']) . "<br>";
}

// 7. 生成頁碼導航
$nav = '';
for ($i = 1; $i <= $pageCount; $i++) {
    $nav .= ($i == $page)
        ? " <strong>$i</strong> "
        : " <a href='?page=$i'>$i</a> ";
}
echo "<div class='pagination'>$nav</div>";
?>

關鍵優化點

進階頁碼導航（首頁/上一頁/下一頁/尾頁 + 省略號）

function build_pager($page, $pageCount, $urlPattern, $showPages = 9) {
    $half = (int)($showPages / 2);
    $start = max(1, $page - $half);
    $end = min($pageCount, $start + $showPages - 1);
    $start = max(1, $end - $showPages + 1); // 重新校正

    $html = '';
    if ($page > 1) {
        $html .= "<a href='" . sprintf($urlPattern, 1) . "'>首頁</a> ";
        $html .= "<a href='" . sprintf($urlPattern, $page - 1) . "'>上一頁</a> ";
    }
    if ($start > 1) $html .= "<a href='" . sprintf($urlPattern, 1) . "'>1</a> ... ";
    for ($i = $start; $i <= $end; $i++) {
        $html .= ($i == $page) ? " <span class='current'>$i</span> " : " <a href='" . sprintf($urlPattern, $i) . "'>$i</a> ";
    }
    if ($end < $pageCount) $html .= "... <a href='" . sprintf($urlPattern, $pageCount) . "'>$pageCount</a> ";
    if ($page < $pageCount) {
        $html .= "<a href='" . sprintf($urlPattern, $page + 1) . "'>下一頁</a> ";
        $html .= "<a href='" . sprintf($urlPattern, $pageCount) . "'>尾頁</a> ";
    }
    return $html;
}
// 用法：echo build_pager($page, $pageCount, '?page=%d');

總結公式速查

總頁數 = ceil(總記錄數 / 每頁條數)
offset = (page - 1) * pageSize
LIMIT offset, pageSize
必須 ORDER BY 確定排序