php实现mysql结构对比并同步

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php

    $db = array(      
        'host'  => 'localhost',
        'user'  => 'root',
        'pwd'   => '123',
        'db1'    => 'test1',
        'db2'    => 'test2'
    );

    $conn = mysql_connect($db['host'], $db['user'], $db['pwd']) or die();

    $stru1 = "SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,COLUMN_TYPE,COLUMN_COMMENT,EXTRA,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '{$db['db1']}'";
    $stru2 = "SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,COLUMN_TYPE,COLUMN_COMMENT,EXTRA,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '{$db['db2']}'";

    $info1 = result($stru1,$conn);
    $info2 = result($stru2,$conn);

    $data1 = gen($info1);
    $data2 = gen($info2);

    $key1 = array_keys($data1);
    $key2 = array_keys($data2);

    $diff1 = array_diff($key1,$key2);  //db1有，db2没有
    $diff2 = array_diff($key2,$key1);

    //在db2中创建db1有但db2没有的表
    if(!empty($diff1))
    {
        foreach($diff1 as $val)
        {
            $create_sql = "show create table ".$db['db1'].".".$val;
            $create = result($create_sql,$conn);
            if($create)
            {
                mysql_query("use ".$db['db2'],$conn) or die(mysql_error());
                foreach($create as $sql)
                {
                    echo $sql['Create Table']." 0";
                    mysql_query($sql['Create Table'],$conn) or die(mysql_error()." 0 </br>");
                }
            }
        }
    }

    //删除db2中db2有但db1没有的表
        if(!empty($diff2))
    {
        foreach($diff2 as $val)
        {
            echo "drop table ".$db['db2'].".".$val." 1 </br>";
            mysql_query("drop table ".$db['db2'].".".$val,$conn) or die(mysql_error()." 1 </br>");
        }
    }

    foreach($data1 as $key1 => $val1)
    {
        foreach($data2 as $key2 => $val2)
        {
            if($key1 == $key2)
            {
                $column1 = array_diff_key($val1,$val2); //db1比db2多的字段信息
                $column2 = array_diff_key($val2,$val1); //db2比db1多的字段信息

                //在db2中新增db1比db2多的字段
                if(!empty($column1))
                {
                    $alter_sql = '';
                    foreach($column1 as $col_info)
                    {
                        $alter_sql = "alter table ".$db['db2'].".".$col_info['TABLE_NAME']." add column ".$col_info['COLUMN_NAME']." ".$col_info['COLUMN_TYPE'];
                        if($col_info['IS_NULLABLE'] = 'NO')
                        {
                            $alter_sql .= " not null ";
                        }
                        if($col_info['COLUMN_DEFAULT'] === null)
                        {
                        }
                        else
                        {
                            if($col_info['DATA_TYPE'] == 'bit')
                            {
                                $alter_sql .= " default ".$col_info['COLUMN_DEFAULT'];
                            }
                            else
                            {
                                $alter_sql .= " default '{$col_info['COLUMN_DEFAULT']}'";
                            }
                        }
                        if($col_info['EXTRA'])
                        {
                            $alter_sql .= " ".$col_info['EXTRA'];
                        }
                        if($col_info['COLUMN_COMMENT'])
                        {
                            $alter_sql .= " comment '{$col_info['COLUMN_COMMENT']}'";
                        }
                        echo $alter_sql." 2 </br>";
                        mysql_query($alter_sql,$conn) or die(mysql_error()." 2 </br>");
                    }
                }

                //删除db2中db1比db2少的字段
                if(!empty($column2))        
                {
                    foreach($column2 as $col_info)
                    {
                        $alter_sql = "alter table ".$db['db2'].".".$col_info['TABLE_NAME']." drop column ".$col_info['COLUMN_NAME'];
                        echo $alter_sql." 3 </br>";
                        mysql_query($alter_sql,$conn) or die(mysql_error()." 3 </br>");
                    }
                }
            }

            //同步字段属性，以db1为基准
            if($key1 == $key2)
            {
                foreach($val1 as $col1 => $info1)
                {
                    foreach($val2 as $col2 => $info2)
                    {
                        if ($col1 == $col2)
                        { 
                            if($info1['ORDINAL_POSITION'] !== $info2['ORDINAL_POSITION'] || $info1['COLUMN_DEFAULT'] !== $info2['COLUMN_DEFAULT'] || $info1['IS_NULLABLE'] !== $info2['IS_NULLABLE'] || $info1['COLUMN_TYPE'] !== $info2['COLUMN_TYPE'] || $info1['COLUMN_COMMENT'] !== $info2['COLUMN_COMMENT'])
                            {
                                $modify = "alter table ".$db['db2'].".".$info2['TABLE_NAME']." modify column ".$info1['COLUMN_NAME']." ".$info1['COLUMN_TYPE'];
                                if($info1['IS_NULLABLE'] == 'NO')
                                {
                                    $modify .= " not null ";
                                }
                                if($info1['COLUMN_DEFAULT'] === null)
                                {
                                }
                                else if($info1['DATA_TYPE'] == 'bit')
                                {
                                    $modify .= " default ".$info1['COLUMN_DEFAULT'];
                                }
                                else 
                                {
                                    $modify .= " default '{$info1['COLUMN_DEFAULT']}'";
                                }
                                if($info1['EXTRA'])
                                {
                                    $modify .= " ".$info1['EXTRA'];
                                }
                                if($info1['COLUMN_COMMENT'])
                                {
                                    $modify .= " comment '{$info1['COLUMN_COMMENT']}'";
                                }
                                if($info1['ORDINAL_POSITION'] == 1)
                                {
                                    $modify .= " first";
                                }
                                else
                                {
                                    $last_pos = $info1['ORDINAL_POSITION'] - 1;
                                    $last_col = result("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = ".$last_pos." and table_schema = "."'{$db['db1']}'"." and table_name = "."'{$info1['TABLE_NAME']}'",$conn);
                                    $modify .= " after ".$last_col[0]['COLUMN_NAME'];
                                }
                                echo $modify."< 4 </br>";
                                mysql_query($modify,$conn) or die(mysql_error()." 4 </br>");
                            }
                        }
                    }
                }
            }
        }
    }

    //解析MySQL查询结果
    function result($sql,$conn)
    {
        mysql_query("set names 'utf8'") or die(mysql_error());
        $array = array();
        $res = mysql_query($sql,$conn) or die(mysql_error()." 5 </br>");
        if($res)
        {
            while ( $row = mysql_fetch_assoc($res))
            {   
                $array[] = $row;
            }
            return $array;
        }
        else
        {
            return $res;
        }
    }

    function gen($array)
    {
        $data = array();
        foreach($array as $key => $item)
        {
            if(!array_key_exists($item['TABLE_NAME'], $data)) 
            {
                foreach ($array as $value) 
                {
                    if ($value['TABLE_NAME'] == $item['TABLE_NAME']) 
                    {
                        $data[$item['TABLE_NAME']][$value['COLUMN_NAME']] = $value;

                    }
                }
            }
        }
        return $data;
    }
 ?>