[MYSQL] DB 테이블 및 컬럼 변경사항 추출 스크립트

-- 테이블
select '추가' as type
     , b.table_schema, b.table_name
     , a.table_schema, a.table_name
  from information_schema.tables a
  left join information_schema.tables b on a.table_name = b.table_name
                                       and b.table_schema = 'OLD_DB'
 where a.table_schema = 'NEW_DB'
   and b.table_name is null
union all
select '삭제' as type
     , b.table_schema, b.table_name
     , a.table_schema, a.table_name
  from information_schema.tables a
  left join information_schema.tables b on a.table_name = b.table_name
                                       and b.table_schema = 'NEW_DB'
 where a.table_schema = 'OLD_DB'
   and b.table_name is null;
 
-- 컬럼
select '삭제' as type
     , a.table_schema, a.table_name, a.column_name, a.column_type, a.column_comment, a.is_nullable, a.column_default
     , b.table_schema, b.table_name, b.column_name, b.column_type, b.column_comment, b.is_nullable, b.column_default
  from information_schema.columns a
  left join information_schema.columns b on a.table_name = b.table_name
                                        and a.column_name = b.column_name
                                        and b.table_schema = 'NEW_DB'
 where a.table_schema = 'OLD_DB'
   and b.table_name is null
union all
select '추가' as type
     , b.table_schema, b.table_name, b.column_name, b.column_type, b.column_comment, b.is_nullable, b.column_default
     , a.table_schema, a.table_name, a.column_name, a.column_type, a.column_comment, a.is_nullable, a.column_default
  from information_schema.columns a
  left join information_schema.columns b on a.table_name = b.table_name
                                        and a.column_name = b.column_name
                                        and b.table_schema = 'OLD_DB'
 where a.table_schema = 'NEW_DB'
   and b.table_name is null
union all
select concat(trim(trailing ',' from type), ' 변경') as type
     , db1, tbl1, col1, type1, comment1, nullable1, default1
     , db2, tbl2, col2, type2, comment2, nullable2, default2
  from (
        select concat(
                      if(ifnull(a.column_type, '') <> ifnull(b.column_type, ''), 'column_type,', '')
                    , if(ifnull(a.column_comment, '') <> ifnull(b.column_comment, ''), 'comment,', '')
                    , if(ifnull(a.is_nullable, '') <> ifnull(b.is_nullable, ''), 'nullable,', '')
                    , if(ifnull(a.column_default, '') <> ifnull(b.column_default, ''), 'default', '')
                     ) as type
             , a.table_schema as db1, a.table_name as tbl1, a.column_name as col1, a.column_type as type1, a.column_comment as comment1, a.is_nullable as nullable1, a.column_default as default1
             , b.table_schema as db2, b.table_name as tbl2, b.column_name as col2, b.column_type as type2, b.column_comment as comment2, b.is_nullable as nullable2, b.column_default as default2
          from information_schema.columns a
          join information_schema.columns b on a.table_name = b.table_name
                                           and a.column_name = b.column_name
                                           and b.table_schema = 'NEW_DB'
         where a.table_schema = 'OLD_DB'
           and (
                ifnull(a.column_type, '') <> ifnull(b.column_type, '')
                or ifnull(a.column_comment, '') <> ifnull(b.column_comment, '')
                or ifnull(a.is_nullable, '') <> ifnull(b.is_nullable, '')
                or ifnull(a.column_default, '') <> ifnull(b.column_default, '')
               )
       ) a;

MySQL용 (5.7)

시간 되면 좀 더 고도화 하자