用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";

    }

}