MySql 查询数据库中所有表名以及对比分布式库中字段和表的不同

时间：2020-12-16 13:45:09

查询数据库中所有表名
select table_name from information_schema.tables where table_schema='数据库名' and table_type='base table';
查询指定数据库中指定表的所有字段名column_name
select column_name from information_schema.columns where table_schema='数据库名' and table_name='表名';

#查看分布式系统中不同库所有相同表名的comment的不同的表

select * from 
        (
        select TABLE_NAME, TABLE_COMMENT from information_schema.tables where table_schema='test1_1207_bak' and table_type='base table'
        ) a
INNER JOIN
     (
    select TABLE_NAME, TABLE_COMMENT from information_schema.tables where table_schema='test2_1207_bak' and table_type='base table'
     ) b on a.TABLE_NAME=b.TABLE_NAME
where a.TABLE_COMMENT!=b.TABLE_COMMENT;

#查看分布式库中同一表的同一字段类型,字段长度和comment等不同之处

select a.table_name,b.table_name,a.column_name,b.column_name,a.data_type,b.data_type,a.COLUMN_TYPE,b.COLUMN_TYPE,
a.CHARACTER_MAXIMUM_LENGTH,b.CHARACTER_MAXIMUM_LENGTH,a.NUMERIC_PRECISION,b.NUMERIC_PRECISION,
a.NUMERIC_SCALE,b.NUMERIC_SCALE,a.COLUMN_KEY,b.COLUMN_KEY,a.EXTRA,b.EXTRA,a.my_COLUMN_COMMENT,b.my_COLUMN_COMMENT from
(
    select 
    table_name,column_name,data_type,COLUMN_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,COLUMN_KEY,EXTRA,
    CASE
         WHEN COLUMN_COMMENT is null THEN ''
         ELSE COLUMN_COMMENT
    END as my_COLUMN_COMMENT
    from information_schema.columns 
    where table_schema='test2_1207_bak'
) a
INNER JOIN 
(
    select 
    table_name,column_name,data_type,COLUMN_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,COLUMN_KEY,EXTRA,
    CASE
         WHEN COLUMN_COMMENT is null THEN ''
         ELSE COLUMN_COMMENT
    END as my_COLUMN_COMMENT
    from information_schema.columns 
    where table_schema='test1_1207_bak'
) b on a.table_name=b.table_name and a.column_name=b.column_name 
where a.data_type!=b.data_type or a.my_COLUMN_COMMENT!=b.my_COLUMN_COMMENT;

#查询缺失表语句：

select table_name from information_schema.tables where table_schema='test1_1207_bak' and table_type='base table' and table_name not in(
   select table_name from information_schema.tables where table_schema='test2_1207_bak' and table_type='base table' 
);

#改用存储过程实现为

# 对比不同库中表区别存储过程
DROP PROCEDURE IF EXISTS p_find_dif_table;
delimiter $$
create procedure p_find_dif_table(database_name1 VARCHAR(100),database_name2 VARCHAR(100))
begin
   -- 比较database_name1库表名与database_name2表名不同的表
select table_name as db1_table_name from information_schema.tables where table_schema=database_name1 and table_type='base table' 
        and table_name not in(
             select table_name from information_schema.tables where table_schema=database_name2 and table_type='base table' 
        );
-- 比较database_name2库表名与database_name1表名不同的表
select table_name as db2_table_name from information_schema.tables where table_schema=database_name2 and table_type='base table' 
        and table_name not in(
             select table_name from information_schema.tables where table_schema=database_name1 and table_type='base table' 
        );
end;
$$
delimiter ;
-- 调用存储过程
CALL p_find_dif_table('test1_1207_bak','test2_1207_bak');

#查询具体表缺失字段语句：

select column_name from information_schema.columns where table_schema='test2_1207_bak' and table_name='oi_auto' 
    and column_name not in (
      select column_name from information_schema.columns where table_schema='test1_1207_bak' and table_name='oi_auto' 
    );

# 改用存储过程和游标实现，对比数据库缺失字段

drop TABLE if EXISTS `release_no_field`;
CREATE TABLE `release_no_field` (
        `column_name` varchar(255),
        `table_name` varchar(255),
        `db_name` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='正式库中缺失字段';

drop TABLE if EXISTS `beta_no_field`;
CREATE TABLE `beta_no_field` (
        `column_name` varchar(255),
        `table_name` varchar(255),
        `db_name` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试库中缺失字段';

# 对比不同库中缺失字段存储过程
DROP PROCEDURE IF EXISTS p_find_dif_column;
delimiter $$
create procedure p_find_dif_column(release_db VARCHAR(100),beta_db VARCHAR(100))
begin
    DECLARE  cur_table VARCHAR(100);
    DECLARE no_more_record INT DEFAULT 0;
    DECLARE  cur_record CURSOR FOR   
       SELECT TABLE_NAME from information_schema.tables
              where table_schema=release_db and table_type='base table' and TABLE_NAME not in('release_no_field','beta_no_field');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET  no_more_record = 1;
    OPEN  cur_record;  
    FETCH  cur_record INTO cur_table;
  
    delete from release_no_field;
    delete from beta_no_field;

 WHILE no_more_record != 1 DO
         -- 正式库中缺失字段
          INSERT INTO release_no_field(column_name,table_name,db_name) select column_name,table_name,release_db from information_schema.columns where table_schema=beta_db and table_name=cur_table 
            and column_name not in (
                select column_name from information_schema.columns where table_schema=release_db and table_name=cur_table
            ); 
         -- 测试库中缺失字段
         INSERT INTO beta_no_field(column_name,table_name,db_name)  select column_name,table_name,beta_db  from information_schema.columns where table_schema=release_db and table_name=cur_table
            and column_name not in (
                select column_name from information_schema.columns where table_schema=beta_db and table_name=cur_table 
            );
   FETCH  cur_record INTO cur_table; 
 END WHILE;    
     CLOSE  cur_record;
    
    SELECT * from release_no_field;
    SELECT * from beta_no_field;
end;
$$
delimiter ;

对比不同库同一表同一字段类型不一致

SELECT
    hosp_release.table_name AS table_name,
    hosp_release.column_name AS column_name,
    hosp_release.column_type AS hosp_release_column_type,
    hosp_beta.column_type AS hosp_beta_column_type 
FROM
    (
  select table_name,column_name,column_type from  information_schema.columns  where table_schema='hosp2' and table_name in (
  select TABLE_NAME from information_schema.tables where table_schema='hosp2' and table_type='base table'
   )) hosp_release , 
     (
  select table_name,column_name,column_type from  information_schema.columns  where table_schema='hosp_shunde_0226' and table_name in (
  select TABLE_NAME from information_schema.tables where table_schema='hosp_shunde_0226' and table_type='base table'
  )) hosp_beta
WHERE hosp_release.table_name=hosp_beta.table_name and hosp_release.column_name=hosp_beta.column_name and hosp_release.column_type!=hosp_beta.column_type

 

标签：

相关文章