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

PHP 多資料庫抽象操作類：支援 MySQL、MSSQL、PostgreSQL

編輯：PHP 相關知識

PHP 多資料庫抽象操作類：支援 MySQL、MSSQL、PostgreSQL

早期 PHP 專案常需支援多種資料庫，本文提供一個統一介面的資料庫操作類，封裝連線、查詢、結果集游標操作等常用功能，透過切換驅動類型即可無縫遷移。

類別設計目標

統一介面：相同方法名稱操作不同資料庫
支援三大資料庫：MySQL、MSSQL (SQL Server)、PostgreSQL
提供游標操作：首筆、末筆、上一筆、下一筆、指定筆
資源自動管理

核心程式碼

<?php
class mDatabase {
    private $dbType;      // mysql, mssql, pg
    private $connectType; // c=一般, p=持久連線
    private $conn;        // 連線資源
    private $curRow = []; // 當前列號 (per result)
    private $seek = [];   // seek 位置 (per result)

    /**
     * 開啟連線
     * @param string $dbType     mysql|mssql|pg
     * @param string $connectType c|p
     * @param string $host       主機/連線字串
     * @param string $user       帳號
     * @param string $pass       密碼
     * @return resource|false
     */
    public function Open($dbType, $connectType, $host, $user = '', $pass = '') {
        $this->dbType = $dbType;
        $this->connectType = $connectType;

        $func = ($connectType === 'p') ? $dbType . '_pconnect' : $dbType . '_connect';

        switch ($dbType) {
            case 'mssql':
                $this->conn = $func($host, $user, $pass);
                break;
            case 'mysql':
                $this->conn = $func($host, $user, $pass);
                break;
            case 'pg':
                $connStr = $host . " user=$user password=$pass";
                $this->conn = $func($connStr);
                break;
            default:
                return false;
        }
        return $this->conn;
    }

    /** 關閉連線 */
    public function Close() {
        $func = $this->dbType . '_close';
        return $func($this->conn);
    }

    /** 選擇資料庫 (MSSQL/MySQL) */
    public function SelectDb($dbName) {
        if ($this->dbType === 'pg') return false; // PG 在連線字串指定
        $func = $this->dbType . '_select_db';
        return $func($dbName, $this->conn);
    }

    /** 執行查詢 */
    public function Query($sql) {
        $func = $this->dbType . '_query';
        $result = ($this->dbType === 'pg')
            ? $func($this->conn, $sql)
            : $func($sql, $this->conn);

        $this->curRow[(int)$result] = 0;
        $this->seek[(int)$result] = 0;
        return $result;
    }

    /** 移動游標 */
    public function DataSeek($result, $rowNumber) {
        if ($this->dbType === 'pg') return false; // PG 用 fetch 時指定行號
        $func = $this->dbType . '_data_seek';
        $ok = $func($result, $rowNumber);
        $this->seek[(int)$result] = (int)$rowNumber;
        return $ok;
    }

    /** 取得欄位名 */
    public function FieldName($result, $offset) {
        $func = $this->dbType . '_field_name';
        return ($this->dbType === 'pg') ? pg_fieldname($result, $offset) : $func($result, $offset);
    }

    /** 取得欄位型別 */
    public function FieldType($result, $offset) {
        $func = $this->dbType . '_field_type';
        return ($this->dbType === 'pg') ? pg_fieldtype($result, $offset) : $func($result, $offset);
    }

    /** 取得欄位長度 */
    public function FieldLength($result, $offset) {
        $map = ['mysql' => 'mysql_field_len', 'mssql' => 'mssql_field_length', 'pg' => 'pg_fieldsize'];
        return $map[$this->dbType]($result, $offset);
    }

    /** 取得數值索引陣列 */
    public function FetchRow($result, $rowNumber = 0) {
        if ($this->dbType === 'pg') {
            $row = pg_fetch_row($result, $rowNumber);
            if ($row) { $this->curRow[(int)$result] = $this->seek[(int)$result] = $rowNumber; }
            return $row;
        }
        $func = $this->dbType . '_fetch_row';
        $row = $func($result);
        if ($row) $this->curRow[(int)$result]++;
        return $row;
    }

    /** 取得關聯+數值索引陣列 */
    public function FetchArray($result, $rowNumber = 0, $resultType = 2) {
        if ($this->dbType === 'pg') {
            $row = pg_fetch_array($result, $rowNumber, $resultType);
            if ($row) { $this->curRow[(int)$result] = $this->seek[(int)$result] = $rowNumber; }
            return $row;
        }
        $func = $this->dbType . '_fetch_array';
        $row = $func($result);
        if ($row) $this->curRow[(int)$result]++;
        return $row;
    }

    /** 取得物件 */
    public function FetchObject($result, $rowNumber = 0, $resultType = 2) {
        if ($this->dbType === 'pg') {
            $obj = pg_fetch_object($result, $rowNumber, $resultType);
            if ($obj) { $this->curRow[(int)$result] = $this->seek[(int)$result] = $rowNumber; }
            return $obj;
        }
        $func = $this->dbType . '_fetch_object';
        $obj = $func($result);
        if ($obj) $this->curRow[(int)$result]++;
        return $obj;
    }

