PHP 將 MSSQL 資料遷移至 MySQL 完整實作指南

前言

資料庫異構遷移是常見需求。本文示範使用 PHP 同步連接 MSSQL 與 MySQL，將資料從 SQL Server 遷移至 MySQL 的完整流程，包含環境設定、資料型別對應、批次處理與錯誤處理。

環境需求

• PHP 7.4+ (建議 8.1+)
• Microsoft ODBC Driver 17/18 for SQL Server
• PDO_SQLSRV / PDO_ODBC 擴展 (連接 MSSQL)
• PDO_MYSQL 擴展 (連接 MySQL)

現代 PDO 實作：雙連線資料遷移

<?php
// 設定參數
$mssqlDsn = "sqlsrv:Server=127.0.0.1;Database=hb";
$mssqlUser = 'sa';
$mssqlPass = '1';

$mysqlDsn = "mysql:host=localhost;dbname=sa;charset=utf8mb4";
$mysqlUser = 'root';
$mysqlPass = 'a';

try {
    // 建立雙連線
    $mssql = new PDO($mssqlDsn, $mssqlUser, $mssqlPass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
    $mysql = new PDO($mysqlDsn, $mysqlUser, $mysqlPass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);

    // 開始交易 (MySQL 端)
    $mysql->beginTransaction();

    // 1. 從 MSSQL 讀取資料
    $stmt = $mssql->query("SELECT id, title FROM bc");
    $batchSize = 100;
    $batch = [];
    $total = 0;

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $batch[] = $row;

        // 批次寫入 MySQL
        if (count($batch) >= $batchSize) {
            insertBatch($mysql, $batch);
            $total += count($batch);
            $batch = [];
            echo "已遷移 $total 筆...<br>";
            if (php_sapi_name() !== 'cli') flush(); // Web 環境即時輸出
        }
    }

    // 處理剩餘資料
    if (!empty($batch)) {
        insertBatch($mysql, $batch);
        $total += count($batch);
    }

    $mysql->commit();
    echo "<br>遷移完成！共 $total 筆資料。";

} catch (PDOException $e) {
    if (isset($mysql)) $mysql->rollBack();
    die("遷移失敗：" . $e->getMessage());
}

// 批次插入函式
function insertBatch(PDO $pdo, array $rows) {
    if (empty($rows)) return;

    $placeholders = implode(',', array_fill(0, count($rows), '(?, ?)'));
    $sql = "INSERT INTO vv (id, title) VALUES $placeholders";

    $stmt = $pdo->prepare($sql);
    $params = [];
    foreach ($rows as $row) {
        $params[] = $row['id'];
        $params[] = $row['title'];
    }
    $stmt->execute($params);
}
?>

關鍵技術點解析

1. 批次插入優化

單筆 INSERT 效能極差。使用多值 INSERT 或 LOAD DATA INFILE 可提升 10-100 倍速度。

2. 資料型別對應表

3. 編碼處理

• MSSQL 使用 nvarchar 儲存 Unicode
• PHP 內部為 UTF-8
• MySQL 連線指定 charset=utf8mb4
• 若出現亂碼，檢查 ODBC 驅動設定與資料庫定序

4. 大量資料遷移策略

• 分批提交：每 100-1000 筆一個交易，平衡效能與回滾成本
• 斷點續傳：記錄已遷移最大 ID，支援中斷後繼續
• 並行處理：大表可按範圍分多程序並行遷移
• 驗證機制：遷移後比對筆數、檢查和、抽樣比對內容

進階工具推薦

對於生產環境大規模遷移，建議使用專業工具：

• Microsoft Data Migration Assistant：官方評估與遷移工具
• AWS Database Migration Service / Azure Database Migration Service：雲端遷移服務
• pt-archiver (Percona Toolkit)：線上歸檔/遷移利器
• Custom ETL 腳本：使用 Python/pandas 或 Node.js 處理複雜轉換邏輯

總結

PHP 雙連線遷移適合中小型資料量、需自訂轉換邏輯的場景。核心在於：批次處理、交易控制、型別對應、編碼一致。大型遷移專案請評估專業 ETL 工具與雲端遷移服務。