萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 入門級 MySQL 連線類別封裝:PDO 版與 mysqli 版最佳實務

原始碼問題分析

  • 使用已移除的 mysql_* 函式
  • 硬編碼參數、無例外處理
  • 未設定字元集、錯誤模式

現代 PDO 封裝類別(單例模式)

<?php
class Database {
    private static ?PDO $instance = null;
    private PDO $pdo;

    private function __construct(array $config) {
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }

    public static function getInstance(array $config = []): self {
        if (self::$instance === null) {
            if (empty($config)) {
                throw new Exception('首次呼叫需提供設定陣列');
            }
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    public function getPDO(): PDO {
        return $this->pdo;
    }

    // 禁止複製
    private function __clone() {}
    public function __wakeup() {}
}

// 使用方式
$db = Database::getInstance([
    'host' => 'localhost',
    'dbname' => 'test',
    'user' => 'root',
    'pass' => '',
    'charset' => 'utf8mb4',
]);
$pdo = $db->getPDO();
$stmt = $pdo->query('SELECT * FROM users');

mysqli 封裝類別

class MySQLiWrapper {
    private mysqli $mysqli;

    public function __construct(string $host, string $user, string $pass, string $db, int $port = 3306) {
        $this->mysqli = new mysqli($host, $user, $pass, $db, $port);
        if ($this->mysqli->connect_errno) {
            throw new Exception('連線失敗:' . $this->mysqli->connect_error);
        }
        $this->mysqli->set_charset('utf8mb4');
    }

    public function query(string $sql): mysqli_result|bool {
        return $this->mysqli->query($sql);
    }

    public function prepare(string $sql): mysqli_stmt {
        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            throw new Exception('Prepare 失敗:' . $this->mysqli->error);
        }
        return $stmt;
    }

    public function getConnection(): mysqli {
        return $this->mysqli;
    }
}

關鍵最佳實務

  • 參數化查詢:永遠用 prepare() + execute(),防 SQL Injection
  • 錯誤模式:設定例外模式,統一 try-catch 處理
  • 字元集:統一 utf8mb4
  • 單一職責:連線類別只負責連線,查詢邏輯放 Repository/Service 層
  • 依賴注入:避免單例,透過建構子注入 PDO 實例,利於測試

簡易查詢助手方法(可加入類別)

public function fetchAll(string $sql, array $params = []): array {
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

public function fetchOne(string $sql, array $params = []): ?array {
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetch() ?: null;
}

public function execute(string $sql, array $params = []): int {
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->rowCount();
}

public function lastInsertId(): string {
    return $this->pdo->lastInsertId();
}

原始來源:www.111cn.net

copyright © 萬盛學電腦網 all rights reserved