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

PHP MySQL 資料庫操作類封裝：連線管理、查詢執行與調試支援

編輯：PHP 綜合知識

在現代 PHP 開發中，推薦使用 PDO 或成熟 ORM（如 Eloquent、Doctrine）。但理解原生 MySQL 擴展的封裝思路，有助於掌握資料庫抽象層的設計原理。本文解析一個經典的 DBCLS 類實現，涵蓋連線字串解析、連線/選庫管理、查詢執行、調試資訊收集等核心功能。

類結構設計

class DBCLS {
    // 配置屬性
    public $debug = true;
    public $debuginfo = "debug informations:\r\n";
    
    public $db_server = '';
    public $db_name = '';
    public $db_user = '';
    public $db_passwd = '';
    
    // 運行時資源
    public $db_link = null;
    public $db_query_sql = 'show tables';
    public $db_recordset = null;
    
    // 狀態標記
    public $conntstr_ready = false;
    public $link_ready = false;
    public $db_ready = false;
    // ...
}

核心方法解析

1. 連線字串解析與驗證

function connection_string($connstr = "") {
    if (empty($connstr)) {
        // 獲取當前配置
        return "Server={$this->db_server};DataBase={$this->db_name};UserID={$this->db_user};PassWord={$this->db_passwd}";
    }
    
    // 解析格式：Server=xxx;DataBase=yyy;UserID=zzz;PassWord=www;
    preg_match_all("|([a-zA-Z0-9]*)=([a-zA-Z0-9]*);*|", $connstr, $matches);
    
    $this->db_server = (strtolower($matches[1][0]) == 'server') ? $matches[2][0] : '';
    $this->db_name   = (strtolower($matches[1][1]) == 'database') ? $matches[2][1] : '';
    $this->db_user   = (strtolower($matches[1][2]) == 'userid') ? $matches[2][2] : '';
    $this->db_passwd = (strtolower($matches[1][3]) == 'password') ? $matches[2][3] : '';
    
    $this->conntstr_ready = !empty($this->db_server) && !empty($this->db_name) 
                         && !empty($this->db_user) && !empty($this->db_passwd);
    return $this->conntstr_ready;
}

2. 連線與選庫

function connect() {
    if (!$this->conntstr_ready) {
        $this->debuginfo .= "連線字串無效！\r\n";
        return false;
    }
    
    $this->db_link = @mysql_connect($this->db_server, $this->db_user, $this->db_passwd);
    if ($this->db_link) {
        $this->link_ready = true;
        return true;
    }
    $this->debuginfo .= "資料庫連線失敗：" . mysql_error() . "\r\n";
    return false;
}

function select($db = "") {
    if (!$this->db_link) {
        $this->debuginfo .= "無可用資料庫連線！\r\n";
        return false;
    }
    
    $target_db = $db ?: $this->db_name;
    if (mysql_select_db($target_db, $this->db_link)) {
        $this->db_name = $target_db;
        $this->db_ready = true;
        return true;
    }
    $this->debuginfo .= "選擇資料庫失敗：" . mysql_error() . "\r\n";
    return false;
}

3. 查詢執行

function execute($SQL = "") {
    if (!empty($SQL)) {
        $this->db_query_sql = $SQL;
    }
    
    if (empty($this->db_query_sql)) {
        $this->debuginfo .= "SQL 為空！\r\n";
        return false;
    }
    
    $this->db_recordset = mysql_query($this->db_query_sql, $this->db_link);
    if ($this->db_recordset === false) {
        $this->debuginfo .= "查詢失敗：" . mysql_error() . "\r\nSQL: {$this->db_query_sql}\r\n";
        return false;
    }
    return true;
}

使用示例

<?php
require_once 'DBCLS.class.php';

$db = new DBCLS();
$db->debug = true;

// 初始化連線配置
if (!$db->connection_string("Server=localhost;DataBase=testdb;UserID=root;PassWord=123456")) {
    die($db->debuginfo);
}

// 連線並選庫
if (!$db->connect() || !$db->select()) {
    die($db->debuginfo);
}

// 執行查詢
if ($db->execute("SELECT id, username, email FROM users WHERE status = 1 LIMIT 10")) {
    while ($row = mysql_fetch_assoc($db->db_recordset)) {
        echo "ID: {$row['id']}, User: {$row['username']}, Email: {$row['email']}
";
    }
    mysql_free_result($db->db_recordset);
}

// 關閉連線（腳本結束自動釋放，長運行腳本建議顯式關閉）
mysql_close($db->db_link);

遷移建議：從 mysql_* 到 PDO

mysql_* 函數已在 PHP 7.0 移除，新項目請直接使用 PDO：

<?php
class PDODatabase {
    private $pdo;
    
    public function __construct($dsn, $user, $pass, $options = []) {
        $this->pdo = new PDO($dsn, $user, $pass, array_merge([
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ], $options));
    }
    
    public function query($sql, $params = []) {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    public function execute($sql, $params = []) {
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }
}

// 使用
$db = new PDODatabase('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'root', '123456');
$users = $db->query("SELECT * FROM users WHERE status = ?", [1]);