PHP 數據庫操作 Helper 類：PDO 封裝與最佳實踐

從 mysql_* 到 PDO 的必然選擇

PHP 7.0 移除 `mysql_*` 擴展，`mysqli` 和 `PDO` 成為標準。PDO 提供數據庫抽象層、預處理語句防注入、多數據庫驅動統一接口，是現代 PHP 開發首選。

核心 DB Helper 類實現

<?php
namespace App\Database;

use PDO;
use PDOException;
use PDOStatement;
use Throwable;

class DB {
    private PDO $pdo;
    private string $table = '';
    private array $where = [];
    private array $bindings = [];
    private string $orderBy = '';
    private int $limit = 0;
    private int $offset = 0;
    private array $selectColumns = ['*'];
    private array $joins = [];
    private bool $transaction = false;

    public function __construct(array $config) {
        $dsn = "{$config['driver']}:host={$config['host']};dbname={$config['database']};charset={$config['charset']}";
        
        $this->pdo = new PDO($dsn, $config['username'], $config['password'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$config['charset']}",
        ]);
    }

    // ==================== 查詢構建器 ====================

    public function table(string $table): self {
        $this->table = $table;
        return $this;
    }

    public function select(array|string $columns = ['*']): self {
        $this->selectColumns = is_array($columns) ? $columns : func_get_args();
        return $this;
    }

    public function where(string $column, string $operator, mixed $value = null): self {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        $this->where[] = ['column' => $column, 'operator' => $operator, 'value' => $value];
        return $this;
    }

    public function whereIn(string $column, array $values): self {
        $this->where[] = ['column' => $column, 'operator' => 'IN', 'value' => $values];
        return $this;
    }

    public function whereNull(string $column): self {
        $this->where[] = ['column' => $column, 'operator' => 'IS NULL', 'value' => null];
        return $this;
    }

    public function orderBy(string $column, string $direction = 'ASC'): self {
        $this->orderBy = "$column $direction";
        return $this;
    }

    public function limit(int $limit, int $offset = 0): self {
        $this->limit = $limit;
        $this->offset = $offset;
        return $this;
    }

    public function join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self {
        $this->joins[] = compact('table', 'first', 'operator', 'second', 'type');
        return $this;
    }

    // ==================== 執行方法 ====================

    public function get(): array {
        return $this->runSelect()->fetchAll();
    }

    public function first(): array|false {
        $this->limit(1);
        return $this->runSelect()->fetch() ?: false;
    }

    public function find(mixed $id, string $column = 'id'): array|false {
        return $this->where($column, $id)->first();
    }

    public function count(string $column = '*'): int {
        $originalSelect = $this->selectColumns;
        $this->selectColumns = ["COUNT($column) as count"];
        $result = $this->runSelect()->fetch();
        $this->selectColumns = $originalSelect;
        return (int) ($result['count'] ?? 0);
    }

    public function exists(): bool {
        return $this->count() > 0;
    }

    public function paginate(int $perPage = 15, int $page = 1): array {
        $total = $this->count();
        $this->limit($perPage, ($page - 1) * $perPage);
        $data = $this->get();
        
        return [
            'data' => $data,
            'total' => $total,
            'per_page' => $perPage,
            'current_page' => $page,
            'last_page' => (int) ceil($total / $perPage),
        ];
    }

    // ==================== 寫入操作 ====================

    public function insert(array $data): int|false {
        $this->resetBuilder();
        $columns = array_keys($data);
        $placeholders = ':' . implode(', :', $columns);
        $sql = "INSERT INTO {$this->table} (" . implode(', ', $columns) . ") VALUES ($placeholders)";
        
        $stmt = $this->prepare($sql);
        foreach ($data as $key => $value) {
            $stmt->bindValue(":$key", $value);
        }
        
        return $stmt->execute() ? (int) $this->pdo->lastInsertId() : false;
    }

    public function insertGetId(array $data): int|false {
        return $this->insert($data);
    }

    public function update(array $data): int {
        $this->resetBuilder();
        $sets = [];
        foreach (array_keys($data) as $column) {
            $sets[] = "$column = :$column";
        }
        $sql = "UPDATE {$this->table} SET " . implode(', ', $sets) . $this->buildWhereClause();
        
        $stmt = $this->prepare($sql);
        foreach ($data as $key => $value) {
            $stmt->bindValue(":$key", $value);
        }
        $this->bindWhereValues($stmt);
        
        return $stmt->execute() ? $stmt->rowCount() : 0;
    }

    public function delete(): int {
        $this->resetBuilder();
        $sql = "DELETE FROM {$this->table}" . $this->buildWhereClause();
        $stmt = $this->prepare($sql);
        $this->bindWhereValues($stmt);
        return $stmt->execute() ? $stmt->rowCount() : 0;
    }

    public function increment(string $column, int $amount = 1, array $extra = []): int {
        $sql = "UPDATE {$this->table} SET $column = $column + $amount" . $this->buildWhereClause();
        $stmt = $this->prepare($sql);
        $this->bindWhereValues($stmt);
        return $stmt->execute() ? $stmt->rowCount() : 0;
    }

    // ==================== 事務 ====================

    public function transaction(callable $callback): mixed {
        if ($this->transaction) {
            return $callback($this); // 嵌套事務直接執行
        }
        
        $this->transaction = true;
        $this->pdo->beginTransaction();
        
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        } finally {
            $this->transaction = false;
            $this->resetBuilder();
        }
    }

    // ==================== 原生 SQL ====================

    public function raw(string $sql, array $bindings = []): PDOStatement {
        $stmt = $this->prepare($sql);
        foreach ($bindings as $key => $value) {
            $isInt = is_int($key);
            $stmt->bindValue($isInt ? $key + 1 : ":$key", $value);
        }
        $stmt->execute();
        return $stmt;
    }

    // ==================== 內部方法 ====================

    private function runSelect(): PDOStatement {
        $sql = $this->buildSelectSql();
        $stmt = $this->prepare($sql);
        $this->bindWhereValues($stmt);
        $stmt->execute();
        $this->resetBuilder();
        return $stmt;
    }

    private function buildSelectSql(): string {
        $sql = "SELECT " . implode(', ', $this->selectColumns) . " FROM {$this->table}";
        
        foreach ($this->joins as $join) {
            $sql .= " {$join['type']} JOIN {$join['table']} ON {$join['first']} {$join['operator']} {$join['second']}";
        }
        
        $sql .= $this->buildWhereClause();
        
        if ($this->orderBy) {
            $sql .= " ORDER BY {$this->orderBy}";
        }
        
        if ($this->limit > 0) {
            $sql .= " LIMIT {$this->limit}";
            if ($this->offset > 0) {
                $sql .= " OFFSET {$this->offset}";
            }
        }
        
        return $sql;
    }

    private function buildWhereClause(): string {
        if (empty($this->where)) return '';
        
        $clauses = [];
        foreach ($this->where as $i => $w) {
            $prefix = $i === 0 ? ' WHERE ' : ' AND ';
            if ($w['operator'] === 'IN') {
                $placeholders = implode(', ', array_fill(0, count($w['value']), '?'));
                $clauses[] = "{$prefix}{$w['column']} IN ($placeholders)";
            } elseif ($w['operator'] === 'IS NULL') {
                $clauses[] = "{$prefix}{$w['column']} IS NULL";
            } else {
                $clauses[] = "{$prefix}{$w['column']} {$w['operator']} ?";
            }
        }
        return implode('', $clauses);
    }

    private function bindWhereValues(PDOStatement $stmt): void {
        $paramIndex = 1;
        foreach ($this->where as $w) {
            if ($w['operator'] === 'IN') {
                foreach ($w['value'] as $v) {
                    $stmt->bindValue($paramIndex++, $v);
                }
            } elseif ($w['operator'] !== 'IS NULL') {
                $stmt->bindValue($paramIndex++, $w['value']);
            }
        }
    }

    private function prepare(string $sql): PDOStatement {
        try {
            return $this->pdo->prepare($sql);
        } catch (PDOException $e) {
            error_log("SQL Prepare Error: $sql\n" . $e->getMessage());
            throw $e;
        }
    }

    private function resetBuilder(): void {
        $this->where = [];
        $this->orderBy = '';
        $this->limit = 0;
        $this->offset = 0;
        $this->selectColumns = ['*'];
        $this->joins = [];
    }

    // ==================== 獲取 PDO 實例 ====================
    public function getPdo(): PDO {
        return $this->pdo;
    }
}
?>

