MySQL-MySQL如何得到两个数据表结构的差异

MySQL-MySQL如何得到两个数据表结构的差异

晚风撩人 发布于 2017-08-05 字数 308 浏览 1081 回复 9

场景是这样的:

开发环境中对产品的数据表结构已经做了很多修改,包括一些字段名,字段类型等,但开发人员没有记录具体修改了那些部分。

现在只能比较开发环境与运行环境的数据表结构,找到差异,然后按照开发环境一个一个修改过来。

请问有没有什么办法能比较两个数据表,并将差异的部分列举出来。

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(9

瑾兮 2017-10-26 9 楼

大体方法都是拿结构对比。自动化的不靠谱

方法一:
用sql拿到ddl写脚本对比。主要是字段的那个部分,这个可当字符串用","拆分

方法二:
直接让Mysql返回所以字段内容,对比、

 mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |

##拿到这样的结果 再分析

清晨说ぺ晚安 2017-10-26 8 楼

我觉得用PHP写个程序应该也挺容易的,我以前好像找到过一个结构同步的PHP程序。只用MYSQL应该不行。

归属感 2017-10-19 7 楼

代码附在最后,我们的方案是,项目开发维护一个install.sql文件,这里是标准的建表语句,然后用程序按照这个建表语句和线上数据库做比对,然后自动的执行脚本修改。

请自行修改install.sql文件位置,以及数据用户名密码

<?php
error_reporting(7);
header ( 'Content-Type: text/html; charset=utf-8' );

$action = $argv[1];
if ($action == 'ok') {
define("ACTION", "OK");
} else {
define("ACTION", "NO");
}

define('ROOT_PATH',dirname(dirname(__FILE__)));

$serverinfo = loadServerInfo();
if (!$serverinfo)
exit("数据库信息提取失败!");

/*************配置信息******************/
define('MYSQL_HOST', $serverinfo['host'] . ':' . $serverinfo['port']); // 数据库地址
define('MYSQL_USER', $serverinfo['user']); // 数据库用户名
define('MYSQL_PASSWORD', $serverinfo['password']); // 数据库密码
define('MYSQL_CONNECT_CHAR', 'utf8'); // 数据库的连接编码
define('MYSQL_CONNECT_DATABASE', $serverinfo['plat'] . '_' . $serverinfo['game'] . '_user'); // 连接的库

define('SQL_FILE', ROOT_PATH . '/conf/sql/install.sql'); // sql的对比文件地址

define('LOG_PATH', './' .date('Ymd'). '.log'); // 执行sql的日志文件

/************************************/

/*************数据库连接*******************/
if (!$link = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD)) {
exit('connect error:' . mysql_error() . "n");
}
if (!mysql_select_db(MYSQL_CONNECT_DATABASE)) {
exit('select db error:' . mysql_error() . "n");
}
mysql_set_charset(MYSQL_CONNECT_CHAR);
/**************************************/

/*****************处理字符串,去掉一些注释的代码**********************/
$sql = file_get_contents(SQL_FILE);
// 去除如/***/的注释
$sql = preg_replace("[(/*)+.+(*/;s*)]", '', $sql);
// 去除如--类的注释
$sql = preg_replace("(--.*?n)", '', $sql);

/*****************处理字符串,去掉一些注释的代码**********************/

preg_match_all("/CREATEs+TABLEs+IF NOT EXISTS.+?(.+?)s*((.+?))s*(ENGINE|TYPE)s*=(.+?;)/is", $sql, $matches);
$newtables = empty($matches[1])?array():$matches[1];
$newsqls = empty($matches[0])?array():$matches[0];

$execSqlInfo = new execSqlInfo();

