PHP 簡易 DAO 類實現 CRUD 操作（基於 PDO 的現代化重構）

原文使用已棄用的 mysql_* 函數，且存在 SQL 注入風險。本文重構為基於 PDO 預處理語句 的 SimpleDao 類，支持鏈式調用、參數綁定、事務、異常處理，適合中小項目快速開發。

重構後的 SimpleDao

<?php
class SimpleDao {
    private PDO $pdo;
    private string $table = '';

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }

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

    /** 通用查詢 */
    public function query(string $sql, array $params = []): array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /** 查詢單條 */
    public function find(array $where = []): ?array {
        $sql = "SELECT * FROM {$this->table}" . $this->buildWhere($where);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array_values($where));
        return $stmt->fetch() ?: null;
    }

    /** 查詢列表 */
    public function all(array $where = [], string $orderBy = '', int $limit = 0): array {
        $sql = "SELECT * FROM {$this->table}" . $this->buildWhere($where);
        if ($orderBy) $sql .= " ORDER BY $orderBy";
        if ($limit > 0) $sql .= " LIMIT $limit";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array_values($where));
        return $stmt->fetchAll();
    }

    /** 插入，返回自增 ID */
    public function insert(array $data): int|false {
        $fields = implode(',', array_keys($data));
        $placeholders = ':' . implode(',:', array_keys($data));
        $sql = "INSERT INTO {$this->table} ($fields) VALUES ($placeholders)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        return $stmt->rowCount() ? (int)$this->pdo->lastInsertId() : false;
    }

    /** 更新，返回影響行數 */
    public function update(array $data, array $where): int {
        $set = implode(',', array_map(fn($k) => "$k = :$k", array_keys($data)));
        $sql = "UPDATE {$this->table} SET $set" . $this->buildWhere($where);
        // 合併 data 和 where 參數（鍵名不衝突時）
        $params = $data + $where;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    /** 刪除，返回影響行數 */
    public function delete(array $where): int {
        $sql = "DELETE FROM {$this->table}" . $this->buildWhere($where);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array_values($where));
        return $stmt->rowCount();
    }

    /** 事務執行 */
    public function transaction(callable $callback): mixed {
        $this->pdo->beginTransaction();
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    private function buildWhere(array $where): string {
        if (empty($where)) return '';
        $conditions = array_map(fn($k) => "$k = :$k", array_keys($where));
        return ' WHERE ' . implode(' AND ', $conditions);
    }
}

// 輔助函數：快速獲取 DAO 實例
function dao(string $table = ''): SimpleDao {
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "user", "pass");
    }
    return (new SimpleDao($pdo))->table($table);
}

使用示例

<?php
// 插入
$userId = dao('users')->insert([
    'username' => 'zhangsan',
    'email' => 'zhangsan@example.com',
    'created_at' => date('Y-m-d H:i:s')
]);

// 查詢單條
$user = dao('users')->find(['id' => $userId]);

// 列表查詢
$users = dao('users')->all(['status' => 1], 'id DESC', 20);

// 更新
dao('users')->update(['email' => 'new@example.com'], ['id' => $userId]);

// 刪除
dao('users')->delete(['id' => $userId]);

// 事務：轉賬示例
dao()->transaction(function($dao) {
    $dao->table('accounts')->update(['balance' => 'balance - 100'], ['id' => 1]);
    $dao->table('accounts')->update(['balance' => 'balance + 100'], ['id' => 2]);
    $dao->table('transfers')->insert(['from_id' => 1, 'to_id' => 2, 'amount' => 100]);
});

核心改進對比

適用場景與局限

• ✅ 中小項目、內部工具、快速原型、不想引入重型 ORM（Eloquent/Doctrine）。
• ❌ 複雜關聯查詢、動態構建條件、分表分庫、高併發寫入優化——請上成熟 ORM 或 Query Builder。

代碼約 120 行，零依賴，可直接放入項目 lib/SimpleDao.php 使用。若需分頁、Join、聚合，可在子類擴展 paginate()、join() 方法。