mysql: compare structure of two tables

Question

I have a two tables. values inside them are not identical, but most of the strcuture is the same, one of the tables has some 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.

Answer 1

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

Answer 2

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