php 获取指定数据库信息，php对比数据库并更新差异

第一步：获取需要对比的数据库信息

// 我这直接是tp6 框架，其他框架自己改下数据库操作就好了。


// 获取本地数据库信息 
public function getDb()
    {
        $dataBase = Env::get('database.database');
        $charset = Env::get('database.charset', 'utf8');

        // 获取所有表、索引
        $p_tables = [];
        $r_tables = Db::query("SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='$dataBase'");
        foreach ($r_tables as $t) {
            $tn = $t["TABLE_NAME"];
            $p_tables[$tn]["table"] = $t;
            $p_tables[$tn]["keys"] = Db::query("show indexes from {$tn}");
        }

        // 获取所有表字段
        $r_columns = Db::query("SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA`='{$dataBase}' ORDER BY `TABLE_NAME` DESC");
        foreach ($r_columns as $o) {
            $p_tables[$o["TABLE_NAME"]]["columns"][] = $o;
        }

        // 格式化数据
        $n_table = [];
        foreach ($p_tables as $t => $v) {

            // 表基础信息
            $n_table[$t] = [
                "engine" => $v["table"]["ENGINE"],
                "charset" => $charset,
                "comment" => $v["table"]["TABLE_COMMENT"],
                "primary" => [],
                "keys" => [],
                "columns" => [],
            ];

            // 表字段信息
            foreach ($v["columns"] as $o) {

                $n_table[$t]["columns"][$o["COLUMN_NAME"]] = [
                    "type" => $o["COLUMN_TYPE"],
                    "null" => $o["IS_NULLABLE"] == "YES" ? 1 : 0,
                    "extra" => $o["EXTRA"],
                    "default" => $o["COLUMN_DEFAULT"],
                    "comment" => $o["COLUMN_COMMENT"]
                ];
            }

            // 表索引信息
            foreach ($v["keys"] as $k) {
                $kn = $k["Key_name"];
                if ($kn == "PRIMARY") {
                    $n_table[$t]["primary"][] = $k["Column_name"];
                } else {
                    if (isset($n_table[$t]["keys"][$kn])) {
                        $n_table[$t]["keys"][$kn]["column"][] = $k["Column_name"];
                    } else {
                        $n_table[$t]["keys"][$kn] = [
                            "type" => $k["Index_type"],
                            "unique" => $k["Non_unique"] ? 0 : 1,
                            "column" => [$k["Column_name"]],
                            "comment" => "",
                        ];
                    }
                }
            }
        }
        return $n_table;
    }

