AJAX 数据库
Querying the database and returning JSON, with paging, search and avoiding N+1.
Key PointsThe endpoint does params → query → JSON; business logic belongs in a service.
A list endpoint
PHP
<?php
// /api/users.php
header("Content-Type: application/json; charset=utf-8");
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$page = max(1, (int)($_GET["page"] ?? 1));
$size = min(50, max(1, (int)($_GET["size"] ?? 10)));
$kw = trim((string)($_GET["kw"] ?? ""));
$where = [];
$params = [];
if ($kw !== "") {
$where[] = "name LIKE ?";
$params[] = "%" . $kw . "%";
}
$whereSql = $where ? " WHERE " . implode(" AND ", $where) : "";
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users" . $whereSql);
$stmt->execute($params);
$total = (int)$stmt->fetchColumn();
$offset = ($page - 1) * $size;
$sql = sprintf("SELECT id, name, email FROM users%s ORDER BY id LIMIT %d OFFSET %d", $whereSql, $size, $offset);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
echo json_encode([
"ok" => true,
"total" => $total,
"page" => $page,
"size" => $size,
"items" => $stmt->fetchAll(),
], JSON_UNESCAPED_UNICODE);Requesting /api/users.php?page=1&size=2 returns:
Output
{"ok":true,"total":3,"page":1,"size":2,"items":[{"id":1,"name":"Tom","email":"tom@example.com"},{"id":2,"name":"Amy","email":"amy@example.com"}]}Clamp
$size. Without it, size=1000000 dumps the whole table — a self-inflicted DoS.Rendering on the client
JavaScript
async function loadUsers(page = 1) {
const res = await fetch(`/api/users.php?page=${page}&size=10`);
const data = await res.json();
if (!data.ok) {
alert(data.msg || "加载失败");
return;
}
const html = data.items.map(u => `
<tr>
<td>${u.id}</td>
<td>${escapeHtml(u.name)}</td>
<td>${escapeHtml(u.email)}</td>
</tr>
`).join("");
document.querySelector("#tbody").innerHTML = html;
}
function escapeHtml(s) {
return s.replace(/[&<>"']/g, c => ({
"&": "&", "<": "<", ">": ">", '"': """, "'": "'"
})[c]);
}Any
innerHTML string needs escapeHtml. **<script> in JSON executes just the same** — user nicknames are payloads even if the data came from your own database.The N+1 problem
Showing an order count per user tempts a query inside the loop — 101 queries for 100 users. Use a JOIN or an aggregate subquery.
PHP
<?php
// 慢：N+1
// foreach ($users as $u) { SELECT COUNT(*) FROM orders WHERE user_id = ? }
// 快：一条语句搞定
$sql = "SELECT u.id, u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
ORDER BY u.id";
echo $sql, "\n";Output
SELECT u.id, u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
ORDER BY u.idCaching
List endpoints repeat queries heavily. A short-TTL cache (Redis or APCu) removes most of the load.
PHP
<?php
function cached(string $key, int $ttl, callable $fn): mixed {
$hit = apcu_fetch($key, $ok);
if ($ok) {
return $hit;
}
$val = $fn();
apcu_store($key, $val, $ttl);
return $val;
}
$items = cached("users:page:1", 30, function () use ($pdo) {
return $pdo->query("SELECT id, name FROM users ORDER BY id LIMIT 10")->fetchAll();
});
echo "条数: " . count($items) . "\n";Output
条数: 3Cache keys must include every parameter affecting the result — page, filters, sort, identity. Miss one and users see each other's data.
列表接口
PHP
<?php
// /api/users.php
header("Content-Type: application/json; charset=utf-8");
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$page = max(1, (int)($_GET["page"] ?? 1));
$size = min(50, max(1, (int)($_GET["size"] ?? 10)));
$kw = trim((string)($_GET["kw"] ?? ""));
$where = [];
$params = [];
if ($kw !== "") {
$where[] = "name LIKE ?";
$params[] = "%" . $kw . "%";
}
$whereSql = $where ? " WHERE " . implode(" AND ", $where) : "";
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users" . $whereSql);
$stmt->execute($params);
$total = (int)$stmt->fetchColumn();
$offset = ($page - 1) * $size;
$sql = sprintf("SELECT id, name, email FROM users%s ORDER BY id LIMIT %d OFFSET %d", $whereSql, $size, $offset);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
echo json_encode([
"ok" => true,
"total" => $total,
"page" => $page,
"size" => $size,
"items" => $stmt->fetchAll(),
], JSON_UNESCAPED_UNICODE);请求 /api/users.php?page=1&size=2 返回：
Output
{"ok":true,"total":3,"page":1,"size":2,"items":[{"id":1,"name":"Tom","email":"tom@example.com"},{"id":2,"name":"Amy","email":"amy@example.com"}]}$size 必须夹取（min(50, max(1, ...))）。不限制的话，前端传 size=1000000 就能一次拉走整张表，等于给自己造了个 DoS。前端渲染
JavaScript
async function loadUsers(page = 1) {
const res = await fetch(`/api/users.php?page=${page}&size=10`);
const data = await res.json();
if (!data.ok) {
alert(data.msg || "加载失败");
return;
}
const html = data.items.map(u => `
<tr>
<td>${u.id}</td>
<td>${escapeHtml(u.name)}</td>
<td>${escapeHtml(u.email)}</td>
</tr>
`).join("");
document.querySelector("#tbody").innerHTML = html;
}
function escapeHtml(s) {
return s.replace(/[&<>"']/g, c => ({
"&": "&", "<": "<", ">": ">", '"': """, "'": "'"
})[c]);
}用
innerHTML 拼字符串就必须 escapeHtml。**JSON 里带 <script> 一样会执行**——很多人以为「数据来自自己的数据库就安全」，其实用户的昵称就是攻击载荷。N+1 查询
列表里要显示「每人的订单数」时，新手会循环里查一次数据库——100 个用户就是 101 次查询。用一次 JOIN 或聚合子查询解决。
PHP
<?php
// 慢：N+1
// foreach ($users as $u) { SELECT COUNT(*) FROM orders WHERE user_id = ? }
// 快：一条语句搞定
$sql = "SELECT u.id, u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
ORDER BY u.id";
echo $sql, "\n";Output
SELECT u.id, u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
ORDER BY u.id缓存
列表类接口重复查询很多。加一层短 TTL 缓存（Redis 或 APCu）能省掉大量数据库压力。
PHP
<?php
function cached(string $key, int $ttl, callable $fn): mixed {
$hit = apcu_fetch($key, $ok);
if ($ok) {
return $hit;
}
$val = $fn();
apcu_store($key, $val, $ttl);
return $val;
}
$items = cached("users:page:1", 30, function () use ($pdo) {
return $pdo->query("SELECT id, name FROM users ORDER BY id LIMIT 10")->fetchAll();
});
echo "条数: " . count($items) . "\n";Output
条数: 3缓存键要包含所有影响结果的参数（分页、筛选、排序、用户身份）。漏一个就会出现「A 用户看到 B 用户数据」这类严重问题。