您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP 分頁技術全解析：原理、代碼實現與通用分頁類封裝

編輯：PHP 綜合知識

前言

分頁是 Web 開發中處理大量數據展示的核心技術。本文從底層原理出發，通過 MySQL LIMIT 機制推導分頁算法，逐步構建從基礎代碼到面向對象分頁類的完整實現，並探討大數據量下的性能優化方案。

一、分頁核心原理

分頁本質是從結果集中截取指定範圍的記錄。需要三個關鍵參數：

每頁條數（PageSize）：如 10、20、50
當前頁碼（CurrentPage）：從 1 開始
總記錄數（TotalRows）：通過 SELECT COUNT(*) 獲取

衍生參數：

總頁數：TotalPages = ceil(TotalRows / PageSize)
偏移量：Offset = (CurrentPage - 1) * PageSize

MySQL 查詢模板：

SELECT * FROM `table` LIMIT {Offset}, {PageSize};
-- 等價於
SELECT * FROM `table` LIMIT {PageSize} OFFSET {Offset};

LIMIT 示例演示

-- 第 1 頁（每頁 10 條）
SELECT * FROM `article` LIMIT 0, 10;
-- 第 2 頁
SELECT * FROM `article` LIMIT 10, 10;
-- 第 3 頁
SELECT * FROM `article` LIMIT 20, 10;
-- 通用公式
SELECT * FROM `article` LIMIT (CurrentPage - 1) * PageSize, PageSize;

二、基礎分頁代碼實現（五步走）

<?php
// 1. 連接數據庫（建議使用 PDO/Mysqli，此處為演示用舊 API）
$conn = mysqli_connect('localhost', 'root', 'password', 'database');
mysqli_set_charset($conn, 'utf8mb4');

// 2. 定義分頁參數
$pageSize = 10;                    // 每頁條數
$pageNow  = max(1, (int)($_GET['page'] ?? 1));  // 當前頁，最小為 1

// 3. 獲取總記錄數
$countSql = "SELECT COUNT(*) AS total FROM `emp`";
$countRes = mysqli_query($conn, $countSql);
$totalRow = mysqli_fetch_assoc($countRes);
$totalRows = $totalRow['total'];
$totalPages = ceil($totalRows / $pageSize);

// 4. 修正頁碼邊界
if ($pageNow > $totalPages) $pageNow = $totalPages;
if ($pageNow < 1) $pageNow = 1;

// 5. 計算偏移量並查詢數據
$offset = ($pageNow - 1) * $pageSize;
$dataSql = "SELECT * FROM `emp` ORDER BY `id` DESC LIMIT {$offset}, {$pageSize}";
$dataRes = mysqli_query($conn, $dataSql);

// 6. 輸出數據
echo '<table border="1" cellpadding="5" cellspacing="0">';
echo '<tr><th>ID</th><th>姓名</th><th>職級</th><th>郵箱</th><th>薪資</th></tr>';
while ($row = mysqli_fetch_assoc($dataRes)) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['grade']}</td><td>{$row['email']}</td><td>{$row['salary']}</td></tr>";
}
echo '</table>';

// 7. 輸出分頁導航
echo '<div class="pagination">';
if ($pageNow > 1) {
    echo "<a href='?page=1'>首頁</a> ";
    echo "<a href='?page=" . ($pageNow - 1) . "'>上一頁</a> ";
}
for ($i = 1; $i <= $totalPages; $i++) {
    if ($i == $pageNow) {
        echo "<span class='current'>{$i}</span> ";
    } else {
        echo "<a href='?page={$i}'>{$i}</a> ";
    }
}
if ($pageNow < $totalPages) {
    echo "<a href='?page=" . ($pageNow + 1) . "'>下一頁</a> ";
    echo "<a href='?page={$totalPages}'>尾頁</a> ";
}
echo "<span>共 {$totalPages} 頁 / {$totalRows} 條</span>";
echo '</div>';

mysqli_free_result($dataRes);
mysqli_close($conn);

三、通用分頁類封裝（PDO 版）

1. 數據庫操作基礎類

<?php
// Database.php
class Database {
    private static $instance = null;
    private $pdo;
    
    private function __construct(array $config) {
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]);
    }
    
    public static function getInstance(array $config = []): self {
        if (self::$instance === null) {
            self::$instance = new self($config);
        }
        return self::$instance;
    }
    
    public function query(string $sql, array $params = []): PDOStatement {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
    
    public function fetchAll(string $sql, array $params = []): array {
        return $this->query($sql, $params)->fetchAll();
    }
    
    public function fetchOne(string $sql, array $params = []) {
        return $this->query($sql, $params)->fetch();
    }
    
    public function fetchColumn(string $sql, array $params = []) {
        return $this->query($sql, $params)->fetchColumn();
    }
}

2. 分頁類核心實現

<?php
// Paginator.php
class Paginator {
    private int $pageSize;
    private int $currentPage;
    private int $totalRows;
    private int $totalPages;
    private int $offset;
    
    public function __construct(int $pageSize = 15, int $currentPage = 1) {
        $this->pageSize     = max(1, $pageSize);
        $this->currentPage  = max(1, $currentPage);
    }
    
    public function setTotalRows(int $totalRows): self {
        $this->totalRows = max(0, $totalRows);
        $this->totalPages = (int)ceil($this->totalRows / $this->pageSize);
        $this->currentPage = min($this->currentPage, max(1, $this->totalPages));
        $this->offset = ($this->currentPage - 1) * $this->pageSize;
        return $this;
    }
    
    public function getLimitSql(): string {
        return "LIMIT {$this->offset}, {$this->pageSize}";
    }
    
