测试2个mysql数据库之间的内容

3 回答

• 0

  这将从 db1.table1 中找到 db2.table1 和 db2.table2 中没有匹配项的任何 Id 行 .

  它假定两个表中的列名称相同，并且 db2.table1 或 db2.table2 中存在的任何列应在 db1.table1 中具有匹配的列名称 . 因此，如果 db2.table2 具有名为 Foo 的列，则 db1.table1 也必须具有名为 Foo 的列 . 如果 db2.table1 具有名为 Bar 的列，则 db1.table1 也必须具有名为 Bar 的列 . 如果该列存在于 db2 但不存在于 db1 中，则会出现MySQL错误 .

  希望这是你想要的！

  header("Content-type: text/plain");
  
  // connect with mysqli
  
  // get a list of columns in db2.table1 and db2.table2
  $columns = array();
  $query = mysqli_query("SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = 'db2' AND table_name IN ('table1', 'table2')");
  while ($row = $mysqli_fetch_assoc($query)) {
      $columns[$row["table_name"]][] = "db1.table1.{$row["column_name"]} = db2.{$row["table_name"]}.{$row["column_name"]}";
  }
  
  $query = mysqli_query("
      SELECT db1.table1.Id
      FROM
          db1.table1
          LEFT JOIN db2.table1
              ON ". implode(" AND ", $columns["table1"]) ."
          LEFT JOIN db2.table2
              ON ". implode(" AND ", $columns["table2"]) ."
      WHERE
          db2.table1.Id IS NULL
          OR db2.table2.Id IS NULL
  ");
  
  while (list($id) = mysqli_fetch_row($query)) {
      echo "ID {$id} does not match\n";
  }
  

  回复于 2024-05-13T22:34:59+08:00
• 0

  您可以使用union all和group by：

  select Student_Name, Id, Address, Attendance, Marks,
         (case when max(which) = 'db1' then 'Missing in db2'
               when min(which) = 'db2' then 'Missing in db1'
          end) as why
  from ((select 'db1' as which, Student_Name, Id, Address, Attendance, Marks
         from db1.table1 t
        ) union all
        (select 'db2' as which, t1.Student_Name, t1.id, t1.Address, t2.Attendance, t2.Marks
         from db2.table1 t1 join
              db2.table2 t2
              on t1.id = t2.id
        )
       ) u
  group by Student_Name, Id, Address, Attendance, Marks
  having count(distinct which) = 1
  

  回复于 2024-05-13T22:34:59+08:00
• 0

  此查询的结果将为您提供DB2中与DB1不同的行数，如果行不存在但它也应该计数 . 如果结果为0，一切正常 .

  SELECT
      COUNT(*) AS difference
  FROM
      DB1.table1 AS d1t1
      LEFT JOIN DB2.table1 AS d2t1
           USING (Id)
      LEFT JOIN DB2.table2 AS d2t2
           USING (Id)
  WHERE
      (d1t1.Student_Name <> d2t1.Student_Name)
      OR (d1t1.Address <> d2t1.Address)
      OR (d1t1.Attendace <> d2t2.Attendace)
      OR (d1t1.Marks <> d2t2.Marks)
  

  回复于 2024-05-13T22:34:59+08:00