您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP 單例模式實現資料庫操作基類：最佳實踐與現代化重構

編輯：PHP 綜合知識

前言

在 PHP 開發中，資料庫操作是核心基礎功能。本文將深入解析基於單例模式的資料庫操作基類實現，並針對原始碼中使用的已棄用 mysql_* 函式進行現代化重構，升級為 PDO 或 mysqli，確保程式碼符合現代 PHP 標準（PHP 7.0+），同時保留單例模式的核心優勢：全域唯一實例、延遲初始化與資源復用。

單例模式在資料庫連線中的應用價值

單例模式確保資料庫連線物件在整個應用程式生命週期中只存在一個實例，避免重複建立連線造成的效能開銷與連線數耗盡風險。核心實現要點：

私有化建構子（__construct）防止外部實例化
靜態私有屬性 $instance 儲存唯一實例
公開靜態方法 getInstance() 提供全域存取點
防止複製（__clone）與反序列化（__wakeup）破壞單例

配置檔案最佳實踐

建議採用 .env 或 config.php 返回陣列的方式管理敏感資訊：

<?php
// config/database.php
return [
    'host'     => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'test',
    'charset'  => 'utf8mb4',
    'options'  => [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ],
];

現代化 PDO 版資料庫基類實現

<?php
declare(strict_types=1);

namespace App\Database;

use PDO;
use PDOException;

class Database
{
    private static ?self $instance = null;
    private PDO $connection;
    private string $lastSql = '';

    private function __construct(array $config)
    {
        $dsn = "mysql:host={$config['host']};dbname={$config['database']};charset={$config['charset']}";
        try {
            $this->connection = new PDO($dsn, $config['username'], $config['password'], $config['options'] ?? []);
        } catch (PDOException $e) {
            throw new \RuntimeException("資料庫連線失敗: {$e->getMessage()}", (int)$e->getCode(), $e);
        }
    }

    public static function getInstance(array $config = []): self
    {
        if (self::$instance === null) {
            if (empty($config)) {
                $config = require __DIR__ . '/../config/database.php';
            }
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    // 禁止複製與反序列化
    private function __clone() {}
    public function __wakeup() { throw new \Exception("單例模式不支援反序列化"); }

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

    public function getLastSql(): string
    {
        return $this->lastSql;
    }

    /** 查詢多筆資料 */
    public function select(string $table, array $conditions = [], array $fields = []): array
    {
        $fieldStr = $fields ? implode(', ', array_map(fn($f) => "`{$f}`", $fields)) : '*';
        $whereClause = '';
        $params = [];

        if ($conditions) {
            $whereParts = [];
            foreach ($conditions as $key => $value) {
                $whereParts[] = "`{$key}` = ?";
                $params[] = $value;
            }
            $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
        }

        $this->lastSql = "SELECT {$fieldStr} FROM `{$table}` {$whereClause}";
        $stmt = $this->connection->prepare($this->lastSql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /** 新增單筆資料 */
    public function insert(string $table, array $data): int|false
    {
        $columns = implode(', ', array_map(fn($k) => "`{$k}`", array_keys($data)));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $this->lastSql = "INSERT INTO `{$table}` ({$columns}) VALUES ({$placeholders})";
        
        $stmt = $this->connection->prepare($this->lastSql);
        $result = $stmt->execute(array_values($data));
        return $result ? (int)$this->connection->lastInsertId() : false;
    }

    /** 更新資料 */
    public function update(string $table, array $data, array $conditions = []): int|false
    {
        if (empty($data)) return false;
        
        $setParts = [];
        $params = [];
        foreach ($data as $key => $value) {
            $setParts[] = "`{$key}` = ?";
            $params[] = $value;
        }

        $whereClause = '';
        if ($conditions) {
            $whereParts = [];
            foreach ($conditions as $key => $value) {
                $whereParts[] = "`{$key}` = ?";
                $params[] = $value;
            }
            $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
        }

        $this->lastSql = "UPDATE `{$table}` SET " . implode(', ', $setParts) . " {$whereClause}";
        $stmt = $this->connection->prepare($this->lastSql);
        return $stmt->execute($params) ? $stmt->rowCount() : false;
    }

    /** 刪除資料 */
    public function delete(string $table, array $conditions = []): int|false
    {
        $whereClause = '';
        $params = [];
        if ($conditions) {
            $whereParts = [];
            foreach ($conditions as $key => $value) {
                $whereParts[] = "`{$key}` = ?";
                $params[] = $value;
            }
            $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
        }

        $this->lastSql = "DELETE FROM `{$table}` {$whereClause}";
        $stmt = $this->connection->prepare($this->lastSql);
        return $stmt->execute($params) ? $stmt->rowCount() : false;
    }
}

使用範例

<?php
require_once 'vendor/autoload.php';

use App\Database\Database;

$db = Database::getInstance();

// 查詢
$users = $db->select('users', ['status' => 1], ['id', 'name', 'email']);

// 新增
$userId = $db->insert('users', [
    'name'     => '張三',
    'email'    => 'zhangsan@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
    'status'   => 1,
]);

// 更新
$affected = $db->update('users', ['name' => '李四'], ['id' => $userId]);

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

// 除錯：查看最後執行的 SQL
echo $db->getLastSql();