Menu Close

MariaDB两个库元数据比较

lcc0556

1. 表比较

根据对象名、对象类型（表或视图）、排序规则来判断是否一致。

SELECT *
  FROM information_schema.tables a
 WHERE a.table_schema = 'db_name1'
   AND NOT EXISTS(SELECT 1
                    FROM information_schema.tables b
                   WHERE b.table_schema = 'db_name2'
                     AND a.table_name = b.table_name
                     AND a.table_type = b.table_type
                     AND ifnull(a.table_collation,'###') = ifnull(b.table_collation,'###')
                   );

2. 列比较

根据表名、字段名、是否为空、长度、默认值来判断是否一致。

SELECT a.*
  FROM information_schema.columns a
 WHERE a.table_schema = 'db_name1'
   AND NOT EXISTS(SELECT 1
                    FROM information_schema.columns b
                   WHERE b.table_schema = 'db_name2'
                     AND a.table_name = b.table_name
                     AND a.column_name = b.column_name
                     -- AND a.ordinal_position = b.ordinal_position
                     AND a.is_nullable = b.is_nullable
                     AND a.data_type = b.data_type
                     AND a.column_type = b.column_type
                     AND ifnull(a.column_default,'###') = ifnull(b.column_default,'###')
                      );

3. 索引比较

包含主键索引与普通索引。根据表名、索引名称、索引字段序号、列名、是否是唯一索引来判断是否一致。

SELECT a.*
  FROM information_schema.statistics a
 WHERE a.table_schema = 'db_name1'
   AND NOT EXISTS(SELECT 1
                    FROM information_schema.statistics b
                   WHERE b.table_schema = 'db_name2'
                     AND a.table_name = b.table_name
                     AND a.index_name = b.index_name
                     AND a.seq_in_index = b.seq_in_index
                     AND a.column_name = b.column_name
                     AND a.non_unique = b.non_unique
                   );
Post Views: 551
Tagged , ,