第二步：对比两个数据库的信息，并把差异保存成sql

   /**
     * 替换远程表数据符合本地数据
     * @param array $remote
     * @param string $lprefix
     * @return mixed
     */
    protected function _replaceTablePrefix($remote, $lprefix = "eb_")
    {
        if ($lprefix <> "eb_") {
            $nr = [];
            foreach ($remote as $k => $v) {
                if (strpos($k, "eb_") === 0) {
                    $k = $lprefix . rtrim($k, "eb_");
                }
                $nr[$k] = $v;
            }
            return $nr;
        }
        return $remote;
    }

    /**
     * 生成创建表SQL
     * @param string $t
     * @param array $v
     * @return string
     */
    protected function _createTableSql($t, $v)
    {
        // 生成字段SQL
        $cSql = "";
        foreach ($v["columns"] as $ck => $cv) {
            $cSql .= "`{$ck}` {$cv["type"]} ";
            if ($cv["null"]) {
                $cSql .= "NULL ";
            } else {
                $cSql .= "NOT NULL ";
            }
            if ($cv["default"] === null) {
                if ($cv["null"]) {
                    $cSql .= "DEFAULT NULL ";
                }
            } else {
                if ($cv["default"] == "CURRENT_TIMESTAMP") {
                    $cSql .= "DEFAULT CURRENT_TIMESTAMP ";
                } else {
                    $cSql .= "DEFAULT '{$cv["default"]}' ";
                }
            }
            if ($cv["extra"]) {
                $cSql .= "{$cv["extra"]} ";
            }
            $cSql .= "COMMENT '{$cv["comment"]}', ";
        }

        // 主键SQL
        $pSql = "";
        if ($v["primary"]) {
            foreach ($v["primary"] as &$p) {
                $p = "`{$p}`";
            }
            $pSql .= "PRIMARY KEY (" . implode(",", $v["primary"]) . "), ";
        }

        // 索引SQL
        $kSql = "";
        foreach ($v["keys"] as $kk => $kv) {
            foreach ($kv["column"] as &$p) {
                $p = "`{$p}`";
            }
            if ($kv["unique"]) {
                $kSql .= "UNIQUE KEY `{$kk}` (" . implode(",", $kv["column"]) . ") ";
            } else {
                $kSql .= "KEY `{$kk}` (" . implode(",", $kv["column"]) . ") ";
            }
            if ($kv["type"]) {
                $kSql .= "USING {$kv["type"]}, ";
            } else {
                $kSql .= ", ";
            }
        }

        $nSql = $cSql . $pSql . $kSql;
        $nSql = rtrim($nSql, ", ");
        return "CREATE TABLE `{$t}` ( {$nSql} ) ENGINE={$v["engine"]} CHARSET={$v["charset"]} COMMENT='{$v["comment"]}'; ";
    }

    /**
     * 更新表SQL
     * @param $t
     * @param $rv
     * @param $lv
     * @return string
     */
    protected function _updateTableSql($t, $rv, $lv)
    {
        $sql = "";
        if ($rv["engine"] <> $lv["engine"]) {
            $sql .= "ALTER TABLE `{$t}` ENGINE={$rv["engine"]} ; ";
        }
        if ($rv["charset"] <> $lv["charset"]) {
            $sql .= "ALTER TABLE `{$t}` CHARSET={$rv["charset"]} ; ";
        }
        return $sql;
    }

    /**
     * 创建字段SQL
     * @param string $t
     * @param string $ck
     * @param array $cv
     * @return string
     */
    protected function _createColumnSql($t, $ck, $cv)
    {
        $sql = "ALTER TABLE `{$t}` ADD `{$ck}` {$cv["type"]} ";
        if ($cv["null"]) {
            $sql .= "NULL ";
        } else {
            $sql .= "NOT NULL ";
        }
        if ($cv["default"] === null) {
            if ($cv["null"]) {
                $sql .= "DEFAULT NULL ";
            }
        } else {
            if ($cv["default"] == "CURRENT_TIMESTAMP") {
                $sql .= "DEFAULT CURRENT_TIMESTAMP ";
            } else {
                $sql .= "DEFAULT '{$cv["default"]}' ";
            }
        }
        if ($cv["extra"]) {
            $sql .= "{$cv["extra"]} ";
        }
        $sql .= "COMMENT '{$cv["comment"]}'; ";
        return $sql;
    }

    /**
     * 修改字段SQL
     * @param string $t
     * @param string $ck
     * @param array $cv
     * @return string
     */
    protected function _updateColumnSql($t, $ck, $cv)
    {
        $sql = "ALTER TABLE `{$t}` CHANGE `{$ck}` `{$ck}` {$cv["type"]} ";
        if ($cv["null"]) {
            $sql .= "NULL ";
        } else {
            $sql .= "NOT NULL ";
        }
        if ($cv["default"] === null) {
            if ($cv["null"]) {
                $sql .= "DEFAULT NULL ";
            }
        } else {
            if ($cv["default"] == "CURRENT_TIMESTAMP") {
                $sql .= "DEFAULT CURRENT_TIMESTAMP ";
            } else {
                $sql .= "DEFAULT '{$cv["default"]}' ";
            }
        }
        if ($cv["extra"]) {
            $sql .= "{$cv["extra"]} ";
        }
        $sql .= "COMMENT '{$cv["comment"]}'; ";
        return $sql;
    }

    /**
     * 更改主键
     * @param string $t
     * @param array $rv
     * @param array $lv
     * @return string
     */
    protected function _updatePrimary($t, $rv, $lv)
    {
        foreach ($rv["primary"] as &$p) {
            $p = "`{$p}`";
        }
        // 如果主键有索引
        $sql  = "ALTER TABLE `{$t}` DROP PRIMARY KEY;";
        $sql .= "ALTER TABLE `{$t}` ADD PRIMARY KEY(" .implode(",", $rv["primary"]). ");                     
        return $sql;
    }

    /**
     * 删除索引
     * @param string $t
     * @param array $keys
     * @return string
     */
    protected function _deleteKeys($t, $keys)
    {
        $sql = "";
        foreach ($keys as $k) {
            $sql .= "ALTER TABLE `{$t}` DROP INDEX `{$k}`; ";
        }
        return $sql;
    }

    /**
     * 更新索引
     * @param string $t
     * @param string $k
     * @param array $kv
     * @return string
     */
    protected function _updateKeys($t, $k, $kv)
    {
        $sql = "ALTER TABLE `{$t}` DROP INDEX `{$k}`; ";
        $sql .= $this->_createKeys($t, $k, $kv);
        return $sql;
    }

    /**
     * 创建索引
     * @param string $t
     * @param string $k
     * @param array $kv
     * @return string
     */
    protected function _createKeys($t, $k, $kv)
    {
        foreach ($kv["column"] as &$p) {
            $p = "`{$p}`";
        }
        $sql = "ALTER TABLE `{$t}` ";
        if ($kv["unique"]) {
            $sql .= "ADD UNIQUE `{$k}` (" . implode(",", $kv["column"]) . ") ";
        } else {
            $sql .= "ADD INDEX `{$k}` (" . implode(",", $kv["column"]) . ") ";
        }
        if ($kv["type"]) {
            $sql .= "USING {$kv["type"]}; ";
        } else {
            $sql .= "; ";
        }
        return $sql;
    }

第三步：调用

        // 获取数据库信息
        $db1 = getDb();
        $db2 = getDb();
        // 对比生成sql
        $diffSql = diffDbSql($db1, $db2);
        
        // 执行差异sql $diffSql 

完美结束...哈哈哈哈哈