MySQL 對比資料庫表結構

pursuer.chen發表於2016-04-29
MySql資料庫

介紹

本章主要介紹怎樣對比資料庫的表結構的差異，這裡主要介紹使用mysqldiff工具來對比表結構的差異，其實在5.6版本之後通過查詢information庫中的系統表也能對比出來，但是mysqldiff還有一個好處就是可以直接生產差異的SQL語句這個功能就是我們需要利用的，而通過分析系統表要實現這個就比較難；接下來就來看看怎樣使用這個工具。

 

 

語法

mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4

這個語法有兩個用法：

db1：db2:如果只是指定資料庫，那麼就將兩個資料庫中互相缺少的物件顯示出來，而物件裡面的差異不進行對比；這裡的物件包括表、儲存過程、函式、觸發器等。

db1.object1:db2.object1：如果指定了具體表物件，那麼就會詳細對比兩個表的差異，包括表名、欄位名、備註、索引、大小寫等都有的表相關的物件。

接下來看一些主要的引數：

--server1：配置server1的連線

--server2：配置server2的連線

--character-set：配置連線時用的字符集，如果不顯示配置預設使用“character_set_client”

--width：配置顯示的寬度

--skip-table-options：這個選項的意思是保持表的選項不變，即對比的差異裡面不包括表名、AUTO_INCREMENT,ENGINE, CHARSET等差異。

-d DIFFTYPE, --difftype：差異的資訊顯示的方式，有[unified|context|differ|sql](default: unified),如果使用sql那麼就直接生成差異的SQL這樣非常方便。

--changes-for=：例如--changes-for=server2,那麼對比以sever1為主，生成的差異的修改也是針對server2的物件的修改。

--show-reverse：這個字面意思是顯示相反的意思，其實是生成的差異修改裡面同時會包含server2和server1的修改。

測試

use study;

create table test1
(id int not null primary key,
a varchar(10) not null,
b varchar(10),
c varchar(10) comment 'c',
d int

)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test1';


create table test2
(id int not null ,
a varchar(10),
b varchar(5),
c varchar(10),
D int
)
ENGINE=myisam DEFAULT CHARSET=utf8 COMMENT='test2';

1.不使用--skip-table-options

mysqldiff  --server1=root:root@localhost --server2=root:root@localhost --changes-for=server2   --show-reverse   --difftype=sql study.test1:study.test2

2.使用--skip-table-options

 

其實用SQL語句也可以達到查詢的效果，這裡就貼上平時用的對比語句。

###############################################################################################################################
##判斷兩個資料庫相同表的欄位不為空是否相同
select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.COLUMN_TYPE,a.IS_NULLABLE,a.COLUMN_DEFAULT,b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.COLUMN_TYPE,b.IS_NULLABLE ,b.COLUMN_DEFAULT,b.COLUMN_COMMENT 
from information_schema.`COLUMNS` a inner join information_schema.`COLUMNS` b 
on a.TABLE_SCHEMA='db1' and b.TABLE_SCHEMA='db2'and a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.IS_NULLABLE<>b.IS_NULLABLE
where a.IS_NULLABLE='NO';
################################################################################################################################
##判斷兩個資料庫相同表的欄位預設值是否相同
select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.COLUMN_DEFAULT,b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.COLUMN_DEFAULT from information_schema.`COLUMNS` a 
inner join information_schema.`COLUMNS` b on a.TABLE_SCHEMA='db1' and b.TABLE_SCHEMA='db2'
and a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.COLUMN_DEFAULT<>b.COLUMN_DEFAULT;

#################################################################################################################################
##判斷兩個資料庫相同表的欄位資料型別是否相同,這裡是判斷資料型別不同如果要判斷資料型別的長度不同需要用COLUMN_TYPE欄位
select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_DEFAULT,b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.DATA_TYPE ,b.COLUMN_DEFAULT
from information_schema.`COLUMNS` a inner join information_schema.`COLUMNS` b on a.TABLE_SCHEMA='db1' and b.TABLE_SCHEMA='db2'
and a.TABLE_NAME=b.TABLE_NAME and a.COLUMN_NAME=b.COLUMN_NAME and a.DATA_TYPE<>b.DATA_TYPE;

##################################################################################################################################
##判斷兩個資料庫相同表的中互相不存在的欄位
select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_DEFAULT
from information_schema.`COLUMNS` a 
where a.TABLE_SCHEMA='db1' and a.COLUMN_NAME NOT IN(SELECT b.COLUMN_NAME from information_schema.`COLUMNS` b where b.TABLE_SCHEMA='db2' and a.TABLE_SCHEMA='db1'
and a.TABLE_NAME=b.TABLE_NAME );

select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_DEFAULT
from information_schema.`COLUMNS` a 
where a.TABLE_SCHEMA='db2' and a.COLUMN_NAME NOT IN(SELECT b.COLUMN_NAME from information_schema.`COLUMNS` b where b.TABLE_SCHEMA='db1' and a.TABLE_SCHEMA='db2'
and a.TABLE_NAME=b.TABLE_NAME );

####mysql沒有full jion所以變相的多做了一次select查詢，這種方法效能比較差,對於表比較多的資料庫建議使用上面的分開查詢
select b.TABLE_SCHEMA,b.TABLE_NAME,b.COLUMN_NAME,b.DATA_TYPE,c.TABLE_SCHEMA,c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE from 
(select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.`COLUMNS` a where a.TABLE_SCHEMA in('db2','db1') )a left join
(select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.`COLUMNS` a where a.TABLE_SCHEMA in('db2')) b  on a.TABLE_NAME=b.TABLE_NAME AND a.COLUMN_NAME=b.COLUMN_NAME left join 
(select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.`COLUMNS` a where a.TABLE_SCHEMA in('db1')) c on a.TABLE_NAME=c.TABLE_NAME AND a.COLUMN_NAME=c.COLUMN_NAME
where b.COLUMN_NAME is null or c.COLUMN_NAME is null ;

#######################################################################################################################
##判斷兩個資料庫互相不存在的表
select a.TABLE_SCHEMA,a.TABLE_NAME
from information_schema.TABLES a 
where a.TABLE_SCHEMA='db1' and a.TABLE_NAME NOT IN(SELECT b.TABLE_NAME from information_schema.TABLES b where b.TABLE_SCHEMA='db2');

select a.TABLE_SCHEMA,a.TABLE_NAME
from information_schema.TABLES a 
where a.TABLE_SCHEMA='db2' and a.TABLE_NAME NOT IN(SELECT b.TABLE_NAME from information_schema.TABLES b where b.TABLE_SCHEMA='db1');


select b.TABLE_SCHEMA,b.TABLE_NAME,c.TABLE_SCHEMA,c.TABLE_NAME from 
(select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES a where a.TABLE_SCHEMA in('db2','db1') )a left join
(select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES a where a.TABLE_SCHEMA in('db2')) b  on a.TABLE_NAME=b.TABLE_NAME left join 
(select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES a where a.TABLE_SCHEMA in('db1')) c on a.TABLE_NAME=c.TABLE_NAME 
where b.TABLE_NAME is null or c.TABLE_NAME is null ;

 

總結

 這裡沒有演示對資料庫的對比，資料庫的對比顯示的只是缺少的資料庫物件，理解起來更加容易。

 

 

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結。

《歡迎交流討論》

相關文章