mysqldiff工具对比mysql数据库表结构差异 2019-07-22 Website News 暂无评论 1545 次阅读 主要介绍使用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 ![001QI5G-0.png][1] 2.使用--skip-table-options ![001QML7-1.png][2] 其实用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 ; [1]: https://www.aladown.com/usr/uploads/2019/07/2521982942.png [2]: https://www.aladown.com/usr/uploads/2019/07/1433871777.png 标签: none 本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。
评论已关闭