php-database

PHP 数据库技能

掌握 PHP 数据库操作的原子技能

概述

全面涵盖 PHP 数据库交互的技能，包括 PDO、ORM 模式、查询优化、架构设计和迁移。

技能参数

输入验证

interface SkillParams {
  topic:
    | "pdo"              // Native PHP database
    | "eloquent"         // Laravel ORM
    | "doctrine"         // Symfony ORM
    | "optimization"     // Query tuning, indexing
    | "migrations"       // Schema versioning
    | "transactions";    // ACID, locking

  level: "beginner" | "intermediate" | "advanced";
  database?: "mysql" | "postgresql" | "sqlite";
  orm?: "eloquent" | "doctrine" | "none";
}

验证规则

validation:
  topic:
    required: true
    allowed: [pdo, eloquent, doctrine, optimization, migrations, transactions]
  level:
    required: true
  database:
    default: "mysql"

学习模块

模块 1：PDO 基础

beginner:
  - Connection and DSN
  - Prepared statements
  - Fetching results

intermediate:
  - Error handling modes
  - Transactions basics
  - Named placeholders

advanced:
  - Connection pooling
  - Stored procedures
  - Batch operations

模块 2：查询优化

beginner:
  - Basic indexing
  - EXPLAIN basics
  - WHERE clause optimization

intermediate:
  - Composite indexes
  - Join optimization
  - Query profiling

advanced:
  - Execution plan analysis
  - Partitioning
  - Query caching

模块 3：ORM 模式

beginner:
  - Model basics
  - CRUD operations
  - Simple relationships

intermediate:
  - Eager loading (N+1 prevention)
  - Query scopes
  - Lifecycle hooks

advanced:
  - Custom repositories
  - Result caching
  - Batch processing

错误处理与重试逻辑

errors:
  CONNECTION_ERROR:
    code: "DB_001"
    recovery: "Check connection string, retry with backoff"

  DEADLOCK_ERROR:
    code: "DB_002"
    recovery: "Retry transaction with exponential backoff"

  QUERY_ERROR:
    code: "DB_003"
    recovery: "Check SQL syntax, validate parameters"

retry:
  max_attempts: 3
  backoff:
    type: exponential
    initial_delay_ms: 100
    max_delay_ms: 2000
  retryable: [CONNECTION_ERROR, DEADLOCK_ERROR]

代码示例

使用预处理语句的 PDO

<?php
declare(strict_types=1);

final class Database
{
    private \PDO $pdo;

    public function __construct(string $dsn, string $user, string $pass)
    {
        $this->pdo = new \PDO($dsn, $user, $pass, [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE id = :id');
        $stmt->execute(['id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public function insert(array $data): int
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO users (name, email) VALUES (:name, :email)'
        );
        $stmt->execute($data);
        return (int) $this->pdo->lastInsertId();
    }
}

带重试的事务

<?php
declare(strict_types=1);

final class TransactionService
{
    public function executeWithRetry(
        \PDO $pdo,
        callable $operation,
        int $maxRetries = 3
    ): mixed {
        $attempt = 0;

        while (true) {
            try {
                $pdo->beginTransaction();
                $result = $operation($pdo);
                $pdo->commit();
                return $result;
            } catch (\PDOException $e) {
                $pdo->rollBack();

                if (++$attempt >= $maxRetries || !$this->isRetryable($e)) {
                    throw $e;
                }

                usleep(100000 * pow(2, $attempt)); // Exponential backoff
            }
        }
    }

    private function isRetryable(\PDOException $e): bool
    {
        return str_contains($e->getMessage(), 'Deadlock');
    }
}

防止 N+1（Eloquent）

<?php
// BAD: N+1 problem
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name; // Query per post!
}

// GOOD: Eager loading
$posts = Post::with('author')->get();
foreach ($posts as $post) {
    echo $post->author->name; // No extra queries
}

// BETTER: Select specific columns
$posts = Post::with(['author:id,name'])->get(['id', 'title', 'author_id']);

查询优化

-- Before: Full table scan
SELECT * FROM orders WHERE status = 'pending';

-- Step 1: Add index
CREATE INDEX idx_orders_status ON orders(status);

-- Step 2: Analyze with EXPLAIN
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Result: Index scan instead of full table scan

故障排除

问题	检测方式	解决方案
N+1 查询	Debugbar 显示 100+ 个查询	使用 with() 预加载
查询缓慢	查询耗时超过 1 秒	添加索引，运行 EXPLAIN
死锁	出现 "Deadlock found" 错误	实现重试逻辑
内存耗尽	出现内存限制错误	使用 chunk() 或 cursor()

内存高效处理

<?php
// BAD: Loads all into memory
$users = User::all();

// GOOD: Chunk processing
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process
    }
});

// BETTER: Cursor for minimal memory
foreach (User::cursor() as $user) {
    // Process one at a time
}

质量指标

指标	目标
预处理语句	100%
N+1 防止率	100%
索引建议	≥95%
事务安全性	100%

用法

Skill("php-database", {topic: "optimization", level: "advanced"})