mysql表结构差异比对

比对表范围差异

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='db_old' and a.TABLE_NAME = b.table_name
where a.TABLE_SCHEMA='db_new' 
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='db_new' 
where b.TABLE_SCHEMA='db_old' 
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
;

比对表字段差异

select n_ts as "新库", n_tn as "新库表名", n_cn as "新库表字段", n_dt as "新库字段类型", n_ct as "新库字段长度", n_cd as "新库字段默认值", n_in as "新库字段为空"
	, o_ts as "旧库", o_tn as "旧库表名", o_cn as "旧库表字段", o_dt as "旧库字段类型", o_ct as "旧库字段长度", o_cd as "旧库字段默认值", o_in as "旧库字段为空"
    , case when n_cn is null then '新库缺字段'
		when n_dt <> o_dt then '字段类型不一致'
		when n_ct <> n_ct then '字段长度不一致'
        when ifnull(n_cd,'') <> ifnull(o_cd,'') then '字段默认值不一致'
        when ifnull(n_in,'') <> ifnull(o_in,'') 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
, 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
from information_schema.`COLUMNS` a 
	left join information_schema.`COLUMNS` b on b.TABLE_SCHEMA='db_old' and a.TABLE_NAME = b.table_name and a.COLUMN_NAME = b.COLUMN_NAME
where a.TABLE_SCHEMA='db_new' 
	and a.table_name in(
		select a.table_name
		from information_schema.TABLES a ,information_schema.TABLES b
		where a.TABLE_SCHEMA='db_new' 
			and b.TABLE_SCHEMA='db_old' 
			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
, 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
from information_schema.`COLUMNS` a 
	right join information_schema.`COLUMNS` b on a.TABLE_SCHEMA='db_new' and a.TABLE_NAME = b.table_name and a.COLUMN_NAME = b.COLUMN_NAME
where b.TABLE_SCHEMA='db_old' 
	and b.table_name in(
		select a.table_name
		from information_schema.TABLES a ,information_schema.TABLES b
		where a.TABLE_SCHEMA='db_new' 
			and b.TABLE_SCHEMA='db_old' 
			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
;