配置與初始化

<?php
// config/database.php
return [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', 3306),
            'database' => env('DB_DATABASE', 'app'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'prefix' => '',
        ],
    ],
];

// bootstrap/app.php
$container->singleton('db', function() {
    $config = config('database.connections.mysql');
    return new \App\Database\DB($config);
});

// 使用
$db = app('db');
$users = $db->table('users')
    ->select(['id', 'name', 'email'])
    ->where('status', 1)
    ->where('created_at', '>', '2024-01-01')
    ->orderBy('id', 'DESC')
    ->limit(10)
    ->get();
?>

進階用法示例

<?php
// 1. 關聯查詢
$orders = $db->table('orders')
    ->select(['orders.id', 'orders.amount', 'users.name as user_name'])
    ->join('users', 'orders.user_id', '=', 'users.id', 'LEFT')
    ->where('orders.status', 'paid')
    ->get();

// 2. 複雜條件
$users = $db->table('users')
    ->where(function($q) {
        $q->where('status', 1)->where('role', 'admin');
    }) // 需擴展 where 閉包支持
    ->orWhere('email', 'like', '%@company.com')
    ->get();

// 3. 批量插入
$rows = [
    ['name' => 'A', 'email' => 'a@test.com'],
    ['name' => 'B', 'email' => 'b@test.com'],
];
$db->transaction(function($db) use ($rows) {
    foreach ($rows as $row) {
        $db->table('users')->insert($row);
    }
});

// 4. 原生 SQL（複雜報表）
$stats = $db->raw("
    SELECT DATE(created_at) as date, COUNT(*) as count, SUM(amount) as total
    FROM orders
    WHERE created_at >= :start
    GROUP BY DATE(created_at)
    ORDER BY date DESC
", ['start' => '2024-01-01'])->fetchAll();

// 5. 分頁
$page = (int)($_GET['page'] ?? 1);
$result = $db->table('articles')
    ->where('published', 1)
    ->orderBy('id', 'DESC')
    ->paginate(20, $page);
?>

安全最佳實踐

性能優化清單

• ✅ 只選需要的列（避免 `SELECT *`）
• ✅ 適當索引（EXPLAIN 分析慢查詢）
• ✅ 分頁大數據用游標/ID 偏移而非 OFFSET
• ✅ 批量操作用事務包裹
• ✅ 讀寫分離（主庫寫、從庫讀）
• ✅ 連接池（Swoole/Workerman 長連接）或持久連接
• ✅ 慢查詢日誌監控（>1s 告警）

總結

PDO 封裝 Helper 類是 PHP 專案的數據庫訪問基石。核心價值：統一接口、預處理安全、鏈式查詢構建、事務管理、分頁封裝。生產環境建議在此之上構建 Repository 模式、讀寫分離代理、查詢緩存層、遷移工具，形成完整數據訪問層架構。