分页本质是两步:
SELECT COUNT(*) FROM tableSELECT * FROM table LIMIT offset, pagesize<?php
// 配置
$host = 'localhost'; $user = 'root'; $pass = 'root'; $db = 'test';
$pageSize = 5;
// 连接
$conn = mysqli_connect($host, $user, $pass, $db);
mysqli_set_charset($conn, 'utf8');
// 总记录数
$totalRows = (int)mysqli_fetch_row(mysqli_query($conn, "SELECT COUNT(*) FROM page"))[0];
$totalPages = ceil($totalRows / $pageSize);
// 当前页
$page = max(1, min((int)$_GET['p'], $totalPages));
$offset = ($page - 1) * $pageSize;
// 查询数据
$sql = "SELECT * FROM page ORDER BY id LIMIT $offset, $pageSize";
$res = mysqli_query($conn, $sql);
// 显示表格
echo "<table border=1 cellspacing=0 width=60% align=center>";
echo "<tr><th>ID</th><th>NAME</th></tr>";
while ($row = mysqli_fetch_assoc($res)) {
echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td></tr>";
}
echo "</table>";
// 页码导航
$prev = $page > 1 ? $page - 1 : 1;
$next = $page < $totalPages ? $page + 1 : $totalPages;
echo "<h3>";
echo "<a href='?p=$prev'>上一页</a> | ";
echo "第 $page / $totalPages 页 | ";
echo "<a href='?p=$next'>下一页</a>";
echo "</h3>";
mysqli_free_result($res);
mysqli_close($conn);
LIMIT offset, count:MySQL 特有语法,offset 从 0 开始。max(1, min($page, $totalPages)) 防止非法页码。?p=页码,配合 htmlspecialchars 防 XSS。function pager($current, $total, $urlPattern) {
$range = 2; // 当前页前后显示页数
$start = max(1, $current - $range);
$end = min($total, $current + $range);
$html = '<div class="pager">';
if ($current > 1) $html .= '<a href="'.sprintf($urlPattern, $current-1).'">«</a>';
for ($i = $start; $i <= $end; $i++) {
$html .= $i == $current ? '<span class="current">'.$i.'</span>'
: '<a href="'.sprintf($urlPattern, $i).'">'.$i.'</a>';
}
if ($current < $total) $html .= '<a href="'.sprintf($urlPattern, $current+1).'">»</a>';
$html .= '</div>';
return $html;
}
echo pager($page, $totalPages, '?p=%d');
COUNT(*) 全表扫描:使用触发器维护计数表,或估算行数 EXPLAIN SELECT * FROM table。WHERE id > last_id LIMIT N 游标分页。