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

PHP 連接多種資料庫完整實戰指南 (MySQL/PostgreSQL/SQL Server/Oracle/SQLite)

編輯：PHP 相關知識

現代 PHP 資料庫連接最佳實踐：PDO

PDO (PHP Data Objects) 是 PHP 官方提供的資料庫存取抽象層，支援 12+ 資料庫驅動，提供預備語句、交易控制、統一錯誤處理，是現代 PHP 專案的標準選擇。

PDO 通用連接範本

<?php
class Database {
    private static ?PDO $instance = null;
    
    public static function getConnection(array $config): PDO {
        if (self::$instance === null) {
            $dsn = self::buildDsn($config);
            self::$instance = new PDO(
                $dsn,
                $config['username'],
                $config['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
                ]
            );
        }
        return self::$instance;
    }
    
    private static function buildDsn(array $config): string {
        $driver = $config['driver'] ?? 'mysql';
        $host = $config['host'] ?? '127.0.0.1';
        $port = $config['port'] ?? self::getDefaultPort($driver);
        $dbname = $config['dbname'] ?? '';
        
        return match($driver) {
            'mysql' => "mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4",
            'pgsql' => "pgsql:host=$host;port=$port;dbname=$dbname",
            'sqlsrv' => "sqlsrv:Server=$host,$port;Database=$dbname",
            'oci' => "oci:dbname=//$host:$port/$dbname",
            'sqlite' => "sqlite:" . ($dbname ?: ':memory:'),
            default => throw new InvalidArgumentException("Unsupported driver: $driver")
        };
    }
    
    private static function getDefaultPort(string $driver): int {
        return match($driver) {
            'mysql' => 3306,
            'pgsql' => 5432,
            'sqlsrv' => 1433,
            'oci' => 1521,
            default => 3306
        };
    }
}
?>

各資料庫專屬配置與注意事項

MySQL / MariaDB (最常用)

<?php
$config = [
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'dbname' => 'myapp',
    'username' => 'app_user',
    'password' => 'secure_password',
    'charset' => 'utf8mb4'  // 支援 emoji
];

$pdo = Database::getConnection($config);

// 建表範例 (InnoDB + utf8mb4)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS users (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(191) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL,
        name VARCHAR(100) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
?>

必須啟用 pdo_mysql 擴展
使用 utf8mb4 而非 utf8 完整支援 Unicode
生產環境建議啟用 SSL：PDO::MYSQL_ATTR_SSL_CA 等選項

PostgreSQL (功能最強大開源資料庫)

<?php
$config = [
    'driver' => 'pgsql',
    'host' => '127.0.0.1',
    'port' => 5432,
    'dbname' => 'myapp',
    'username' => 'app_user',
    'password' => 'secure_password'
];

$pdo = Database::getConnection($config);

// PostgreSQL 專屬功能範例
$pdo->exec("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"");
$pdo->exec("
    CREATE TABLE IF NOT EXISTS events (
        id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
        payload JSONB NOT NULL,
        created_at TIMESTAMPTZ DEFAULT NOW()
    )
");

// JSONB 查詢
$stmt = $pdo->prepare("SELECT * FROM events WHERE payload @> ?");
$stmt->execute([json_encode(['type' => 'order_created'])]);
?>

啟用 pdo_pgsql 擴展
原生支援 UUID、JSONB、陣列、幾何類型、全文搜尋
交易隔離層級控制更精細

Microsoft SQL Server

<?php
// Linux 環境需安裝 Microsoft ODBC Driver + pdo_sqlsrv
$config = [
    'driver' => 'sqlsrv',
    'host' => '192.168.1.100',
    'port' => 1433,
    'dbname' => 'MyDatabase',
    'username' => 'sa',
    'password' => 'StrongPassword123'
];

$pdo = Database::getConnection($config);

// SQL Server 專屬：分頁查詢 (2012+)
$stmt = $pdo->prepare("
    SELECT * FROM Products 
    ORDER BY CreatedDate DESC
    OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
");
$stmt->execute([($page - 1) * $pageSize, $pageSize]);
?>

Windows 內建驅動；Linux 需安裝 Microsoft ODBC Driver + pecl install pdo_sqlsrv
字串長度限制：VARCHAR(MAX)、NVARCHAR(MAX)
日期時間建議使用 DATETIME2、DATETIMEOFFSET

Oracle Database

<?php
// 需安裝 Oracle Instant Client + pdo_oci
$config = [
    'driver' => 'oci',
    'host' => '192.168.1.200',
    'port' => 1521,
    'dbname' => 'ORCLPDB1',  // Service Name 或 SID
    'username' => 'app_user',
    'password' => 'secure_password'
];

$pdo = Database::getConnection($config);

// Oracle 專屬：序列 + 觸發器自動編號
$pdo->exec("CREATE SEQUENCE users_seq START WITH 1 INCREMENT BY 1");
$pdo->exec("
    CREATE OR REPLACE TRIGGER users_bir
    BEFORE INSERT ON users
    FOR EACH ROW
    BEGIN
        SELECT users_seq.NEXTVAL INTO :NEW.id FROM dual;
    END;
");
?>

pdo_oci 編譯較複雜，建議使用 Docker 映像預裝
Oracle 12c+ 支援 JSON、身分欄位
分頁查詢：OFFSET ... ROWS FETCH NEXT ... ROWS ONLY (12c+)

SQLite (嵌入式、零配置、測試首選)

<?php
$config = [
    'driver' => 'sqlite',
    'dbname' => __DIR__ . '/data/app.db'  // 檔案路徑
];

$pdo = Database::getConnection($config);

// SQLite 專屬：記憶體資料庫 (測試極快)
$testPdo = new PDO('sqlite::memory:');
$testPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 啟用外鍵約束 (預設關閉)
$pdo->exec('PRAGMA foreign_keys = ON');
$pdo->exec('PRAGMA journal_mode = WAL'); // 更好併發效能
?>

PHP 內建 pdo_sqlite，無需額外安裝
適合：開發環境、單元測試、小型應用、桌面應用
不適合：高併發寫入、大型資料集、多伺服器共享

驅動安裝快速參考

資料庫	PDO 驅動	Ubuntu/Debian 安裝	Windows
MySQL	pdo_mysql	`apt install php-pdo-mysql`	php.ini 取消註解
PostgreSQL	pdo_pgsql	`apt install php-pdo-pgsql`	php.ini 取消註解
SQL Server	pdo_sqlsrv	ODBC Driver + `pecl install pdo_sqlsrv`	下載 DLL 放入 ext
Oracle	pdo_oci	Instant Client + 重編譯 PHP	較複雜，建議 Docker
SQLite	pdo_sqlite	內建	內建