MySQL如何通过PHP进行分页查询 MySQL+PHP实现高效分页的完整方案

LIMIT

LIMIT

<?php

// 假设数据库连接已建立，$pdo 是一个 PDO 对象
// try {
//     $pdo = new PDO('mysql:host=localhost;dbname=your_database;charset=utf8mb4', 'your_user', 'your_password');
//     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//     $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// } catch (PDOException $e) {
//     die("数据库连接失败: " . $e->getMessage());
// }

// 模拟一个PDO连接，实际项目中请替换为真实的连接
class MockPDOStatement {
    private $data;
    private $index = 0;
    public function __construct($data) { $this->data = $data; }
    public function fetchAll() { return $this->data; }
    public function fetchColumn() { return count($this->data); } // For count(*)
}
class MockPDO {
    public function prepare($sql) {
        // 模拟一些数据
        $all_data = [];
        for ($i = 1; $i <= 100; $i++) {
            $all_data[] = ['id' => $i, 'name' => 'Item ' . $i, 'description' => 'Description for item ' . $i];
        }

        // 简单的LIMIT/OFFSET解析
        $limit_match = [];
        preg_match('/LIMIT\s*(\d+)\s*,\s*(\d+)/i', $sql, $limit_match);
        $offset = isset($limit_match[1]) ? (int)$limit_match[1] : 0;
        $limit = isset($limit_match[2]) ? (int)$limit_match[2] : count($all_data);

        // 模拟COUNT(*)
        if (strpos(strtoupper($sql), 'COUNT(*)') !== false) {
            return new MockPDOStatement($all_data); // Return full data for count simulation
        }

        $limited_data = array_slice($all_data, $offset, $limit);
        return new MockPDOStatement($limited_data);
    }
    public function query($sql) {
        if (strpos(strtoupper($sql), 'COUNT(*)') !== false) {
            return new MockPDOStatement([['count' => 100]]); // Simulate total count
        }
        return $this->prepare($sql);
    }
}
$pdo = new MockPDO();


// 配置参数
$records_per_page = 10; // 每页显示10条记录

// 获取当前页码，默认为第一页
$current_page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
if ($current_page < 1) {
    $current_page = 1; // 确保页码不小于1
}

// 计算偏移量
$offset = ($current_page - 1) * $records_per_page;

// 1. 获取总记录数
try {
    $stmt_count = $pdo->query("SELECT COUNT(*) FROM your_table_name");
    $total_records = $stmt_count->fetchColumn();
} catch (PDOException $e) {
    echo "获取总记录数失败: " . $e->getMessage();
    $total_records = 0; // 失败时设为0
}

// 计算总页数
$total_pages = ceil($total_records / $records_per_page);

// 2. 查询当前页的数据
try {
    // 重要的是这里的 LIMIT 子句
    $stmt_data = $pdo->prepare("SELECT id, name, description FROM your_table_name ORDER BY id DESC LIMIT :offset, :limit");
    $stmt_data->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt_data->bindParam(':limit', $records_per_page, PDO::PARAM_INT);
    $stmt_data->execute();
    $results = $stmt_data->fetchAll();
} catch (PDOException $e) {
    echo "查询数据失败: " . $e->getMessage();
    $results = []; // 失败时设为空数组
}

// 显示数据
echo "<h2>当前页数据 (第 {$current_page} 页 / 共 {$total_pages} 页)</h2>";
if (!empty($results)) {
    echo "<table border='1' style='width:100%; border-collapse: collapse;'>";
    echo "<thead><tr><th>ID</th><th>名称</th><th>描述</th></tr></thead>";
    echo "<tbody>";
    foreach ($results as $row) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row['id']) . "</td>";
        echo "<td>" . htmlspecialchars($row['name']) . "</td>";
        echo "<td>" . htmlspecialchars($row['description']) . "</td>";
        echo "</tr>";
    }
    echo "</tbody>";
    echo "</table>";
} else {
    echo "<p>没有找到数据。</p>";
}

// 生成分页链接
echo "<div style='margin-top: 20px;'>";
if ($current_page > 1) {
    echo "<a href='?page=" . ($current_page - 1) . "'>上一页</a> ";
}

// 显示部分页码，避免页码过多
$start_page = max(1, $current_page - 2);
$end_page = min($total_pages, $current_page + 2);

if ($start_page > 1) {
    echo "<a href='?page=1'>1</a> ... ";
}

for ($i = $start_page; $i <= $end_page; $i++) {
    if ($i == $current_page) {
        echo "<span style='font-weight: bold; margin: 0 5px;'>{$i}</span> ";
    } else {
        echo "<a href='?page={$i}' style='margin: 0 5px;'>{$i}</a> ";
    }
}

if ($end_page < $total_pages) {
    echo "... <a href='?page={$total_pages}'>{$total_pages}</a>";
}

if ($current_page < $total_pages) {
    echo " <a href='?page=" . ($current_page + 1) . "'>下一页</a>";
}
echo "</div>";

?>

SELECT * FROM your_table

LIMIT

ORDER BY

ORDER BY

ORDER BY

LIMIT offset, count

offset

LIMIT 1000000, 10

SELECT * FROM your_table WHERE id > [last_id_on_previous_page] ORDER BY id ASC LIMIT 10

WHERE

ORDER BY

LIMIT

LIMIT

SELECT COUNT(*) FROM your_table_name

$_GET['page']

bindParam

bindValue

LIMIT