3

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.

EDIT : I just found out about describe ( https://electrictoolbox.com/mysql-table-structure-describe/ ) which might be a great help on this. I've read all the answers, which look great but I think the best way would be to use DESCRIBE on both tables and then compare the results to add the missing columns to table2 using informations from the DESCRIBE query of table1

EDIT2: Bill Karwin answer is the best one to me. He uses information_schema which is better than DESCRIBE (only difference would be that it could be slower). The only thing after that would be to ALTER TABLE table2 with the informations we got from the query.

hellodracon
  • 165
  • 1
  • 12
  • Seems like all the columns you added to TABLE1 are missing in TABLE2? Perhaps you should simply add columns to TABLE1 and TABLE2 at the same time? Perhaps there's a real problem underneath your theoretical question, but I don't get it. – KIKO Software Oct 17 '22 at 15:06
  • Sorry, maybe I didn't explain it well. I've created table1 and table2 at the same moment. But some times after, I've updated table1 (by adding new columns) that aren't in table2. I need to find a solution to compare them when running a script and update columns of table2 by adding missing ones compared to table1 – hellodracon Oct 17 '22 at 15:14
  • 1
    OK, I think I get it. So, the first thing to do is to find out what the columns in a table are. Only when you know that, can you compare them. Have a look at `SHOW COLUMNS FROM ;`. Or [check this answer](https://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql). When you have both you can use [array_diff()](https://www.php.net/manual/en/function.array-diff) on the Field column.
     – KIKO Software Oct 17 '22 at 15:19
  • Instead of adding an "EDIT" to your question, you can answer your own question. That aligns better with the format of Stack overflow, and you can even award yourself some points. – KIKO Software Oct 18 '22 at 08:27
  • Yes but I found the Bill Karwin solutions was better after that. I was going to re-edit. – hellodracon Oct 18 '22 at 08:32
  • No problem. Yes, Bill Karwin's solution is very nice. – KIKO Software Oct 18 '22 at 08:38

3 Answers3

3
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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Ok I wrote before that I just found out about DESCRIBE but information_schema is better. Your answer looks to be the best one. The only thing afterwards would be to alter table TABLE2 with the information_schema infos. I'll check you as the answer – hellodracon Oct 18 '22 at 08:26
  • It is not easy to construct a full column definition from the stuff in infromation_schema. – Rick James Oct 18 '22 at 18:49
  • @RickJames, True, but the above query would at least quickly identify if there are any discrepancies between the tables, and name the columns to add. If there are none, then you're done. If there are a few, then the developer can add them manually. – Bill Karwin Oct 18 '22 at 19:15
1

(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

mobailey
  • 21
  • 4
  • Yes, MySQL has similar information_schema built-in tables, but there's nothing similar to `MINUS`, as far as I know. Subtracting two sets... Hmmm. Does somebody know how to do that in MySQL? – KIKO Software Oct 17 '22 at 15:32
  • 1
    MySQL does appear to have MINUS - see https://www.mysqltutorial.org/mysql-minus/. Its little used in any SQL language, but handy to know about. – mobailey Oct 18 '22 at 08:02
  • Well, it doesn't have a MINUS, but it can be emulated it with a LEFT JOIN, similar to what Bill Karwin did in his answer. – KIKO Software Oct 18 '22 at 08:25
  • Thanks, looked great, I like the idea but @Bill Karwin answers looks to be better. – hellodracon Oct 18 '22 at 08:28
  • You're right! Sorry I've only just read my own link, which I thought was describing MySQL's MINUS command, and it says: "Note that MySQL does not support the MINUS operator. This tutorial shows you to how to emulate the MINUS operator in MySQL using join clauses." Bah. – mobailey Oct 18 '22 at 09:57
  • We all do this, jumping to conclusions to quickly. The title is also somewhat misleading, it should read: _"MySQL MINUS emulation"_. – KIKO Software Oct 18 '22 at 18:56
0

Doing the task manually is not that complex, and is probably fewer keystrokes than trying to cobble the equivalent from information_schema.columns, etc.

  1. SHOW CREATE TABLE for each table.
  2. Spot the 'new' columns.
  3. Copy & paste them into an ALTER TABLE along with ADD COLUMN in front of each.
  4. Execute the ALTER.

If you need to do this on lots of tables, then I have to ask "why".

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • The question says: _"I could do it manually, but I want to avoid it."_ A comment would be more appropriate if you want to ask _"why"_. – KIKO Software Oct 18 '22 at 18:38