    /** 取得單一欄位值 */
    public function Result($result, $rowNumber, $fieldName) {
        $func = $this->dbType . '_result';
        return $func($result, $rowNumber, $fieldName);
    }

    /** 釋放結果集 */
    public function FreeResult($result) {
        $func = $this->dbType . '_free_result';
        return $func($result);
    }

    /** 取得列數 */
    public function RowsNumber($result) {
        $func = $this->dbType . '_num_rows';
        return ($this->dbType === 'pg') ? pg_numrows($result) : $func($result);
    }

    /** 取得欄位數 */
    public function FieldsNumber($result) {
        $func = $this->dbType . '_num_fields';
        return ($this->dbType === 'pg') ? pg_numfields($result) : $func($result);
    }

    /** 當前列號 (0-based) */
    public function CurRecNumber($result) {
        return $this->curRow[(int)$result] ?? 0;
    }

    /** 當前行號 (1-based) */
    public function RecordNumber($result) {
        return $this->CurRecNumber($result) + 1;
    }

    /** 移至首筆 */
    public function MoveFirstRec($result) {
        if ($this->dbType === 'pg') return $this->FetchRow($result, 0);
        $this->DataSeek($result, 0);
        return $this->FetchRow($result);
    }

    /** 移至末筆 */
    public function MoveLastRec($result) {
        $count = $this->RowsNumber($result);
        if (!$count) return false;
        $last = $count - 1;
        if ($this->dbType === 'pg') return $this->FetchRow($result, $last);
        $this->DataSeek($result, $last);
        return $this->FetchRow($result);
    }

    /** 移至上一筆 */
    public function MovePreviousRec($result) {
        $cur = $this->CurRecNumber($result);
        if ($cur === 0) return false;
        $prev = $cur - 1;
        if ($this->dbType === 'pg') return $this->FetchRow($result, $prev);
        $this->DataSeek($result, $prev);
        return $this->FetchRow($result);
    }

    /** 移至下一筆 */
    public function MoveNextRec($result) {
        $cur = $this->CurRecNumber($result);
        $total = $this->RowsNumber($result);
        if ($cur + 1 >= $total) return false;
        $next = $cur + 1;
        if ($this->dbType === 'pg') return $this->FetchRow($result, $next);
        $row = $this->FetchRow($result);
        if ($row) { $this->curRow[(int)$result]++; $this->seek[(int)$result] = $this->curRow[(int)$result]; }
        return $row;
    }

    /** 移至指定筆 (1-based) */
    public function MoveToRec($result, $rowNumber) {
        $total = $this->RowsNumber($result);
        if ($rowNumber < 1 || $rowNumber > $total) return false;
        $idx = $rowNumber - 1;
        if ($this->dbType === 'pg') return $this->FetchRow($result, $idx);
        $this->DataSeek($result, $idx);
        return $this->FetchRow($result);
    }
}
?>

使用範例

<?php
// 設定：只需修改這三行即可切換資料庫
$DB_TYPE = 'mysql';      // mysql | mssql | pg
$DB_HOST = 'localhost';  // PG 可用 "host=localhost port=5432 dbname=test"
$DB_NAME = 'mydb';

$db = new mDatabase();
$db->Open($DB_TYPE, 'c', $DB_HOST, 'user', 'pass');
if ($DB_TYPE !== 'pg') $db->SelectDb($DB_NAME);

$result = $db->Query("SELECT id, name, email FROM users WHERE status=1");

echo "共 {$db->RowsNumber($result)} 筆資料\n";

while ($row = $db->FetchArray($result)) {
    echo "ID:{$row['id']} Name:{$row['name']} Email:{$row['email']}\n";
}

$db->FreeResult($result);
$db->Close();
?>

方法對照表

功能	MySQL	MSSQL	PostgreSQL
連線	mysql_connect	mssql_connect	pg_connect
持久連線	mysql_pconnect	mssql_pconnect	pg_pconnect
查詢	mysql_query	mssql_query	pg_exec
游標移動	mysql_data_seek	mssql_data_seek	不支援 (fetch 時指定)
取得列數	mysql_num_rows	mssql_num_rows	pg_numrows
釋放結果	mysql_free_result	mssql_free_result	pg_freeresult

現代化建議：改用 PDO

此類別基於已移除的 mysql_*、mssql_* 擴展（PHP 7+ 不可用）。新專案強烈建議使用 PDO：

原生支援 MySQL、PostgreSQL、SQL Server (sqlsrv)、SQLite、Oracle 等
統一介面、Prepared Statement、例外處理、交易控制
無需自行封裝切換邏輯

<?php
// PDO 通用寫法
$dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4"; // 或 pgsql: / sqlsrv:
$pdo = new PDO($dsn, 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$stmt = $pdo->query("SELECT * FROM users");
foreach ($stmt as $row) { /* ... */ }
?>

本類別適合維護舊版 PHP (≤5.6) 專案參考，新開發請直接採用 PDO。