萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL PDO 操作類完整範例(支援預處理、事務、錯誤處理)

類特性

  • 單例模式管理 PDO 連線,首次使用時建立(Lazy Connect)。
  • 全面採用預處理(Prepared Statement)防止 SQL 注入。
  • 支援事務:begin()/commit()/rollback()。
  • 統一錯誤處理:debug 開啟時拋出 PDOException 詳細資訊。
  • 封裝常用 CRUD:query/execute/insert/insertBatch/updateByPrimaryKey/deleteByPrimaryKey。

完整程式碼

<?php
/**
 * MySQL PDO 操作類
 * 依賴 Yaf_Registry 讀取配置,可根據實際框架調整
 */
class MyMysql
{
    static private $dns      = null;
    static private $username = null;
    static private $password = null;
    static private $pdo      = null;

    public $debug = false;
    private $_beginTransaction = false;

    public function __construct($debug = true, $database = 'default')
    {
        $this->debug = $debug;
        $config = Yaf_Registry::get('config')->db->$database;
        self::$dns      = $config->dns;
        self::$username = $config->username;
        self::$password = $config->password;
    }

    static private function instance()
    {
        if (is_null(self::$pdo)) {
            try {
                self::$pdo = new PDO(self::$dns, self::$username, self::$password, [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                ]);
                self::$pdo->exec('SET NAMES utf8mb4');
            } catch (PDOException $e) {
                exit('PDOException: ' . $e->getMessage());
            }
        }
        return self::$pdo;
    }

    // ====== 查詢 ======
    public function query($sql, array $params = [], $fetchMode = PDO::FETCH_ASSOC)
    {
        self::instance();
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll($fetchMode);
        if ($this->debug) $this->error($stmt);
        return $rows;
    }

    // ====== 增刪改(回傳影響行數) ======
    public function execute($sql, array $params = [])
    {
        self::instance();
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute($params);
        if ($this->debug) $this->error($stmt);
        return $stmt->rowCount();
    }

    public function exec($sql)
    {
        self::instance();
        $rows = self::$pdo->exec($sql);
        if ($this->debug) $this->error();
        return $rows;
    }

    // ====== 單筆新增(回傳自增 ID) ======
    public function insert($table, array $data)
    {
        self::instance();
        $fields = '`' . implode('`,`', array_keys($data)) . '`';
        $placeholders = ':' . implode(',:', array_keys($data));
        $sql = "INSERT INTO `{$table}` ({$fields}) VALUES ({$placeholders})";
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute($data);
        if ($this->debug) $this->error($stmt);
        return self::$pdo->lastInsertId();
    }

    // ====== 批次新增 ======
    public function insertBatch($table, array $rows)
    {
        if (empty($rows)) return 0;
        self::instance();
        $fields = '`' . implode('`,`', array_keys($rows[0])) . '`';
        $placeholders = [];
        foreach ($rows as $i => $row) {
            $ph = [];
            foreach (array_keys($row) as $col) {
                $ph[] = ":{$col}_{$i}";
            }
            $placeholders[] = '(' . implode(',', $ph) . ')';
            // 重新索引參數陣列
            $params = [];
            foreach ($rows as $i => $row) {
                foreach ($row as $col => $val) {
                    $params[":{$col}_{$i}"] = $val;
                }
            }
        }
        $sql = "INSERT INTO `{$table}` ({$fields}) VALUES " . implode(',', $placeholders);
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute($params);
        if ($this->debug) $this->error($stmt);
        return $stmt->rowCount();
    }

    // ====== 依主鍵更新 ======
    public function updateByPrimaryKey($table, array $where, array $data)
    {
        self::instance();
        $set = [];
        foreach ($data as $col => $val) {
            $set[] = "`{$col}` = :{$col}";
        }
        $whereCol = key($where);
        $whereVal = current($where);
        $sql = "UPDATE `{$table}` SET " . implode(',', $set) . " WHERE `{$whereCol}` = :where_val";
        $params = $data;
        $params['where_val'] = $whereVal;
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute($params);
        if ($this->debug) $this->error($stmt);
        return $stmt->rowCount();
    }

    // ====== 依主鍵刪除 ======
    public function deleteByPrimaryKey($table, array $where)
    {
        self::instance();
        $whereCol = key($where);
        $whereVal = current($where);
        $sql = "DELETE FROM `{$table}` WHERE `{$whereCol}` = :where_val";
        $stmt = self::$pdo->prepare($sql);
        $stmt->execute(['where_val' => $whereVal]);
        if ($this->debug) $this->error($stmt);
        return $stmt->rowCount();
    }

    public function getLastInsertId()
    {
        self::instance();
        return self::$pdo->lastInsertId();
    }

    // ====== 事務控制 ======
    public function begin()
    {
        self::instance();
        if ($this->_beginTransaction) $this->rollback();
        $this->_beginTransaction = self::$pdo->beginTransaction();
        return $this->_beginTransaction;
    }

    public function commit()
    {
        if ($this->_beginTransaction) {
            $this->_beginTransaction = false;
            self::$pdo->commit();
        }
        return true;
    }

    public function rollback()
    {
        if ($this->_beginTransaction) {
            $this->_beginTransaction = false;
            self::$pdo->rollBack();
        }
        return false;
    }

    // ====== 錯誤處理 ======
    private function error($stmt = null)
    {
        $err = $stmt ? $stmt->errorInfo() : self::$pdo->errorInfo();
        $msg = "SQLSTATE:{$err[0]}";
        if ($err[1]) $msg .= " - ERRORCODE:{$err[1]}";
        if ($err[2]) $msg .= " - ERROR:{$err[2]}";
        if ($err[1] || $err[2]) {
            throw new PDOException($msg);
        }
    }

    public function close()
    {
        self::$pdo = null;
    }
}

使用範例

$db = new MyMysql(true, 'default');

// 查詢
$users = $db->query('SELECT * FROM users WHERE status = ? AND role = ?', [1, 'admin']);

// 新增
$id = $db->insert('users', ['name' => 'John', 'email' => 'john@example.com']);

// 批次新增
$db->insertBatch('logs', [
    ['user_id' => 1, 'action' => 'login'],
    ['user_id' => 2, 'action' => 'logout'],
]);

// 事務範例
$db->begin();
try {
    $db->execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
    $db->execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    throw $e;
}

遷移建議

  • 若非 Yaf 框架,將建構子中的配置讀取改為傳入陣列或環境變數。
  • 生產環境建議關閉 debug,改用統一日誌記錄器(Monolog 等)。
  • 大量寫入場景可考慮 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 或分批提交。
copyright © 萬盛學電腦網 all rights reserved