脚本：
以下案例中都是 新库test1， 旧库test 。
1.对比表范围差异：
select a.n_ts as "新库", a.n_tn as "新库表名", a.o_ts as "旧库", a.o_tn as "旧库表名"
, case when a.n_tn is null then '新库缺表'
when a.o_tn is null then '旧库缺表'
else '表名一致' end as "比对结果"
from (
select a.TABLE_SCHEMA as n_ts,a.TABLE_NAME as n_tn, b.table_schema as o_ts, b.table_name as o_tn
from information_schema.TABLES a left join information_schema.TABLES b
on b.TABLE_SCHEMA='test' and a.TABLE_NAME = b.table_name
where a.TABLE_SCHEMA='test1'
union all
select a.TABLE_SCHEMA as n_ts,a.TABLE_NAME as n_tn, b.table_schema as o_ts, b.table_name as o_tn
from information_schema.TABLES a right join information_schema.TABLES b
on a.TABLE_NAME = b.table_name and a.TABLE_SCHEMA='test1'
where b.TABLE_SCHEMA='test' and a.table_name is null
) a
order by if(isnull(a.n_tn),1,0),if(isnull(a.o_tn),1,0),a.n_tn,a.o_tn;
测试案例：
±-------±-------------±-------±-------------±-------------+
| 新库 | 新库表名 | 旧库 | 旧库表名 | 比对结果 |
±-------±-------------±-------±-------------±-------------+
| test1 | mn1 | test | mn1 | 表名一致 |
| test1 | mn2 | test | mn2 | 表名一致 |
| test1 | test2 | test | test2 | 表名一致 |
| test1 | test3 | test | test3 | 表名一致 |
| NULL | NULL | test | mn3 | 新库缺表 |
±-------±-------------±-------±-------------±-------------+
5 rows in set (0.01 sec)
±-------±-------------±-------±-------------±-------------+
| 新库 | 新库表名 | 旧库 | 旧库表名 | 比对结果 |
±-------±-------------±-------±-------------±-------------+
| test1 | mn1 | test | mn1 | 表名一致 |
| test1 | mn2 | test | mn2 | 表名一致 |
| test1 | test2 | test | test2 | 表名一致 |
| test1 | test3 | test | test3 | 表名一致 |
| test1 | mn3 | NULL | NULL | 旧库缺表 |
±-------±-------------±-------±-------------±-------------+
5 rows in set (0.00 sec)
2.对比表字段差异：
select n_ts as "newdb", n_tn as "newtable", n_cn as "newcol", n_dt as "newcol_type", n_ct as "newcol_length", n_cd as "newcol_default", n_in as "newcol_null",n_ck as "newcol_key",n_cex as "newcol_autoincr"
,o_ts as "olddb", o_tn as "oldtable", o_cn as "oldcol", o_dt as "oldcol_type", o_ct as "oldcol_length", o_cd as "oldcol_default", o_in as "oldcol_null",o_ck as "oldcol_key",o_cex as "oldcol_autoincr"
,case when n_cn is null then '字段不一致新库缺字段'
when n_dt o_dt then '字段类型不一致'
when n_ct o_ct then '字段长度不一致'
when ifnull(n_cd,'') ifnull(o_cd,'') then '字段默认值不一致'
when ifnull(n_in,'') ifnull(o_in,'') then '字段是否可空不一致'
when ifnull(n_ck,'') ifnull(o_ck,'') then '字段键值不一致'
when ifnull(n_cex,'') ifnull(o_cex,'') then '字段是否自增不一致'
when o_cn is null then '旧库缺字段'
else '字段一致' end as "对比结果"
from (
select a.TABLE_SCHEMA as n_ts, a.TABLE_NAME as n_tn, a.COLUMN_NAME as n_cn, a.DATA_TYPE as n_dt, a.COLUMN_TYPE as n_ct, a.COLUMN_DEFAULT as n_cd, a.IS_NULLABLE as n_in,a.COLUMN_KEY as n_ck,a.EXTRA as n_cex
, b.TABLE_SCHEMA as o_ts, b.TABLE_NAME as o_tn, b.COLUMN_NAME as o_cn, b.DATA_TYPE as o_dt, b.COLUMN_TYPE as o_ct, b.COLUMN_DEFAULT as o_cd, b.IS_NULLABLE as o_in,b.COLUMN_KEY as o_ck,b.EXTRA as o_cex
from information_schema.`COLUMNS` a left join information_schema.`COLUMNS` b
on b.TABLE_SCHEMA='test' and a.TABLE_NAME = b.table_name and a.COLUMN_NAME = b.COLUMN_NAME
where a.TABLE_SCHEMA='test1'
and a.table_name in(
select a.table_name from information_schema.TABLES a ,information_schema.TABLES b
where a.TABLE_SCHEMA='test1' and b.TABLE_SCHEMA='test' and a.table_name = b.table_name)
union all
select a.TABLE_SCHEMA as n_ts, a.TABLE_NAME as n_tn, a.COLUMN_NAME as n_cn, a.DATA_TYPE as n_dt, a.COLUMN_TYPE as n_ct, a.COLUMN_DEFAULT as n_cd, a.IS_NULLABLE as n_in,a.COLUMN_KEY as n_ck,a.EXTRA as n_cex
, b.TABLE_SCHEMA as o_ts, b.TABLE_NAME as o_tn, b.COLUMN_NAME as o_cn, b.DATA_TYPE as o_dt, b.COLUMN_TYPE as o_ct, b.COLUMN_DEFAULT as o_cd, b.IS_NULLABLE as o_in,b.COLUMN_KEY as o_ck,b.EXTRA as o_cex
from information_schema.`COLUMNS` a right join information_schema.`COLUMNS` b
on a.TABLE_SCHEMA='test1' and a.TABLE_NAME = b.table_name and a.COLUMN_NAME = b.COLUMN_NAME
where b.TABLE_SCHEMA='test'
and b.table_name in(
select a.table_name from information_schema.TABLES a ,information_schema.TABLES b
where a.TABLE_SCHEMA='test1' and b.TABLE_SCHEMA='test' and a.table_name = b.table_name) and a.table_name is null) a
order by if(isnull(a.n_tn),1,0),if(isnull(a.o_tn),1,0),a.n_tn,a.n_cn,a.o_tn,a.o_cn;
2.1可对比内容：
可对比出 字段缺失，字段类型不一致，字段长度不一致，字段是否可空不一致。
可对比出 主键不一致，唯一键不一致，单列普通索引不一致的情况。
无法对比出 普通索引复合索引不一致情况: index(id,name)和index(id)
2.2测试案例：
create table test.mn3 (id int(11) primary key auto_increment, `name` varchar(5),address varchar(20));
create table test1.mn3 (id int(11), `name` varchar(5),address varchar(20));
结果显示：
| test1 | mn3 | id | int | int(11) | NULL | YES | | test | mn3 | id | int | int(11) | NULL | NO | PRI | 字段是否可空不一致 |
drop table test1.mn3;
create table test1.mn3 (id int(11) not null, `name` varchar(5),address varchar(20));
结果显示：
| test1 | mn3 | id | int | int(11) | NULL | NO | | test | mn3 | id | int | int(11) | NULL | NO | PRI | 字段键值不一致 |
drop table test1.mn3;
create table test1.mn3 (id int(11) primary key, `name` varchar(5),address varchar(20));
结果显示：
| test1 | mn3 | id | int | int(11) | NULL | NO | PRI | | test | mn3 | id | int | int(11) | NULL | NO | PRI | auto_increment | 字段是否自增不一致 |
create table test.mn4 (id int(11) unique key, `name` varchar(5),address varchar(20));
create table test1.mn4 (id int(11) , `name` varchar(5),address varchar(20));
结果显示：
| test1 | mn4 | id | int | int(11) | NULL | YES | | | test | mn4 | id | int | int(11) | NULL | YES | UNI | | 字段键值不一致 |
drop table test.mn4;
create table test.mn4 (id int(11) , name varchar(5),address varchar(20) ,primary key(id,name));
| test1 | mn4 | id | int | int(11) | NULL | YES | | | test | mn4 | id | int | int(11) | NULL | NO | PRI | | 字段是否可空不一致 |
| test1 | mn4 | name | varchar | varchar(5) | NULL | YES | | | test | mn4 | name | varchar | varchar(5) | NULL | NO | PRI | | 字段是否可空不一致 |
drop table test.mn4;
drop table test1.mn4;
create table test.mn4 (id int(11) , name varchar(5),address varchar(20) ,primary key(id,name));
create table test1.mn4 (id int(11) , name varchar(5),address varchar(20) ,primary key(id));
| test1 | mn4 | name | varchar | varchar(5) | NULL | YES | | | test | mn4 | name | varchar | varchar(5) | NULL | NO | PRI | | 字段是否可空不一致 |
drop table test.mn4;
drop table test1.mn4;
create table test.mn4 (id int(11) , name varchar(5),address varchar(20) ,unique key(id,name));
create table test1.mn4 (id int(11) , name varchar(5),address varchar(20) ,unique key(id));
| test1 | mn4 | id | int | int(11) | NULL | YES | UNI | | test | mn4 | id | int | int(11) | NULL | YES | MUL | | 字段键值不一致 |
| test1 | mn4 | name | varchar | varchar(5) | NULL | YES | | | test | mn4 | name | varchar | varchar(5) | NULL | YES | | | 字段一致 |
drop table test.mn4;
drop table test1.mn4;
create table test.mn4 (id int(11) , name varchar(5),address varchar(20) ,index(id));
create table test1.mn4 (id int(11) , name varchar(5),address varchar(20) );
| test1 | mn4 | id | int | int(11) | NULL | YES | | | test | mn4 | id | int | int(11) | NULL | YES | MUL | | 字段键值不一致
drop table test.mn4;
drop table test1.mn4;
create table test.mn4 (id int(11) , name varchar(5),address varchar(20) ,index(id,name));
create table test1.mn4 (id int(11) , name varchar(5),address varchar(20),index(id) );
| test1 | mn4 | id | int | int(11) | NULL | YES | MUL | | test | mn4 | id | int | int(11) | NULL | YES | MUL | | 字段一致 |
| test1 | mn4 | name | varchar | varchar(5) | NULL | YES | | | test | mn4 | name | varchar | varchar(5) | NULL | YES | | | 字段一致 |
drop table test.mn4;
drop table test1.mn4;
create table test.mn4 (id int(11) , name varchar(5),address varchar(20) ,index(name));
create table test1.mn4 (id int(11) , name varchar(5),address varchar(20),index(id) );
| test1 | mn4 | id | int | int(11) | NULL | YES | MUL | | test | mn4 | id | int | int(11) | NULL | YES | | | 字段键值不一致 |
| test1 | mn4 | name | varchar | varchar(5) | NULL | YES | | | test | mn4 | name | varchar | varchar(5) | NULL | YES | MUL | | 字段键值不一致 |
alter table test1.mn4 drop column address;
| test1 | mn4 | id | int | int(11) | NULL | YES | MUL | | test | mn4 | id | int | int(11) | NULL | YES | | | 字段键值不一致 |
| test1 | mn4 | name | varchar | varchar(5) | NULL | YES | | | test | mn4 | name | varchar | varchar(5) | NULL | YES | MUL | | 字段键值不一致 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | test | mn4 | address | varchar | varchar(20) | NULL | YES | | | 字段不一致新库缺字段 |
drop table test.mn4;
drop table test1.mn4;
create table test.mn4 (id int(11) , name varchar(15),address text);
create table test1.mn4 (id int(11) , name varchar(5),address varchar(20));
| test1 | mn4 | address | varchar | varchar(20) | NULL | YES | | | test | mn4 | address | text | text | NULL | YES | | | 字段类型不一致 |
| test1 | mn4 | name | varchar | varchar(5) | NULL | YES | | | test | mn4 | name | varchar | varchar(15) | NULL | YES | | | 字段长度不一致 |
drop table test.mn4;
drop table test1.mn4;
create table test.mn4 (id int(11) , name varchar(15),login_date date);
create table test1.mn4 (id int(11) , name varchar(15),login_date timestamp);
| test1 | mn4 | login_date | timestamp | timestamp | CURRENT_TIMESTAMP | NO | | on update CURRENT_TIMESTAMP | test | mn4 | login_date | date | date | NULL | YES | | | 字段类型不一致 |
