using php/mysql to compare two tables and add missing columns from table1 to table2-CodePudding

Ok, Let's say I have two tables with identical structures but different values :

TABLE1 : column.A, column.B, column.C
TABLE2 : column.A, column.B, column.C

I add columns in table 1, it becomes :

TABLE1 : column.A, column.B, column.C, column.D, column.E, column.F ... column.M
TABLE2 : column.A, column.B, column.C

Now, using php/mysql I want to compare TABLE1 and TABLE2 and add in TABLE2 missing columns from TABLE1. I'm talking structure only (columns, not values). I could do it manually, but I want to avoid it.

Is there a way to do it ? I can't find any.

CodePudding user response：

(My answer may be Oracle-specific, but I'm hoping it will lead you to a MySQL solution.)

I'd perform this database operation all inside a database procedure (which you can call from PHP if you want).

In Oracle (which may not help you much), I would:

SELECT column_name FROM user_tab_columns WHERE table_name = 'TABLE1' MINUS SELECT column_name FROM user_tab_columns WHERE table_name = 'TABLE2';

(I believe MySQL has similar information_schema built-in tables).

Then put together an alter table statement as such:

EXECUTE IMMEDIATE 'ALTER TABLE TABLE2 ADD (' & ... comma seperated results from the above SQL ... & ");"

and get data types in the first query too, to include in the alter statement.

-mobailey

CodePudding user response：

create table table1 ( a int, b int, c int, d int, e int);

create table table2 (a int, b int, c int);

select t1.column_name
from information_schema.columns as t1
left outer join information_schema.columns as t2
  on t1.table_schema = t2.table_schema
  and t1.column_name = t2.column_name
  and t2.table_name = 'table2'
where (t1.table_schema, t1.table_name) = ('test', 'table1')
  and t2.table_name is NULL;

 ------------- 
| COLUMN_NAME |
 ------------- 
| d           |
| e           |
 -------------

To produce the necessary ALTER TABLE statement to add the columns, you'll need to inspect the rest of the columns of that information_schema table. See https://dev.mysql.com/doc/refman/8.0/en/information-schema-columns-table.html