1. 数据库表结构设计
以新闻表为例,创建测试数据:
| 字段名 |
类型 |
说明 |
| id |
INT(11) |
主键,自增 |
| title |
VARCHAR(255) |
新闻标题 |
| content |
TEXT |
新闻内容 |
| created_at |
DATETIME |
发布时间 |
插入200条测试数据(模拟真实场景):
INSERT INTO news (title, content, created_at)
SELECT
CONCAT('新闻标题', seq),
CONCAT('这是第', seq, '条新闻的详细内容,用于测试分页效果。'),
DATE_SUB(NOW(), INTERVAL seq MINUTE)
FROM seq_1_to_200;
🔧 seq_1_to_200生成方式
MySQL 8.0+支持递归CTE:
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n<200
)
INSERT INTO news (title, content, created_at)
SELECT
CONCAT('新闻标题', n),
CONCAT('这是第', n, '条新闻的详细内容'),
DATE_SUB(NOW(), INTERVAL n MINUTE)
FROM seq;
2. 完整分页逻辑代码
以下为独立PHP文件list.php的完整实现:
<?php
// 配置参数
$host = 'localhost';
$dbname = 'test_db';
$username = 'root';
$password = '';
$pageSize = 20; // 每页条数
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
} catch (PDOException $e) {
die("数据库连接失败: " . $e->getMessage());
}
// 获取当前页码与每页条数
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$pageSize = isset($_GET['pageSize']) ? max(10, min(100, (int)$_GET['pageSize'])) : $pageSize;
// 计算总数据量
$total = $pdo->query("SELECT COUNT(*) FROM news")->fetchColumn();
$totalPages = (int)ceil($total / $pageSize);
// 计算偏移量
$offset = ($page - 1) * $pageSize;
// 查询当前页数据
$stmt = $pdo->prepare("SELECT id, title, created_at FROM news ORDER BY created_at DESC LIMIT :offset, :pageSize");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':pageSize', $pageSize, PDO::PARAM_INT);
$stmt->execute();
$newsList = $stmt->fetchAll();
// 生成分页链接(保留其他参数)
function buildPageUrl($page, $pageSize, $params = []) {
$params['page'] = $page;
$params['pageSize'] = $pageSize;
return '?' . http_build_query($params);
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="utf-8">
<title>php分页代码简单实现 - 新闻列表</title>
<style>
body { font-family: sans-serif; margin:0; padding:20px; background:#f5f7fa; }
.container { max-width:800px; margin:0 auto; background:#fff; padding:20px; border-radius:8px; box-shadow:0 2px 10px rgba(0,0,0,0.1); }
.news-item { padding:16px 0; border-bottom:1px solid #eee; }
.news-item:last-child { border-bottom:none; }
.news-title { font-size:1.1rem; font-weight:600; color:#037ef3; margin-bottom:8px; }
.news-meta { color:#888; font-size:0.9rem; }
.pagination { display:flex; justify-content:center; gap:8px; margin-top:24px; flex-wrap:wrap; }
.page-link { padding:8px 14px; background:#037ef3; color:#fff; text-decoration:none; border-radius:4px; }
.page-link:hover { background:#0266c2; }
.page-link.disabled { background:#ccc; cursor:not-allowed; }
.page-link.active { background:#0266c2; font-weight:600; }
.page-jump { display:flex; align-items:center; gap:4px; margin-left:16px; }
.page-jump input { width:60px; padding:6px; border:1px solid #ddd; border-radius:4px; text-align:center; }
.page-jump button { padding:6px 12px; background:#037ef3; color:#fff; border:none; border-radius:4px; cursor:pointer; }
</style>
</head>
<body>
<div class="container">
<h1>php分页代码简单实现 - 新闻列表</h1>
<p>共 <strong><?= $total ?></strong> 条数据,当前第 <strong><?= $page ?></strong>/<strong><?= $totalPages ?></strong> 页</p>
<?php if ($newsList): ?>
<?php foreach ($newsList as $news): ?>
<div class="news-item">
<div class="news-title"><?= htmlspecialchars($news['title']) ?></div>
<div class="news-meta">发布时间:<?= date('Y-m-d H:i:s', strtotime($news['created_at'])) ?></div>
</div>
<?php endforeach; ?>
<?php else: ?>
<p>暂无数据</p>
<?php endif; ?>
<div class="pagination">
<!-- 上一页 -->
<?php if ($page > 1): ?>
<a href="<?= buildPageUrl($page-1, $pageSize, $_GET) ?>" class="page-link">上一页</a>
<?php else: ?>
<span class="page-link disabled">上一页</span>
<?php endif; ?>
<!-- 页码列表 -->
<?php for ($i = max(1, $page - 2); $i <= min($totalPages, $page + 2); $i++): ?>
<?php if ($i == $page): ?>
<span class="page-link active"><?= $i ?></span>
<?php else: ?>
<a href="<?= buildPageUrl($i, $pageSize, $_GET) ?>" class="page-link"><?= $i ?></a>
<?php endif; ?>
<?php endfor; ?>
<!-- 下一页 -->
<?php if ($page < $totalPages): ?>
<a href="<?= buildPageUrl($page+1, $pageSize, $_GET) ?>" class="page-link">下一页</a>
<?php else: ?>
<span class="page-link disabled">下一页</span>
<?php endif; ?>
<!-- 跳转输入框 -->
<div class="page-jump">
<span>跳至</span>
<input type="number" min="1" max="<?= $totalPages ?>" value="<?= $page ?>" id="jump-page">
<span>页</span>
<button onclick="jumpToPage()">跳转</button>
</div>
</div>
</div>
<script>
function jumpToPage() {
const input = document.getElementById('jump-page');
let page = parseInt(input.value);
if (isNaN(page) || page < 1) page = 1;
if (page > <?= $totalPages ?>) page = <?= $totalPages ?>;
window.location.href = 'list.php?page=' + page + '&pageSize=' + <?= $pageSize ?> + '&' + (window.location.hash || '');
}
</script>
</body>
</html>
3. 分页性能关键点
- COUNT(*)查询优化:对大表使用索引覆盖(如
CREATE INDEX idx_status ON news(status)),避免全表扫描
- 避免深分页:当
offset > 10000时,LIMIT offset, size性能急剧下降,应改用「基于游标的分页」(见后续章节)
- 结果集缓存:对静态数据,可将
total值缓存至Redis,减少重复COUNT查询