用php脚本比较MySQL两个数据库的结构差异

 

define('DATABASE1', 'mysql://root:password@127.0.0.1/db1');
$dbi1 = new DbMysql;
$dbi1->dbh = DATABASE1;

define('DATABASE2', 'mysql://root:password@127.0.0.1/db2');
$dbi2 = new DbMysql;
$dbi2->dbh = DATABASE2;

// db1
$db1 = array();
$map = array();
$dbi1->fetchMap("SHOW TABLES", $map);
$tables = array_keys($map);
for($i=0; $i<count($tables); $i++){
    $map = array();
    $dbi1->fetchMap("DESCRIBE ".$tables[$i], $map);
    $structures = array();
    foreach($map as $k=>$v){
        $structures[] = "$k=$v";
    }
    $db1[$tables[$i]] = $structures;
}

// db2
$db2 = array();
$map = array();
$dbi2->fetchMap("SHOW TABLES", $map);
$tables = array_keys($map);
for($i=0; $i<count($tables); $i++){
    $map = array();
    $dbi2->fetchMap("DESCRIBE ".$tables[$i], $map);
    $structures = array();
    foreach($map as $k=>$v){
        $structures[] = "$k=$v";
    }
    $db2[$tables[$i]] = $structures;
}

// db2 compare db1
foreach($db2 as $table=>$structures2){
    $structures1 = $db1[$table];
    for($i=0; $i<count($structures2); $i++){
        if(!in_array($structures2[$i], $structures1))
            echo $table."\t".$structures2[$i]."\n";
    }
}

// db1 compare db2
foreach($db1 as $table=>$structures1){
    $structures2 = $db2[$table];
    for($i=0; $i<count($structures1); $i++){
        if(!in_array($structures1[$i], $structures2))
            echo $table."\t".$structures1[$i]."\n";
    }
}

 

数据库操作类

DbMysql.php