PHP 使用 PDO 連接 MSSQL Server：跨平台資料庫存取實戰

PDO_SQLSRV 驅動優勢

• 統一 PDO 介面，程式碼可移植至 MySQL/PostgreSQL/SQLite
• 支援預處理語句、參數綁定、事務控制
• Windows/Linux/macOS (ODBC Driver 17/18) 全平台支援

環境準備

1. 安裝 php_pdo_sqlsrv.dll (Windows) 或 pdo_sqlsrv.so (Linux)
2. 安裝 Microsoft ODBC Driver 17/18 for SQL Server
3. php.ini 啟用：extension=pdo_sqlsrv

基礎連線與查詢

<?php
$dsn = 'sqlsrv:Server=192.168.1.106;Database=bookStore_demo;CharacterSet=UTF-8';
$user = 'sa';
$password = '123';

try {
    $pdo = new PDO($dsn, $user, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    
    $sql = 'SELECT * FROM article';
    $stmt = $pdo->query($sql);
    $results = $stmt->fetchAll();
    
    var_dump($results);
    
} catch (PDOException $e) {
    die('連線失敗: ' . $e->getMessage());
}
?>

進階封裝：資料庫操作類別

<?php
class DB extends PDO {
    private static $instance = null;
    
    private function __construct() {
        $dsn = "sqlsrv:Server=" . getenv('DB_HOST') . ";Database=" . getenv('DB_NAME') . ";CharacterSet=UTF-8";
        parent::__construct($dsn, getenv('DB_USER'), getenv('DB_PASS'), [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    // 安全查詢：自動參數綁定
    public function query($sql, $params = []) {
        $stmt = $this->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
    
    // 單筆查詢
    public function fetchOne($sql, $params = []) {
        return $this->query($sql, $params)->fetch();
    }
    
    // 多筆查詢
    public function fetchAll($sql, $params = []) {
        return $this->query($sql, $params)->fetchAll();
    }
    
    // 執行增刪改
    public function execute($sql, $params = []) {
        return $this->query($sql, $params)->rowCount();
    }
    
    // 最後插入 ID
    public function lastInsertId($sequence = null) {
        return parent::lastInsertId($sequence);
    }
}

// 使用範例
$db = DB::getInstance();

// 查詢
$articles = $db->fetchAll("SELECT * FROM article WHERE status = ? AND category = ?", [1, 'tech']);

// 新增
$db->execute(
    "INSERT INTO article (title, content, author_id) VALUES (?, ?, ?)",
    ['標題', '內容', 1001]
);
$new_id = $db->lastInsertId();

// 更新
$db->execute("UPDATE article SET views = views + 1 WHERE id = ?", [$new_id]);

// 刪除
$db->execute("DELETE FROM article WHERE id = ?", [43]);
?>

參數綁定型別對照

明確指定型別：$stmt->bindValue(':id', $id, PDO::PARAM_INT);

事務控制

<?php
try {
    $pdo->beginTransaction();
    
    $pdo->execute("INSERT INTO orders (user_id, total) VALUES (?, ?)", [1001, 999.00]);
    $order_id = $pdo->lastInsertId();
    
    foreach ($items as $item) {
        $pdo->execute(
            "INSERT INTO order_items (order_id, product_id, qty, price) VALUES (?, ?, ?, ?)",
            [$order_id, $item['pid'], $item['qty'], $item['price']]
        );
    }
    
    $pdo->commit();
    echo "訂單建立成功，ID: $order_id";
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "訂單建立失敗: " . $e->getMessage();
}
?>

常見問題 FAQ

Q: 連線字串 Server 參數怎麼寫？

Server=ip,port (如 192.168.1.100,1433) 或 Server=(local) (本機命名管道)

Q: 如何處理 NVARCHAR(MAX) 長文本？

PDO_SQLSRV 5.6+ 自動處理，舊版需 CAST(col AS NTEXT)

Q: Linux 連線失敗 "Communication link failure"

檢查 /etc/odbcinst.ini、/etc/odbc.ini 配置、防火牆、SELinux、ODBC Driver 版本

Q: 預處理語句報錯 "Invalid cursor state"

同一連線上一個預處理未關閉 (closeCursor()) 即建立新的，確保執行完畢再準備下一條