環境需求 (PHP 7.4+ / 8.0+)
- 安裝 Microsoft ODBC Driver for SQL Server (v17/v18)
- 安裝 PHP 驅動:
- Linux/macOS:
pecl install sqlsrv pdo_sqlsrv
- Windows: 下載對應 PHP 版本的
php_sqlsrv.dll, php_pdo_sqlsrv.dll 放入 ext 目錄
- 啟用擴充套件:`php.ini` 加入 `extension=sqlsrv`, `extension=pdo_sqlsrv`
- 重啟 Web Server / PHP-FPM
連線方式比較
| 方式 | 驅動 | 特點 | 推薦度 |
| PDO::sqlsrv | pdo_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 |
| ApplicationIntent | ReadWrite/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_sqlsrv | pecl 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 |