萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 連接 MSSQL (SQL Server) 完整指南:sqlsrv/PDO 驅動、連線字串、CRUD 操作與類別封裝

環境需求 (PHP 7.4+ / 8.0+)

  1. 安裝 Microsoft ODBC Driver for SQL Server (v17/v18)
  2. 安裝 PHP 驅動:
    • Linux/macOS: pecl install sqlsrv pdo_sqlsrv
    • Windows: 下載對應 PHP 版本的 php_sqlsrv.dll, php_pdo_sqlsrv.dll 放入 ext 目錄
  3. 啟用擴充套件:`php.ini` 加入 `extension=sqlsrv`, `extension=pdo_sqlsrv`
  4. 重啟 Web Server / PHP-FPM

連線方式比較

方式驅動特點推薦度
PDO::sqlsrvpdo_sqlsrv標準 PDO 介面、支援多資料庫、參數綁定完善⭐⭐⭐⭐⭐ (首選)
sqlsrv_* 函數sqlsrv程序式、Microsoft 官方、功能完整⭐⭐⭐ (維舊專案)
mssql_* 函數php_mssql.dll❌ PHP 7.0 移除、僅支援舊版 SQL Server❌ 禁用

PDO 連線字串 (DSN) 完整格式

<?php
$dsn = 'sqlsrv:Server=192.168.1.100,1433;Database=MyDB;Encrypt=true;TrustServerCertificate=true;';
$user = 'sa';
$pass = 'StrongPassword123';

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
    PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8,
    PDO::ATTR_CONNECTION_POOLING => true,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "連線成功";
} catch (PDOException $e) {
    die("連線失敗: " . $e->getMessage());
}
?>

DSN 參數詳解

參數說明範例
Server伺服器 IP/主機名,可指定埠號192.168.1.100,1433 或 tcp:host,1433
Database預設資料庫MyDB
Encrypt強制加密連線 (Azure SQL 必須 true)true/false
TrustServerCertificate信任伺服器憑證 (開發/自簽憑證用)true/false
LoginTimeout登入逾時秒數30
ConnectionPooling連線池 (1/0)1
ApplicationIntentReadWrite/ReadOnly (可用性群組)ReadOnly
MultiSubnetFailover多子網故障轉移true

CRUD 核心操作 (PDO)

查詢 (SELECT)

<?php
// 單筆
$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE id = ?');
$stmt->execute([$id]);
$user = $stmt->fetch();

// 多筆
$stmt = $pdo->prepare('SELECT * FROM users WHERE status = ? ORDER BY created_at DESC LIMIT ? OFFSET ?');
$stmt->execute([1, 20, 0]);
$users = $stmt->fetchAll();

// 計數
$count = $pdo->query('SELECT COUNT(*) FROM users WHERE status = 1')->fetchColumn();
?>

新增 (INSERT) 並取得自增 ID

<?php
$stmt = $pdo->prepare('INSERT INTO users (name, email, created_at) VALUES (?, ?, GETDATE())');
$stmt->execute(['John', 'john@example.com']);
$newId = $pdo->lastInsertId(); // SCOPE_IDENTITY()
echo "新增 ID: $newId";
?>

更新 (UPDATE)

<?php
$stmt = $pdo->prepare('UPDATE users SET email = ?, updated_at = GETDATE() WHERE id = ?');
$affected = $stmt->execute(['new@example.com', $id]);
echo "影響行數: $affected";
?>

刪除 (DELETE)

<?php
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$stmt->execute([$id]);
?>

交易控制

<?php
try {
    $pdo->beginTransaction();
    
    $pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)')->execute([$uid, $total]);
    $orderId = $pdo->lastInsertId();
    
    foreach ($items as $item) {
        $pdo->prepare('INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)')
            ->execute([$orderId, $item['pid'], $item['qty']]);
    }
    
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}
?>

sqlsrv 程序式 API 範例 (維舊專案參考)

<?php
$conn = sqlsrv_connect('192.168.1.100', [
    'Database' => 'MyDB',
    'UID' => 'sa',
    'PWD' => 'pass',
    'CharacterSet' => 'UTF-8',
    'Encrypt' => true,
    'TrustServerCertificate' => true,
]);

if (!$conn) die(print_r(sqlsrv_errors(), true));

// 查詢
$stmt = sqlsrv_query($conn, 'SELECT * FROM users WHERE id = ?', [$id]);
if ($stmt) {
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        print_r($row);
    }
    sqlsrv_free_stmt($stmt);
}

// 參數化查詢
$stmt = sqlsrv_prepare($conn, 'INSERT INTO users (name) VALUES (?)', [&$name]);
$name = 'Test';
sqlsrv_execute($stmt);

// 關閉
sqlsrv_close($conn);
?>

封裝類別:MssqlDatabase (單例模式)

<?php
class MssqlDatabase {
    private static ?self $instance = null;
    private PDO $pdo;
    
    private function __construct(array $config) {
        $dsn = "sqlsrv:Server={$config['host']},{$config['port']};Database={$config['db']};Encrypt={$config['encrypt']};TrustServerCertificate={$config['trust_cert']};";
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }
    
    public static function getInstance(array $config = []): self {
        if (!self::$instance) {
            self::$instance = new self($config ?: config('database.mssql'));
        }
        return self::$instance;
    }
    
    public function query(string $sql, array $params = []): array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    public function execute(string $sql, array $params = []): int {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
    
    public function insert(string $table, array $data): string|int {
        $cols = implode(',', array_keys($data));
        $placeholders = ':' . implode(',:', array_keys($data));
        $sql = "INSERT INTO $table ($cols) VALUES ($placeholders)";
        $this->pdo->prepare($sql)->execute($data);
        return $this->pdo->lastInsertId();
    }
    
    public function pdo(): PDO { return $this->pdo; }
}

// 使用
$db = MssqlDatabase::getInstance([
    'host' => '192.168.1.100',
    'port' => 1433,
    'db' => 'MyDB',
    'user' => 'sa',
    'pass' => 'pass',
    'encrypt' => 'true',
    'trust_cert' => 'true',
]);

$users = $db->query('SELECT TOP 10 * FROM users WHERE status = ?', [1]);
?>

常見問題排查

錯誤原因解決
could not find driver未安裝/啟用 pdo_sqlsrvpecl install + php.ini 啟用
SSL Provider: certificate verify failed加密連線憑證驗證失敗TrustServerCertificate=true 或安裝 CA 憑證
Login timeout expired防火牆/網路不通/埠號錯誤telnet 測試 1433、檢查 SQL Server 設定
Unicode data error字元編碼不匹配DSN 加 CharacterSet=UTF-8、資料庫欄位用 nvarchar
copyright © 萬盛學電腦網 all rights reserved