mysql:比较两个表的结构

Question

我有两张桌子。它们内部的值并不相同，但大多数结构是相同的，其中一个表中有一些额外的字段。在一个简化的形式中，我有类似这样的东西：

|table_1|    |table_2|
id           id
name         name
telephone    telephone
email        email
             address
             language

我想将table_2结构复制到table_1，并将地址和语言设置为空。为此，我需要显式地将它们设置为null，这不是很好，因为我的实际表是一团糟(超过30列)。我只有4-5个新字段，有没有办法只比较两个表的结构，看看有什么不同？然后我会自己添加新的字段。

Answer 1

下面的(未经测试的) SQL应该会给出两个表中的列的列表。

如果表中存在in_table_1和in_table_2列，则该列将包含“是”。

select column_name
      ,max(case when table_name = 'table_1' then 'Yes' end) as in_table_1
      ,max(case when table_name = 'table_2' then 'Yes' end) as in_table_2
  from information_schema.columns
 where table_name in('table_1', 'table_2')
   and table_schema = 'your_database'
 group
    by column_name
 order
    by column_name;

您可以添加having count(*) = 1以仅返回不在两个表中的列。

您可能还想添加数据类型。看一看INFORMATION_SCHEMA

Answer 2

​

查看两个不同数据库中两个表的差异

​

​

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

原始答案由/ at https://dba.stackexchange.com/a/75651/193007提供

Answer 3

我来得太晚了，但我正在张贴我的回复，这样人们就可以有更多的选择来选择表格

我遇到了将数据从旧数据库迁移到新数据库的挑战。一件事是表保留了他们的名字，但结构已经改变了。每个表上都给出了缺省值，如果没有提供，则由其他人来完成。关系型数据库管理系统是MariaDb 10.1，我使用以下脚本来获取列的差异。

set @tem = cast('tabl1' as char(90));
set @db_new = cast('db_new' as char(90));
set @db_old = cast('db_old' as char(90));
select n.column_name newCol, (case when o.column_name is null then '''''' else
o.column_name end) oldCol from information_schema.columns as n 
left join information_schema.columns as o on (n.table_name = o.table_name and n.column_name = o.column_name and o.table_schema = @db_old)
where 
n.table_name = @tem
and
n.table_schema = @db_new