[ PROMPT_NODE_24062 ]
D1 设计模式
[ SKILL_DOCUMENTATION ]
# D1 模式与最佳实践
## 分页
typescript
async function getUsers({ page, pageSize }: { page: number; pageSize: number }, env: Env) {
const offset = (page - 1) * pageSize;
const [countResult, dataResult] = await env.DB.batch([
env.DB.prepare('SELECT COUNT(*) as total FROM users'),
env.DB.prepare('SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?').bind(pageSize, offset)
]);
return { data: dataResult.results, total: countResult.results[0].total, page, pageSize, totalPages: Math.ceil(countResult.results[0].total / pageSize) };
}
## 条件查询
typescript
async function searchUsers(filters: { name?: string; email?: string; active?: boolean }, env: Env) {
const conditions: string[] = [], params: (string | number | boolean | null)[] = [];
if (filters.name) { conditions.push('name LIKE ?'); params.push(`%${filters.name}%`); }
if (filters.email) { conditions.push('email = ?'); params.push(filters.email); }
if (filters.active !== undefined) { conditions.push('active = ?'); params.push(filters.active ? 1 : 0); }
const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
return await env.DB.prepare(`SELECT * FROM users ${whereClause}`).bind(...params).all();
}
## 批量插入
typescript
async function bulkInsertUsers(users: Array, env: Env) {
const stmt = env.DB.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
const batch = users.map(user => stmt.bind(user.name, user.email));
return await env.DB.batch(batch);
}
## 使用 KV 进行缓存
typescript
async function getCachedUser(userId: number, env: { DB: D1Database; CACHE: KVNamespace }) {
const cacheKey = `user:${userId}`;
const cached = await env.CACHE?.get(cacheKey, 'json');
if (cached) return cached;
const user = await env.DB.prepare('SELECT * FROM users WHERE id = ?').bind(userId).first();
if (user) await env.CACHE?.put(cacheKey, JSON.stringify(user), { expirationTtl: 300 });
return user;
}
## 查询优化
typescript
// ✅ 在 WHERE 子句中使用索引
const users = await env.DB.prepare('SELECT * FROM users WHERE email = ?').bind(email).all();
// ✅ 限制结果集
const recentPosts = await env.DB.prepare('SELECT * FROM posts ORDER BY created_at DESC LIMIT 100').all();
// ✅ 对多个独立查询使用 batch()
const [user, posts, comments] = await env.DB.batch([
env.DB.prepare('SELECT * FROM users WHERE id = ?').bind("
粤公网安备44030002003366号