    public function getOffset(): int { return $this->offset; }
    public function getPageSize(): int { return $this->pageSize; }
    public function getCurrentPage(): int { return $this->currentPage; }
    public function getTotalPages(): int { return $this->totalPages; }
    public function getTotalRows(): int { return $this->totalRows; }
    
    // 生成分頁導航 HTML
    public function render(string $urlPattern = '?page=%d', array $options = []): string {
        $defaults = [
            'prevText' => '« 上一頁',
            'nextText' => '下一頁 »',
            'firstText' => '首頁',
            'lastText' => '尾頁',
            'showNumbers' => true,
            'maxNumbers' => 10,
            'currentClass' => 'active',
            'disabledClass' => 'disabled',
        ];
        $opts = array_merge($defaults, $options);
        
        if ($this->totalPages <= 1) return '';
        
        $html = '<nav aria-label="Pagination"><ul class="pagination">';
        
        // 首頁/上一頁
        if ($this->currentPage > 1) {
            $html .= $this->link($opts['firstText'], 1, $urlPattern);
            $html .= $this->link($opts['prevText'], $this->currentPage - 1, $urlPattern);
        } else {
            $html .= $this->span($opts['firstText'], $opts['disabledClass']);
            $html .= $this->span($opts['prevText'], $opts['disabledClass']);
        }
        
        // 數字頁碼
        if ($opts['showNumbers']) {
            $start = max(1, $this->currentPage - (int)($opts['maxNumbers'] / 2));
            $end   = min($this->totalPages, $start + $opts['maxNumbers'] - 1);
            $start = max(1, $end - $opts['maxNumbers'] + 1);
            
            for ($i = $start; $i <= $end; $i++) {
                if ($i == $this->currentPage) {
                    $html .= $this->span($i, $opts['currentClass']);
                } else {
                    $html .= $this->link($i, $i, $urlPattern);
                }
            }
        }
        
        // 下一頁/尾頁
        if ($this->currentPage < $this->totalPages) {
            $html .= $this->link($opts['nextText'], $this->currentPage + 1, $urlPattern);
            $html .= $this->link($opts['lastText'], $this->totalPages, $urlPattern);
        } else {
            $html .= $this->span($opts['nextText'], $opts['disabledClass']);
            $html .= $this->span($opts['lastText'], $opts['disabledClass']);
        }
        
        $html .= "<li class='page-info'>共 {$this->totalPages} 頁 / {$this->totalRows} 條</li>";
        $html .= '</ul></nav>';
        
        return $html;
    }
    
    private function link($text, int $page, string $pattern): string {
        $url = sprintf($pattern, $page);
        return "<li><a href='{$url}'>{$text}</a></li>";
    }
    
    private function span($text, string $class): string {
        return "<li class='{$class}'><span>{$text}</span></li>";
    }
}

3. 控制器中使用示例

<?php
// ArticleController.php
require_once 'Database.php';
require_once 'Paginator.php';

$db = Database::getInstance([
    'host' => 'localhost', 'user' => 'root', 'pass' => 'pass', 'dbname' => 'blog', 'charset' => 'utf8mb4'
]);

$page = max(1, (int)($_GET['page'] ?? 1));
$pageSize = 10;

// 1. 獲取總數
$totalRows = $db->fetchColumn("SELECT COUNT(*) FROM `article` WHERE `status` = 1");

// 2. 初始化分頁器
$paginator = new Paginator($pageSize, $page);
$paginator->setTotalRows($totalRows);

// 3. 查詢數據
$articles = $db->fetchAll(
    "SELECT `id`, `title`, `summary`, `created_at` FROM `article` WHERE `status` = 1 ORDER BY `id` DESC " . $paginator->getLimitSql()
);

// 4. 輸出模板（此處簡化為直接輸出）
foreach ($articles as $art) {
    echo "<article><h2>{$art['title']}</h2><p>{$art['summary']}</p><time>{$art['created_at']}</time></article>";
}

// 5. 輸出分頁導航
echo $paginator->render('?page=%d', ['maxNumbers' => 8]);

四、大數據量分頁優化方案

問題	原因	解決方案
深分頁性能急劇下降	`LIMIT 1000000, 20` 需掃描 100 萬行再丟棄	延遲關聯：`SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 20) AS tmp USING(id)`；或游標分頁：`WHERE id > last_id ORDER BY id LIMIT 20`
COUNT(*) 慢	InnoDB 無緩存總行數，需全表掃描	近似值：`EXPLAIN SELECT COUNT(*)`；緩存總數；觸發器維護計數表；`information_schema.TABLES`（非事務安全）
併發下數據漂移	查詢總數與查詢數據之間有寫入/刪除	接受最終一致性；或使用事務+可重複讀隔離級別（會鎖表）
前端跳轉頁碼過多	總頁數極大（如百萬頁）	僅提供下一頁/游標模式；不顯示具體頁碼；搜索/篩選替代翻頁

五、游標分頁（Keyset Pagination）實現

// 適合無限滾動、時間線、日誌查看等場景
// 前端傳遞 last_id（或 last_created_at）而非 page
$lastId = (int)($_GET['last_id'] ?? 0);
$pageSize = 20;

$sql = "SELECT * FROM `article` WHERE `status` = 1";
if ($lastId > 0) {
    $sql .= " AND `id` < {$lastId}";  // 假設 ID 遞增，取更早的數據
}
$sql .= " ORDER BY `id` DESC LIMIT {$pageSize}";

$articles = $db->fetchAll($sql);

// 返回數據 + 下一頁游標
$nextCursor = 0;
if (!empty($articles)) {
    $nextCursor = end($articles)['id'];
}
echo json_encode(['data' => $articles, 'next_cursor' => $nextCursor, 'has_more' => count($articles) === $pageSize]);