mysql: compare structure of two tables

I have a two tables. values inside them are not identical, but most of the strcuture is the same, one of the tables has s开发者_开发百科ome extra fields in them. in a simplified form I have something like this:

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

I would like to copy table_2 structore over to table_1 and set address and language as NULL. To do so, I need to explicitly set them to null which is not very good, because my real table is a mess (over 30 columns). I have only 4-5 new fields though, is there a way to just compare structure between two tables and see the difference? I would just add the new fields myself then.

The following (untested) SQL should give you a list of columns in both tables.
The columns in_table_1 and in_table_2 will contain 'Yes' if the column exists in that table.

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;

You could add having count(*) = 1 to only return the columns that aren't in both tables.

You probably want to add datatypes as well. Have a look at the the INFORMATION_SCHEMA

To see the differences in two tables in two different databases

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;

original answer is given by / at https://dba.stackexchange.com/a/75651/193007

I am be too late but I am posting my response so that someone out there can have more options to choose form

I was given a challenge to migrate data from and old db to a new one. One thing was the tables kept their name but the structure has been changed. The default values were given to me on each table and where it was not provided it was someone else job to do it. The RDBMS was MariaDb 10.1 and I used the following script to get the difference in columns.

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