PHP MySQL 資料庫存取類封裝：連線池、預處理、錯誤處理與編碼設定

類別設計目標

• 單例/長連線 (mysql_pconnect) 減少握手開銷。
• 自動編碼設定 (SET NAMES gb2312)。
• 統一錯誤處理與日誌。
• 提供常用查詢輔助方法。

完整程式碼 (PHP 5 風格)

<?php
class DB_MYSQL {
    // 配置屬性
    private $Host = 'localhost';
    private $Database = 'db_name';
    private $User = 'user';
    private $Password = 'password';

    // 狀態屬性
    private $Link_Id = 0;
    private $Query_Id = 0;
    private $Row_Result = array();
    private $Field_Result = array();
    private $Affected_Rows = 0;
    private $Rows = 0;
    private $Fields = 0;
    private $Row_Position = 0;
    public $Insert_Id = 0;

    // 建構子：自動連線
    public function __construct() {
        $this->connect();
    }

    // 解構子：釋放資源
    public function __destruct() {
        @mysql_free_result($this->Query_Id);
        mysql_close($this->Link_Id);
    }

    // 連線/選庫
    public function connect($Database='', $Host='', $User='', $Password='') {
        $Database = $Database ?: $this->Database;
        $Host = $Host ?: $this->Host;
        $User = $User ?: $this->User;
        $Password = $Password ?: $this->Password;

        if (!$this->Link_Id) {
            $this->Link_Id = @mysql_pconnect($Host, $User, $Password);
            if (!$this->Link_Id) $this->halt('連線資料庫伺服器失敗!');
            if (!mysql_select_db($Database, $this->Link_Id))
                $this->halt('無法開啟指定資料庫: '.$Database);
        }
        return $this->Link_Id;
    }

    // 釋放結果集記憶體
    public function free() {
        if (@mysql_free_result($this->Query_Id))
            unset($this->Row_Result);
        $this->Query_Id = 0;
    }

    // 執行查詢
    public function query($sql) {
        if ($this->Query_Id) $this->free();
        if (!$this->Link_Id) $this->connect();

        @mysql_query('SET NAMES gb2312', $this->Link_Id);
        $this->Query_Id = mysql_query($sql, $this->Link_Id);
        $this->Insert_Id = mysql_insert_id($this->Link_Id);

        if (!$this->Query_Id) $this->halt('SQL 錯誤: '.$sql);
        @mysql_query('SET NAMES gb2312', $this->Link_Id);
        return $this->Query_Id;
    }

    // 結果集指標定位
    public function seek($pos) {
        if (@mysql_data_seek($this->Query_Id, $pos)) {
            $this->Row_Position = $pos;
            return true;
        }
        $this->halt('定位結果集錯誤!');
        return false;
    }

    // 取得所有行 (關聯+數字索引陣列)
    public function get_rows_array() {
        $this->get_rows();
        for ($i=0; $i<$this->Rows; $i++) {
            if (!mysql_data_seek($this->Query_Id, $i))
                $this->halt('mysql_data_seek 錯誤!');
            $this->Row_Result[$i] = mysql_fetch_array($this->Query_Id);
        }
        return $this->Row_Result;
    }

    // 取得欄位名陣列
    public function get_fields_array() {
        $this->get_fields();
        for ($i=0; $i<$this->Fields; $i++) {
            $obj = mysql_fetch_field($this->Query_Id, $i);
            $this->Field_Result[$i] = $obj->name;
        }
        return $this->Field_Result;
    }

    // 影響行數
    public function get_affected_rows() {
        $this->Affected_Rows = mysql_affected_rows($this->Link_Id);
        return $this->Affected_Rows;
    }

    // 結果集行數
    public function get_rows() {
        $this->Rows = mysql_num_rows($this->Query_Id);
        return $this->Rows;
    }

    // 結果集欄位數
    public function get_fields() {
        $this->Fields = mysql_num_fields($this->Query_Id);
        return $this->Fields;
    }

    // 單行查詢便利方法
    public function fetch_one_array($sql) {
        @mysql_query('SET NAMES gb2312', $this->Link_Id);
        $this->query($sql);
        return mysql_fetch_array($this->Query_Id);
    }

    // 錯誤處理
    private function halt($msg) {
        $this->Error = mysql_error($this->Link_Id);
        printf("資料庫錯誤: %s
\n", $msg);
        printf("MySQL 訊息: %s
\n", $this->Error);
        printf("錯誤頁面: %s
\n", $_SERVER['PHP_SELF'], $_SERVER['PHP_SELF']);
        die('腳本終止');
    }
}
?>

使用範例

$db = new DB_MYSQL();

// 查詢多行
$db->query("SELECT * FROM users WHERE status=1");
$rows = $db->get_rows_array();
foreach ($rows as $row) {
    echo $row['name'] . "
";
}

// 單行查詢
$user = $db->fetch_one_array("SELECT * FROM users WHERE id=10");

// 寫入操作
$db->query("INSERT INTO logs (msg) VALUES ('test')");
echo "新增 ID: " . $db->Insert_Id;
echo "影響行數: " . $db->get_affected_rows();

現代化改造建議 (PHP 7+/8+)

PDO 版本骨架

class DB {
    private $pdo;
    public function __construct($dsn, $user, $pass) {
        $this->pdo = new PDO($dsn, $user, $pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }
    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);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
    public function lastInsertId() { return $this->pdo->lastInsertId(); }
}

總結

此類別展示了經典 PHP MySQL 封裝核心邏輯，但新專案請直接使用 PDO 或 mysqli，享受預處理、參數綁定、例外處理與 utf8mb4 完整支援，避免 SQL 注入與編碼坑點。