PHP中MSSQL与MySQL结果集对比同步及脚本排障求助

Hey there! Let’s break down your two PHP database sync problems and fix them step by step.

The core idea here is to identify unique records that exist in MSSQL but not in MySQL, then insert those into MySQL. Here's a practical approach:

• First, grab existing unique IDs from MySQL
  Pick a unique identifier field (like panel_id) that exists in both tables. Fetch all existing IDs from MySQL and store them in an array for quick lookup:

  // Connect to MySQL first (use your existing connection logic)
  $mysqlExistingIds = [];
  $mysqlQuery = "SELECT panel_id FROM PANELS";
  $mysqlResult = mysqli_query($mysqlConn, $mysqlQuery);
  
  while ($row = mysqli_fetch_assoc($mysqlResult)) {
      $mysqlExistingIds[] = $row['panel_id'];
  }
  

• Second, fetch data from MSSQL and filter new rows
  If your MSSQL table has a timestamp field (like created_at), you can optimize by only fetching rows added since your last sync (way faster than pulling all data). If not, fetch all rows, then check which IDs aren’t in the MySQL array:

  // Fetch all rows from MSSQL (use your existing connection method)
  $mssqlQuery = "SELECT * FROM PanleList";
  // Optimized version if you have a sync timestamp:
  // $mssqlQuery = "SELECT * FROM PanleList WHERE created_at > '2024-05-20 10:00:00'";
  
  $mssqlStmt = $mssqlPdo->prepare($mssqlQuery);
  $mssqlStmt->execute();
  $mssqlRows = $mssqlStmt->fetchAll(PDO::FETCH_ASSOC);
  
  // Insert only new rows into MySQL
  foreach ($mssqlRows as $row) {
      if (!in_array($row['panel_id'], $mysqlExistingIds)) {
          // Use prepared statements to avoid SQL injection (safer than string concatenation)
          $insertStmt = mysqli_prepare($mysqlConn, "INSERT INTO PANELS (panel_id, name, description) VALUES (?, ?, ?)");
          mysqli_stmt_bind_param($insertStmt, "sss", $row['panel_id'], $row['name'], $row['description']);
          mysqli_stmt_execute($insertStmt);
      }
  }
  

• Pro tips for better performance

  • Always use prepared statements instead of raw SQL string concatenation to prevent injection and speed up repeated inserts.
  • Track the last sync time (store it in a file or a small MySQL table) so you only query new MSSQL rows each time, reducing data transfer.

It’s super frustrating when a sync script only processes the first row—let’s dig into the most common causes, especially around nested foreach loops:

Common Causes & Fixes #

• Issue 1: You’re not fetching all MSSQL rows upfront
  If you’re using functions like mssql_fetch_assoc() in a loop, and then accidentally moving the result pointer inside a nested loop, you’ll only get the first row. Fix this by loading all MSSQL rows into an array first:

  // ❌ Bad: Fetching rows one by one in the loop (pointer can get messed up)
  while ($mssqlRow = mssql_fetch_assoc($mssqlResult)) {
      // Nested loop here might advance the pointer unexpectedly
  }
  
  // ✅ Good: Load all rows into an array first
  $mssqlRows = [];
  while ($row = mssql_fetch_assoc($mssqlResult)) {
      $mssqlRows[] = $row;
  }
  
  foreach ($mssqlRows as $row) {
      // Your insert logic here (no pointer issues now)
  }
  

• Issue 2: Nested loop is terminating the entire script
  Check if you’re using exit, return, or break 2 (which breaks both loops) inside your nested foreach. If so, replace it with a regular break to only exit the inner loop:

  foreach ($mssqlRows as $mssqlRow) {
      $isExisting = false;
      foreach ($mysqlExistingIds as $id) {
          if ($mssqlRow['panel_id'] == $id) {
              $isExisting = true;
              break; // Only exits the inner loop—correct!
              // ❌ Avoid: exit; or return; here, it stops the whole script
          }
      }
      if (!$isExisting) {
          // Execute insert
      }
  }
  

• Issue 3: Hidden errors on subsequent rows
  The first row inserts fine, but later rows might fail silently (e.g., unique key conflicts, data type mismatches). Add error logging to catch this:

  foreach ($mssqlRows as $row) {
      $insertStmt = mysqli_prepare($mysqlConn, "INSERT INTO PANELS (panel_id, name) VALUES (?, ?)");
      mysqli_stmt_bind_param($insertStmt, "ss", $row['panel_id'], $row['name']);
      
      if (!mysqli_stmt_execute($insertStmt)) {
          // Log the error to a file so you can debug
          error_log("Failed to insert row: " . print_r($row, true) . " | Error: " . mysqli_error($mysqlConn));
      }
  }
  

• Issue 4: Unique key checks are flawed
  Double-check that your unique identifier (like panel_id) is correctly compared. Maybe the data types don’t match (e.g., MSSQL uses INT but MySQL uses VARCHAR), causing false matches. Explicitly cast values if needed:

  if (!in_array((string)$row['panel_id'], $mysqlExistingIds)) {
      // Insert logic
  }
  

内容的提问来源于stack exchange，提问作者Marius Flevie