MySQL 比较数据库并添加缺失的列

Question

我有两个非常大的数据库，它们在各个方面都几乎相同，但是一个在每个表中都有很多额外的列，而第二个没有。

将行保留为默认值，我将如何比较表并将第一个数据库中定义的缺失列添加到第二个数据库？

例子:数据库 1 (id, name, surname, extra)数据库2(id,name,surname)

我想将数据库 1 中定义的“额外”列添加到数据库 2。

我不太关心实际的额外数据，它可以设置为数据库 1 中定义的默认值。所需的列就足够了。

请注意:我尝试过 Toad 等工具，但它们都想更改第二个数据库中的数据以匹配我不想要的第一个数据库。

数据库 1 示例表(CollectionSearchIndexAttributes):

CREATE TABLE IF NOT EXISTS `CollectionSearchIndexAttributes` (
  `cID` int(10) unsigned NOT NULL DEFAULT '0',
  `ak_meta_title` longtext COLLATE utf8_unicode_ci,
  `ak_meta_description` longtext COLLATE utf8_unicode_ci,
  `ak_meta_keywords` longtext COLLATE utf8_unicode_ci,
  `ak_icon_dashboard` longtext COLLATE utf8_unicode_ci,
  `ak_exclude_nav` tinyint(1) DEFAULT '0',
  `ak_exclude_page_list` tinyint(1) DEFAULT '0',
  `ak_header_extra_content` longtext COLLATE utf8_unicode_ci,
  `ak_tags` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ak_is_featured` tinyint(1) DEFAULT '0',
  `ak_exclude_search_index` tinyint(1) DEFAULT '0',
  `ak_exclude_sitemapxml` tinyint(1) DEFAULT '0',
  `ak_job_posting_department` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ak_job_location` longtext COLLATE utf8_unicode_ci,
  `ak_exclude_subpages_from_nav` tinyint(1) DEFAULT '0',
  `ak_thumbnail` int(11) DEFAULT '0',
  `ak_blog_entry_topics` longtext COLLATE utf8_unicode_ci,
  `ak_project_topics` longtext COLLATE utf8_unicode_ci,
  `ak_project_client` longtext COLLATE utf8_unicode_ci,
  `ak_project_tasks` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ak_project_skills` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`cID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

数据库 2 示例表(CollectionSearchIndexAttributes):

CREATE TABLE IF NOT EXISTS `CollectionSearchIndexAttributes` (
  `cID` int(10) unsigned NOT NULL DEFAULT '0',
  `ak_meta_title` longtext COLLATE utf8_unicode_ci,
  `ak_meta_description` longtext COLLATE utf8_unicode_ci,
  `ak_meta_keywords` longtext COLLATE utf8_unicode_ci,
  `ak_icon_dashboard` longtext COLLATE utf8_unicode_ci,
  `ak_exclude_nav` tinyint(1) DEFAULT '0',
  `ak_exclude_page_list` tinyint(1) DEFAULT '0',
  `ak_header_extra_content` longtext COLLATE utf8_unicode_ci,
  `ak_tags` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ak_is_featured` tinyint(1) DEFAULT '0',
  `ak_exclude_search_index` tinyint(1) DEFAULT '0',
  `ak_exclude_sitemapxml` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`cID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

如您所见，数据库 1 的表有 8 列，而数据库 2 的表没有。

Answer 1

这是您可以使用的程序，只需根据数据库名称更新它即可。另外，我在这里假设所有表都存在于两个数据库中。

这是两个示例表

mysql> select table_name, column_name
    -> from information_schema.columns 
    -> where table_schema = 'db1'
    -> order by table_name
    -> ;
+------------+-------------+
| table_name | column_name |
+------------+-------------+
| t1         | c1          |
| t1         | c2          |
| t2         | c1          |
| t2         | c2          |
| t2         | c3          |
| t3         | c1          |
| t3         | c2          |
| t3         | c3          |
+------------+-------------+
8 rows in set (0.00 sec)

mysql> 
mysql> select table_name, column_name
    -> from information_schema.columns 
    -> where table_schema = 'db2'
    -> order by table_name
    -> ;
+------------+-------------+
| table_name | column_name |
+------------+-------------+
| t1         | c1          |
| t2         | c1          |
| t2         | c2          |
+------------+-------------+
3 rows in set (0.00 sec)

这是程序

drop procedure sync_tables;

delimiter //
create procedure sync_tables()
begin

declare tab_name  VARCHAR(100);
declare col_name  VARCHAR(100);
declare col_type  VARCHAR(100);

declare sql_cmd  VARCHAR(100);

declare done int default FALSE;

declare cur cursor for 
select concat('alter table ' , 'db2','.',table_name, ' add column ', column_name , ' ' , column_type ) as sql_cmd_c
  from information_schema.columns
  where table_schema = 'db1' 
     and
          concat( table_name, '_', column_name) not in (
              select concat( table_name,'_',column_name) as db2p
              from information_schema.columns   
              where table_schema = 'db2'
);
declare continue handler for not found  set done = TRUE;
read_loop : LOOP
   open cur;
   fetch cur into sql_cmd;
     set @sqlcmd := sql_cmd;
     prepare sqlcmd from @sqlcmd;
     execute sqlcmd;
     if done then 
        leave read_loop;
     end if;
     select 'bhavin';
end LOOP;

end//
delimiter ;

这是运行调用后的更新表

mysql> select table_name, column_name
    -> from information_schema.columns 
    -> where table_schema = 'db1'
    -> order by table_name
    -> ;
+------------+-------------+
| table_name | column_name |
+------------+-------------+
| t1         | c1          |
| t1         | c2          |
| t2         | c1          |
| t2         | c2          |
| t2         | c3          |
| t3         | c1          |
| t3         | c2          |
| t3         | c3          |
+------------+-------------+
8 rows in set (0.00 sec)

mysql> 
mysql> select table_name, column_name
    -> from information_schema.columns 
    -> where table_schema = 'db2'
    -> order by table_name
    -> ;
+------------+-------------+
| table_name | column_name |
+------------+-------------+
| t1         | c1          |
| t1         | c2          |
| t2         | c1          |
| t2         | c2          |
| t2         | c3          |
+------------+-------------+
5 rows in set (0.00 sec)