$totalNum = count($newtables);
for ($num = 0; $num < $totalNum; $num++) {
$newcols = getcolumn($newsqls[$num]);
$newtable = $newtables[$num];
$oldtable = $newtable;

// 要把表得前缀与后缀替换掉
$tmps = explode('_', $newtable);
$tmps[0] = $serverinfo['plat'];
array_pop($tmps);
$tmps[] = $serverinfo['area'];
$newtable = implode('_', $tmps);

$checksql = "SHOW CREATE TABLE {$newtable}";
$query = mysql_query($checksql);

if (!$query) {
$usql = $newsqls[$num];
$usql = str_replace($oldtable, $newtable, $usql);
execQuery($execSqlInfo, $usql);
} else {
$value = mysql_fetch_array($query);

// 判断注释
if ($comment = checkTableComment($newsqls[$num], $value['Create Table'])) {
$usql = "ALTER TABLE ".$newtable." COMMENT = '{$comment}'";
execQuery($execSqlInfo, $usql);
}

$oldcols = getcolumn($value['Create Table']);
$updates = array();
$allfileds =array_keys($newcols);
foreach ($newcols as $key => $value) {
if($key == 'PRIMARY') {
if($value != $oldcols[$key]) {
if(!empty($oldcols[$key])) {
$usql = "RENAME TABLE ".$newtable." TO ".$newtable . '_bak';
execQuery($execSqlInfo, $usql);
}
$updates[] = "ADD PRIMARY KEY $value";
}
} elseif ($key == 'KEY') {
foreach ($value as $subkey => $subvalue) {
if(!empty($oldcols['KEY'][$subkey])) {
if($subvalue != $oldcols['KEY'][$subkey]) {
$updates[] = "DROP INDEX `$subkey`";
$updates[] = "ADD INDEX `$subkey` $subvalue";
}
} else {
$updates[] = "ADD INDEX `$subkey` $subvalue";
}
}
} elseif ($key == 'UNIQUE') {
foreach ($value as $subkey => $subvalue) {
if(!empty($oldcols['UNIQUE'][$subkey])) {
if($subvalue != $oldcols['UNIQUE'][$subkey]) {
$updates[] = "DROP INDEX `$subkey`";
$updates[] = "ADD UNIQUE INDEX `$subkey` $subvalue";
}
} else {
$usql = "ALTER TABLE ".$newtable." DROP INDEX `$subkey`";
execQuery($execSqlInfo, $usql);
$updates[] = "ADD UNIQUE INDEX `$subkey` $subvalue";
}
}
} else {
if(!empty($oldcols[$key])) {
if(strtolower($value) != strtolower($oldcols[$key])) {
$updates[] = "CHANGE `$key` `$key` $value";
}
} else {
$i = array_search($key, $allfileds);
$fieldposition = $i > 0 ? 'AFTER `'.$allfileds[$i-1].'`' : 'FIRST';
$updates[] = "ADD `$key` $value $fieldposition";
}
}
}
if ($updates) {
$usql = "ALTER TABLE ".$newtable." ".implode(', ', $updates);
execQuery($execSqlInfo, $usql);
} else {
checkColumnDiff($execSqlInfo, $newcols, $oldcols);
}
}
}

// 输出信息并存入
$message = $execSqlInfo->formatMessage();
echo $message;
file_put_contents(LOG_PATH, $message, FILE_APPEND);

function execQuery($execSqlInfo, $sql) {

$res = true;

if (ACTION == 'OK') {
$res = mysql_query($sql);
}
if (!$res) {
$debug = debug_backtrace();
$execSqlInfo->setMsg('line ' . $debug[0]['line'] . ' : ' . 'sql wrong:' . $sql . ' ' . mysql_error());
} else {
// 记录
$execSqlInfo->setSql($sql);
}

}

/**
*
* 检索两个数组的键值顺序是否一致,若不一致列出具体的信息
*/
function checkColumnDiff($execSqlInfo, $newCols, $oldCols) {

if (array_keys($newCols) == array_keys($oldCols)) {
return false;
}
if (count($newCols) != count($oldCols)) {
return false;
}
$size = count($newCols);

for ($i=0; $i < $size; $i++) {
$newCol = key($newCols);
$oldCol = key($oldCols);

if (!empty($newCol) && !in_array($newCol, array('KEY', 'INDEX', 'UNIQUE', 'PRIMARY'))
&& $newCol != $oldCol) {
$execSqlInfo->setMsg("字段顺序不正确: 第" . ($i+1) . "个字段 sql中字段为 {$newCol} 数据库中字段为 {$oldCol}" );
}
next($newCols);
next($oldCols);
}

}

function checkTableComment($newSql, $oldSql) {
if (!$newSql || !$oldSql) {
return false;
}

// 获取最后一行
$newlastSql = array_pop(explode("n", $newSql));
$oldlastSql = array_pop(explode("n", $oldSql));
$newComment = '';
$oldComment = '';
if (preg_match("/COMMENT='(.*)'/is", $newlastSql, $matchs))
$newComment = $matchs[1];

if (!$newComment)
return false;

if (preg_match("/COMMENT='(.*)'/is", $oldlastSql, $matchs))
$oldComment = $matchs[1];

if ($newComment == $oldComment)
return false;

return $newComment;
}

function remakesql($value) {
$value = trim(preg_replace("/s+/", ' ', $value));
$value = str_replace(array('`',', ', ' ,', '( ' ,' )', 'mediumtext'), array('', ',', ',','(',')','text'), $value);
return $value;
}

