Mysql: compare the structure of two tables

Question

I have two tables. the values ​​inside them are not identical, but most strcuture is the same, one of the tables contains some additional fields. in 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 to table_1 and set the address and language as NULL. To do this, I need to explicitly set them to null, which is not very good, because my real table is a mess (more than 30 columns). I have only 4-5 new fields, but is there a way to compare the structure between the two tables and see the difference? Instead, I would add new fields.

Answer 1

The following (unchecked) SQL should provide you with a list of columns in both tables.
The in_table_1 and in_table_2 columns will contain “Yes” if the column exists in this 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 can add having count(*) = 1 to return only those columns that are not in both tables.

You might also want to add data types. Take a look at INFORMATION_SCHEMA