PHP MySQL 分頁查詢完整實作教學

從基礎 LIMIT 分頁到大資料量優化的完整指南

分頁是資料列表最核心功能。本文涵傳統 LIMIT offset, count 寫法、安全參數處理、URL 產生、以及百萬筆資料的游標分頁優化。

資料表準備

CREATE TABLE `articles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `content` MEDIUMTEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

基礎分頁實作（PDO + 安全參數綁定）

<?php
// config.php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// list.php
require 'config.php';

$page     = max(1, (int)($_GET['page'] ?? 1));
$perPage  = 15;
$offset   = ($page - 1) * $perPage;

// 1. 取得總筆數
$totalStmt = $pdo->query("SELECT COUNT(*) FROM articles");
$total     = $totalStmt->fetchColumn();
$totalPages = (int)ceil($total / $perPage);

// 2. 取得當頁資料
$stmt = $pdo->prepare("SELECT id, title, created_at FROM articles ORDER BY created_at DESC LIMIT :offset, :perPage");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':perPage', $perPage, PDO::PARAM_INT);
$stmt->execute();
$articles = $stmt->fetchAll();

// 3. 產生分頁連結
function pageUrl(int $page): string
{
    $params = $_GET;
    $params['page'] = $page;
    return '?' . http_build_query($params);
}
?>





  
ID
標題
發布時間
  

  
    

      

      

      

    
  
  

大資料量優化：游標分頁

當資料超過百萬筆，LIMIT 1000000, 15 需掃描前 100 萬列，極慢。改用「游標分頁」：

<?php
// 以唯一、有序欄位（如 id 或 created_at+id）為游標
$lastId = (int)($_GET['after'] ?? 0); // 上一頁最後一筆 id
$perPage = 15;

$stmt = $pdo->prepare("
    SELECT id, title, created_at
    FROM articles
    WHERE id > :lastId
    ORDER BY id ASC
    LIMIT :perPage
");
$stmt->bindValue(':lastId', $lastId, PDO::PARAM_INT);
$stmt->bindValue(':perPage', $perPage, PDO::PARAM_INT);
$stmt->execute();
$articles = $stmt->fetchAll();

// 下一頁連結
$nextUrl = '';
if (count($articles) === $perPage) {
    $last = end($articles);
    $nextUrl = '?' . http_build_query(['after' => $last['id']]);
}
?>

• 優點：查詢時間固定 O(log n)，不隨頁數增長而變慢。
• 缺點：無法直接跳到第 N 頁、需依序翻頁、URL 不含頁碼。
• 適用：無限捲動、API 列表、後台管理大量資料。

進階：搜尋/篩選條件下的分頁

<?php
$where = [];
$params = [];

if (!empty($_GET['keyword'])) {
    $where[] = 'title LIKE :keyword';
    $params[':keyword'] = '%' . $_GET['keyword'] . '%';
}
if (!empty($_GET['category_id'])) {
    $where[] = 'category_id = :cat';
    $params[':cat'] = (int)$_GET['category_id'];
}

$whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

// 總數
$totalStmt = $pdo->prepare("SELECT COUNT(*) FROM articles $whereSql");
$totalStmt->execute($params);
$total = $totalStmt->fetchColumn();

// 列表
$listStmt = $pdo->prepare("SELECT * FROM articles $whereSql ORDER BY created_at DESC LIMIT :offset, :perPage");
foreach ($params as $k => $v) $listStmt->bindValue($k, $v);
$listStmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$listStmt->bindValue(':perPage', $perPage, PDO::PARAM_INT);
$listStmt->execute();
$articles = $listStmt->fetchAll();

// 分頁連結保留篩選參數
function pageUrl(int $page): string
{
    $params = $_GET;
    $params['page'] = $page;
    return '?' . http_build_query($params);
}
?>

常見錯誤與最佳實務