PHP MYSQL 数据库结构同步脚本

<?php
namespace 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);