DataConstruct.php

<?php

/**

 * Created by PhpStorm.

 * User: Administrator

 * Date: 2017/7/21

 * Time: 14:26

 * 列子

 * $pdo=new DataConstruct();

 * $tables=$pdo->getAllTableNames();

 * $pdo->printLog($pdo->getAllTableConstruct($tables),"F:/logstr.php");

 * $pdo->importSql('E:/UserTable.sql');

 */class DataConstruct

{

    public $dataBaseType;

    public $host;

    public $username;

    public $password;

    public $port;

    public $database;

    public $conn;

    public function __construct($username = "root", $password = "", $database = "aui", $port = "3306", $host = "localhost", $dataBaseType = "mysql")

    {

        $this->dataBaseType = $dataBaseType;

        $this->host = $host;

        $this->username = $username;

        $this->password = $password;

        $this->port = $port;

        $this->database = $database;

        $dsn = "$dataBaseType:dbname=$database;host=$host";

        try {

            $this->conn = new \PDO($dsn, $this->username, $this->password);

        } catch (PDOException $e) {

            echo 'Connection failed: ' . $e->getMessage();

        }

    }

    /**

     * @desc 获取所有的额表明

     * @return array

     */

    public function getAllTableNames()

    {

        $tableNames = array();

        $sql = "show tables";

        $cursor = $this->conn->query($sql);

        foreach ($cursor as $row) {

            $tableNames[] = $row[0];

        }

        return $tableNames;

    }

    /**

     * @desc 获取某个表的表结构

     * @param $tablename

     * @return array

     */

    public function getTableConstruct($tablename)

    {

        $table = array();

        $sql = "select  COLUMN_NAME,COLUMN_TYPE,COLUMN_DEFAULT,COLUMN_KEY,COLUMN_COMMENT from information_schema.`COLUMNS` where TABLE_NAME='" . $tablename . "' and TABLE_SCHEMA='" . $this->database . "'";

        $cursor = $this->conn->query($sql, \PDO::FETCH_ASSOC);

        foreach ($cursor as $row) {

            $table[$row['COLUMN_NAME']] = $row;

        }

        return $table;

    }

    /**

     * @desc 获取所有表的表结构

     * @param array $tableNames

     * @return array

     */

    public function getAllTableConstruct($tableNames = array())

    {

        $AllTableConstruct = array();

        if (is_array($tableNames)) {

            foreach ($tableNames as $tableName) {

                $AllTableConstruct[$tableName] = $this->getTableConstruct($tableName);

            }

        }

        return $AllTableConstruct;

    }

    /**

     * @desc 打印表结构到指定的文件中

     * @param $tableConstruct

     * @param $logpath

     */

    public function printLog($tableConstruct, $logpath)

    {

        foreach ($tableConstruct as $key => $table) {

            file_put_contents($logpath, $key . PHP_EOL, FILE_APPEND);

            file_put_contents($logpath, "COLUMN_NAME   COLUMN_TYPE   COLUMN_DEFAULT   COLUMN_KEY   COLUMN_COMMENT" . PHP_EOL, FILE_APPEND);

            foreach ($table as $column) {

                file_put_contents($logpath, $column['COLUMN_NAME'] . "\t" . $column['COLUMN_TYPE'] . "\t" . $column['COLUMN_DEFAULT'] . "\t" . $column['COLUMN_KEY'] . "\t" . $column['COLUMN_COMMENT'] . PHP_EOL, FILE_APPEND);

            }

            file_put_contents($logpath, PHP_EOL, FILE_APPEND);

        }

    }

    /**

     * @desc 执行sql文件 （不能超温php.ini中配置的内存大小，否则会报内存溢出错误）

     * @param $filepath

     */

    public function importSql($filepath)

    {

        $_sql = file_get_contents($filepath);

        $_arr = explode(';', $_sql);

        $this->conn->query('set names utf8;');

        foreach ($_arr as $_value) {

            try {

                $this->conn->query($_value . ';');

            } catch (\PDOException $e) {

                var_dump($this->conn->errorInfo());

            }

        }

    }

}

<?php
include_once("DataConstruct.php");

//---------------------------------------比较两个不同的数据库之间的表结构-start-------------------------------

        $pdo=new DataConstruct();

        $oldtables=$pdo->getAllTableNames();

        $pdonew=new DataConstruct("root",'','test');

        $newtables=$pdonew->getAllTableNames();

        foreach($newtables as $key => $val){

            if(in_array($val,$oldtables)){

                $oldTableCu=$pdo->getTableConstruct($val);

                $newTableCU=$pdonew->getTableConstruct($val);

                if(!empty(array_diff($oldTableCu,$newTableCU))){//表示不相同，没有变化

                    echo "---diff table---".$val;

                }

            }else{

                echo "---new table---".$val;

            }

        }

        //---------------------------------------比较两个不同的数据库之间的表结构--end------------------------------
?>