function getcolumn($creatsql) {

preg_match("/((.+))s*(ENGINE|TYPE)s*=/is", $creatsql, $matchs);

$cols = explode("n", $matchs[1]);
$newcols = array();
foreach ($cols as $value) {
$value = trim($value);
if(empty($value)) continue;
$value = remakesql($value);
if(substr($value, -1) == ',') $value = substr($value, 0, -1);

$vs = explode(' ', $value);
$cname = $vs[0];

if($cname == 'KEY' || $cname == 'INDEX' || $cname == 'UNIQUE') {

$name_length = strlen($cname);
if($cname == 'UNIQUE') $name_length = $name_length + 4;

$subvalue = trim(substr($value, $name_length));
$subvs = explode(' ', $subvalue);
$subcname = $subvs[0];
$newcols[$cname][$subcname] = trim(substr($value, ($name_length+2+strlen($subcname))));

} elseif($cname == 'PRIMARY') {

$newcols[$cname] = trim(substr($value, 11));

} else {

$newcols[$cname] = trim(substr($value, strlen($cname)));
}
}
return $newcols;
}

function loadServerInfo() {
$serverinfo = array();

/*
$serverinfo['host'] = $xmlObj->System->MySQL->MySQLHost;
$serverinfo['port'] = $xmlObj->System->MySQL->MySQLPort;
$serverinfo['user'] = $xmlObj->System->MySQL->MySQLUser;
$serverinfo['password'] = $xmlObj->System->MySQL->MySQLPwd;
$serverinfo['prefix'] = $xmlObj->System->MySQL->MySQLPrefix;
$serverinfo['plat'] = $xmlObj->System->Plat;
$serverinfo['game'] = $xmlObj->System->Game;
$serverinfo['area'] = $xmlObj->System->Area;
*/
return $serverinfo;
}

class execSqlInfo {

public $msgList = array();
public $excsqlList = array();

function setMsg($message) {
$this->msgList[] = $message;
}

function setSql($querysql) {
$this->excsqlList[] = $querysql;
}

function formatMessage() {
$showMessage = "n";
$showMessage .= date('Y m d H:i:s') . "n";

if (!$this->excsqlList && !$this->msgList) {
$showMessage .= "nothing to do!n";
} else {
if($this->msgList) {
$showMessage .= "error message:n ";
foreach ($this->msgList as $v) {
$showMessage .= $v . "n
*******************************************************nn";
}
}
$showMessage .= "exec sql :n";
foreach ($this->excsqlList as $v) {
$showMessage .= $v . "n
////////////////////////////////////////////////////////nn";
}
}
return $showMessage;
}
}

归属感 2017-10-15 6 楼

也评论下吧,看了下前边的几个答案,觉得都不怎么靠谱
楼主的问题应该是如何做数据库版本管理和如何获得两个数据库的结构差异两个问题。
1.数据库版本管理,可以拿一个基础版本作为版本A。每个版本维护一个增量sql脚本就可以了,脚本文件做一个版本管理就好了。A.1.1.sql 之类。
2.推荐下sqlyog 的架构对比工具。谁用谁知道。

虐人心 2017-10-10 5 楼

select * from information_schema.TABLES

where TABLE_SCHEMA='oa'
这个表里边有 表字段类型 引擎 及一些信息

想挽留 2017-09-28 4 楼

这种事情为什么非要手动做呢? 谁没事一天到晚将数据库改来改去?考虑到这种更改频率不是很高,所以不需要单独写个程序或执行一大段脚本什么的,还是用工具靠谱些,navicat的structure synchronization不是很方便吗?再说了,光比较字段增减还不够,很多时候字段的增减就意味着索引策略也会相应的更新.索引,存储过程,视图等不用更新吗?

甜柠檬 2017-09-24 3 楼

用sql的话,好像只能列出它们的相同部分,根据相同部分找另一个表的增加字段应该可以吧

SELECT a.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS a
JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.COLUMN_NAME = b.COLUMN_NAME
AND b.TABLE_NAME = 'in_table_2'
AND b.TABLE_SCHEMA = 'in_database2'
WHERE a.TABLE_NAME = 'in_table_1'
AND a.TABLE_SCHEMA = 'in_database1'

想挽留 2017-09-04 2 楼

你直接将两个表的结构导出成SQL文件,然后进行差异对比好了

可以使用vimdiff或者使用SNVtortoise Merge工具进行对比,甚至notepad++都能对比两个文件的差异

既然有现成的工具,不建议还写什么代码进行对比之类的~~

虐人心 2017-08-17 1 楼

如果B是基于A修改的,并且开启了bin-log,那么可以检查bin-log里面的alter记录。

修改
my.cnf
去掉log_bin前面的注释
开启mysqlbinlog

这个时候mysql会记录所有针对数据库的操作

登入mysql

show binary logs;

可以显示已生成的mysqlbinlog

要进行检查可以这么做

mysqlbinlog /var/log/mysql/mysql-bin.000001 | grep 'alter table tablename' > test.file

可以导出所有对tablename的alter记录