References:Stripe API Pagination、GitHub REST API
Use Cases
- All list endpoints
- Queries with large data volumes
- Complex filtering scenarios
- Full-text search
Pagination Methods
1. Offset Pagination (Page-number Pagination)
GET /users?page=1&page_size=20
GET /users?offset=0&limit=20
Response: { "data": [...], "pagination": { "page": 1, "page_size": 20, "total": 156, "total_pages": 8 } }
Applicable: ✅ Small data volume (< 100,000) ✅ Need page jumping (user goes to page 5) ✅ Need total (display "156 total")
Not applicable: ❌ Large data volume (poor performance, deep pagination OFFSET is slow) ❌ High-concurrency updates (data may be misaligned)
### 2. Cursor 分页(游标分页)
```text
GET /users?cursor=usr_abc123&limit=20
响应:
{
"data": [...],
"pagination": {
"next_cursor": "usr_xyz789",
"has_more": true
}
}
适用:
✅ 数据量大(无穷滚动)
✅ 实时数据流
✅ 性能要求高
✅ 数据频繁更新
不适用:
❌ 需要跳页
❌ 需要精确 total
实现:
cursor 通常是排序字段的值(id / created_at)
WHERE id > cursor ORDER BY id LIMIT 20
3. Time-based Pagination
GET /events?since=2026-01-15T00:00:00Z&until=2026-01-16T00:00:00Z
适用:日志、事件流、时间相关数据
Selection Decision
后台管理 / 小数据 → offset
公开 API / 大数据 / 信息流 → cursor
日志 / 事件 → time-based
Filtering
Basic Filtering
GET /users?status=active
GET /users?role=admin
GET /users?created_after=2026-01-01
Multi-value Filtering
GET /users?status=active,inactive
GET /users?role=admin&role=manager (重复参数)
Range Filtering
GET /products?price_min=10&price_max=100
GET /orders?created_after=2026-01-01&created_before=2026-01-31
Operator Filtering (Advanced)
GET /products?price[gte]=10&price[lte]=100
GET /products?stock[gt]=0
GET /products?name[like]=phone
操作符:
eq - 等于
ne - 不等于
gt - 大于
gte - 大于等于
lt - 小于
lte - 小于等于
in - 在列表中
nin - 不在列表中
like - 模糊匹配
Full-text Search
GET /products?q=iphone
GET /products?keyword=iphone
参数名:q / keyword / search
搜索范围:通常服务端定义(如 name + description)
Sorting
GET /users?sort=created_at 升序(默认)
GET /users?sort=-created_at 降序(- 前缀)
GET /users?sort=-priority,name 多字段排序
或:
GET /users?sort_by=created_at&order=desc
Sparse Fieldsets
GET /users/{id}?fields=id,name,email
返回:只包含这三个字段
GET /orders/{id}?include=items,user
返回:默认字段 + 嵌套关联
适用:
- 移动端节省流量
- 性能优化
- 减少敏感字段返回
Unified Convention Template
## 列表接口规范
### 分页参数(offset 风格)
- page: integer, default=1, min=1
- page_size: integer, default=20, min=1, max=100
### 分页响应
{
"data": [...],
"pagination": {
"page": 1,
"page_size": 20,
"total": 156,
"total_pages": 8,
"has_next": true,
"has_previous": false
}
}
### 筛选参数
- 单值:?status=active
- 多值:?status=active,inactive
- 范围:?created_after=...&created_before=...
### 排序
- 单字段:?sort=created_at(升序)
- 单字段降序:?sort=-created_at
- 多字段:?sort=-priority,name
### 搜索
- 全文:?q=keyword
### 字段选择
- 包含:?fields=id,name,email
- 关联:?include=user,items
Complete Example
GET /products?
q=phone&
category=electronics&
price_min=100&
price_max=1000&
in_stock=true&
sort=-rating,-created_at&
page=1&
page_size=20&
fields=id,name,price,rating
→
{
"data": [
{
"id": "prd_001",
"name": "iPhone 15",
"price": 999,
"rating": 4.8
}
],
"pagination": {
"page": 1,
"page_size": 20,
"total": 87,
"total_pages": 5,
"has_next": true,
"has_previous": false
}
}
Workflow
1. 评估数据量和查询场景
2. 选择分页方式(offset / cursor / time)
3. 列出可筛选字段
4. 决定支持的操作符(基础 / 高级)
5. 列出可排序字段和默认排序
6. 决定是否支持搜索
7. 决定是否支持字段选择
8. 写入 OpenAPI 参数定义
9. 输出列表接口规范
Quality Self-check
□ 全 API 用同一种分页方式(不要混用)
□ page_size 有上限(防止滥用,max 100)
□ 排序字段命名一致(- 前缀降序)
□ 筛选参数有校验(防注入)
□ 搜索的 q 参数有最小长度
□ 列表响应有 total 或 has_more
□ 大数据集用 cursor 不用 offset
□ 默认排序明确(不能不排序)
Common Pitfalls
- Different pagination styles for different endpoints——/users uses page, /orders uses cursor
- No upper limit on page_size——attacker requests page_size=10000
- OFFSET deep pagination is slow——OFFSET 100000 LIMIT 20 has poor performance
- Sort fields not restricted——?sort=password_hash exposes fields
- total not returned——frontend cannot display "X total"
- Filters directly concatenated into SQL——SQL injection risk
- Search causes full table scan——no index, crashes when data volume is large
- Inconsistent response formats——some use data, some use items, some use results
Supporting Templates
templates/pagination-filter-sort-template.md— Pagination parameters + filtering parameters + list response structure template
Collaboration with Other Skills
上游:
endpoint-design → 列表端点清单
平行:
request-response → 列表响应结构
下游:
openapi-mock → OpenAPI 参数定义
转交后端 → 数据库索引设计(database-engineer 协作)