生成sql表结构

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'] . "	" . $column['COLUMN_TYPE'] . "	" . $column['COLUMN_DEFAULT'] . "	" . $column['COLUMN_KEY'] . "	" . $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());
            }

        }
    }
}

new.php   

<?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------------------------------
?>