MySQL单库中Tables,columns对比表元数脚本
脚本：
以下案例中都是 新库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;
