1.第一步先同步表
查询出要当前数据库对比源数据库需要删除的表和需要新建的表，同步表数据
重点语句：
SHOW TABLES（显示当前数据库中所有表的名称）DROP TABLE {表名}（删除当前数据库中该表）SHOW CREATE TABLE {表名}（获取创建该表的语句）
2.第二步再对比表结构->同步表结构
查询出两个数据库中表所有的字段，对比不同的表结构
重点语句：
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name IN({表名数据}) AND table_schema = '{数据库名}'（获取当前库所有表的表结构）SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = {列标识号} and table_schema = '{数据库名}' and table_name = '{表名}'（获取当前库该表的列标识号对应的字段名称）
3.第三步直接上代码
<?php
function dd($arr){
echo "<pre>";
var_dump($arr);
echo "</pre>";
die();
}
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 = mysqli_connect($selfConf['host'], $selfConf['user'], $selfConf['pwd'], $selfConf['db']);
$source = mysqli_connect($sourceConf['host'], $sourceConf['user'], $sourceConf['pwd'], $sourceConf['db']);
//删表 ，建表
$selfData = $this->getTable($self); //获取本身，和对比源的结构
$sourceData = $this->getTable($source);
$removeList = array_diff($selfData, $sourceData); //如果自身有，源没有，就删除
$createList = array_diff($sourceData, $selfData); //如果源有，自身没有，就新增
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_sql = "SHOW CREATE TABLE `{$val}`";
if($sql = $source->query($create_sql)->fetch_row()){
if($self->query($sql[1])){
$this->stat['success'][] = $sql[1];
}else{
$this->stat['error'][] = $sql[1];
}
}
}
}
//表结构
$selfStructure = $this->getStructure($self,$selfConf['db']);
$sourceStructure = $this->getStructure($source,$sourceConf['db']);
foreach($sourceStructure as $pKey => $item){ //对比表的字段是否相同
$val = $selfStructure[$pKey];
if ($val){
//dd($item);
$removeColumn = array_diff_key($val, $item);
$addColumn = array_diff_key($item, $val);
if(!empty($removeColumn)){
foreach($removeColumn as $removeVal){
$removeColumnSql = "ALTER TABLE `{$pKey}` DROP COLUMN `{$removeVal['COLUMN_NAME']}`";
if($self->query($removeColumnSql)){
$this->stat['success'][] = $removeColumnSql;
}else{
$this->stat['error'][] = $removeColumnSql;
}
}
}
//dd($addColumn);
if(!empty($addColumn)){
foreach($addColumn as $addVal){
$addInfo = "`{$addVal['COLUMN_NAME']}` {$addVal['COLUMN_TYPE']}";
//字符编码
if ($addVal['CHARACTER_SET_NAME']){
$addInfo .= " CHARACTER SET {$addVal['CHARACTER_SET_NAME']}";
}
//字符编码
if ($addVal['COLLATION_NAME']){
$addInfo .= " COLLATE {$addVal['COLLATION_NAME']}";
}
//是否为null
if ($addVal['IS_NULLABLE'] == 'NO'){
$addInfo .= " NOT NULL";
}
//默认值
if ($addVal['COLUMN_DEFAULT']!==null){
if(in_array($addVal['DATA_TYPE'], $this->convert_map)){
$addInfo .= " DEFAULT '{$addVal['COLUMN_DEFAULT']}'";
}else{
$addInfo .= " DEFAULT {$addVal['COLUMN_DEFAULT']}";
}
}
//EXTRA
if ($addVal['EXTRA']){
$addInfo .= " {$addVal['EXTRA']}";
}
//备注信息
if ($addVal['COLUMN_COMMENT']){
$addInfo .= " COMMENT '{$addVal['COLUMN_COMMENT']}'";
}
if($addVal['ORDINAL_POSITION'] == 1)
{
$addInfo .= " first";
}
else
{
$last_pos = $addVal['ORDINAL_POSITION'] - 1;
$last_col = $this->getAlterAfter($source,$sourceConf['db'],$addVal['TABLE_NAME'],$last_pos);
$addInfo .= " AFTER `{$last_col[0]['COLUMN_NAME']}`";
}
$addSql = "ALTER TABLE `{$pKey}` 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 $resource
* @param $db
* @param $table
* @param $lastPos
* @return array
*/
function getAlterAfter($resource,$db,$table,$lastPos){
$info = [];
$column_sql = "SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where ORDINAL_POSITION = {$lastPos} and table_schema = '{$db}' and table_name = '{$table}'";
$column_res = $resource->query($column_sql);
if($column_res) {
while ($row_column = $column_res->fetch_assoc()) {
$info[] = $row_column;
}
}
return $info;
}
/**
* 获取表列表
* @param $resource
* @param $db
* @return array
*/
function getTable($resource){
$table_arr = [];
$sql_table = 'SHOW TABLES';
$res_table = $resource->query($sql_table);
while($row_table = $res_table->fetch_assoc()){
$table_arr []= current($row_table);
}
return $table_arr;
}
/**
* 数据排序处理
* @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' => 'localhost',
'user' => 'root',
'pwd' => 'root',
'db' => 'yinghuo'
);
$sourceConf = array( //同步来源数据库
'host' => 'localhost',
'user' => 'root',
'pwd' => 'root',
'db' => 'tp'
);
$res = $sync->sync($selfConf, $sourceConf);
dd($res);
