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