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

Question

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

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

示例： 数据库1（ID，姓名，额外） 数据库2（ID，名字，姓氏）

我想在数据库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

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

这里有两个示例表

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) 

Answer 2

我不知道有一个工具可以做到这一点，但使用某些脚本很容易。

在MYSQL中，数据库有一个隐含的INFORMATION_SCHEMA，其中包含关于该数据库中其他表的信息的表。

可以使用

SELECT c1.table_name, c1.column_name 
FROM db_1.INFORMATION_SCHEMA.columns c1 LEFT JOIN 
    db_2.INFORMATION_SCHEMA.columns c2 
     ON c1.table_name = c2.table_name AND c1.column_name = c2.column_name 
WHERE c2.column_name IS NULL 

这是假设DB_1是第一和db_2是第二个连接的表。

这将有助于查找列，然后您可以轻松地添加缺少的列。