PHP MYSQL 数据库结构同步脚本

<?phpnamespace Common\Lib;class MysqlSync{    /**     * 执行状态记录     * @var array     */    private $stat = array();    /**     * 默认值需要加上引号的类型的索引     * @var array     */    private $convert_map = array('varchar', 'char', 'tinytext', 'mediumtext', 'text', 'longtext', 'enum');    /**     * 数据库结构同步     * @param $selfConf     * @param $sourceConf     * @return array     */    function sync($selfConf, $sourceConf){        $self = new \Mysqli($selfConf['host'], $selfConf['user'], $selfConf['psw'], $selfConf['db']);        $source = new \Mysqli($sourceConf['host'], $sourceConf['user'], $sourceConf['psw'], $sourceConf['db']);        $selfData = $this->getStructure($self, $selfConf['db']);     //获取本身，和对比源的结构        $sourceData = $this->getStructure($source, $sourceConf['db']);        $selfKeys = array_keys($selfData);      //获取本身，和对比源的表        $sourceKeys = array_keys($sourceData);        $removeList = array_diff($selfKeys, $sourceKeys);       //如果自身有，源没有，就删除        $createList = array_diff($sourceKeys, $selfKeys);       //如果源有，自身没有，就新增        if(!empty($removeList)){        //执行删除操作            $remove_tab = '';            foreach($removeList as $val){                $remove_tab .= "`{$val}`,";            }            $remove_tab = trim($remove_tab, ',');            $remove_sql = "DROP TABLE {$remove_tab}";            if($self->query($remove_sql)){                $this->stat['success'][] = $remove_sql;            }else{                $this->stat['error'][] = $remove_sql;            }        }        if(!empty($createList)){        //执行新增操作            foreach($createList as $val){                $create_arr = array();                foreach($sourceData[$val] as $item){                    $sql_write = "`{$item['COLUMN_NAME']}` {$item['COLUMN_TYPE']}";                    if(!empty($item['COLUMN_DEFAULT'])){                        if(in_array($item['DATA_TYPE'], $this->convert_map)){                            $sql_write .= " DEFAULT '{$item['COLUMN_DEFAULT']}'";                        }else{                            $sql_write .= " DEFAULT {$item['COLUMN_DEFAULT']}";                        }                    }                    $create_arr[] = $sql_write;                }                $create_sql = "CREATE TABLE IF NOT EXISTS `{$val}` (" . implode(',', $create_arr) . ")";                if($self->query($create_sql)){                    $this->stat['success'][] = $create_sql;                }else{                    $this->stat['error'][] = $create_sql;                }            }        }        foreach($sourceData as $pKey => $item){     //对比表的字段是否相同            foreach($selfData as $key => $val){                if($pKey == $key){     //检测表结构是否相同                    $removeColumn = array_diff_key($val, $item);                    $addColumn = array_diff_key($item, $val);                    if(!empty($removeColumn)){                        foreach($removeColumn as $removeVal){                            $removeColumnSql = "ALTER TABLE `{$key}` DROP COLUMN `{$removeVal['COLUMN_NAME']}`";                            if($self->query($removeColumnSql)){                                $this->stat['success'][] = $removeColumnSql;                            }else{                                $this->stat['error'][] = $removeColumnSql;                            }                        }                    }                    if(!empty($addColumn)){                        foreach($addColumn as $addVal){                            $addInfo = "`{$addVal['COLUMN_NAME']}` {$addVal['COLUMN_TYPE']}";                            if(!empty($addVal['COLUMN_DEFAULT'])){                                if(in_array($addVal['DATA_TYPE'], $this->convert_map)){                                    $addInfo .= " DEFAULT '{$addVal['COLUMN_DEFAULT']}'";                                }else{                                    $addInfo .= " DEFAULT {$addVal['COLUMN_DEFAULT']}";                                }                            }                            $addSql = "ALTER TABLE `{$key}` ADD COLUMN {$addInfo}";                            if($self->query($addSql)){                                $this->stat['success'][] = $addSql;                            }else{                                $this->stat['error'][] = $addSql;                            }                        }                    }                }            }        }        return $this->stat;    }    /**     * 获取表结构     * @param $resource     * @param $db     * @return array     */    function getStructure($resource, $db){        $table_str = '';        $info = array();        $sql_table = 'SHOW TABLES';        $res_table = $resource->query($sql_table);        while($row_table = $res_table->fetch_assoc()){            $table_str .= "'" . current($row_table) . "',";        }        $table_str = trim($table_str, ',');        $column_sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name IN({$table_str}) AND table_schema = '{$db}'";        $column_res = $resource->query($column_sql);        if($column_res) {            while ($row_column = $column_res->fetch_assoc()) {                $info[] = $row_column;            }            return $this->gen($info);        }else{            return array();        }    }    /**     * 数据排序处理     * @param $array     * @return array     */    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;    }}$sync = new MysqlSync();$selfConf = array(      //待同步数据库    'host'  => '',    'user'  => '',    'pwd'   => '',    'db'    => '');$sourceConf = array(        //同步来源数据库    'host'  => '',    'user'  => '',    'pwd'   => '',    'db'    => '');$sync->sync($selfConf, $sourceConf);