0

So in this case, I will get the whole database schema multiple times. But everytime the tables structure might be slightly different than the previous one. Since I already have data inside, is there a way to write a query to compare with the existing table and just adding new columns?

For example I already have this table in my database.

CREATE TABLE `Ages` ( `AgeID` int(11) DEFAULT NULL, `AgeName` varchar(32) DEFAULT NULL, `AgeAbbreviation` varchar(13) DEFAULT NULL, `YouthAge` varchar(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

And in the new schema that I get has the same table but with different columns. CREATE TABLE `Ages` ( `AgeID` int(11) DEFAULT NULL, `AgeName` varchar(32) DEFAULT NULL, `AgeAbbreviation` varchar(13) DEFAULT NULL, `YouthAge` varchar(15) DEFAULT NULL, `AgeLimit` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

In this case the column AgeLimit will be add to the existing table.

Paul
  • 93
  • 2
  • 15
  • You can of course always compare the queries by hand and write an `Alter table` query. You could also write some script retrieving the current table structure and parse the incoming structure and generate the `Alter table` query automatically. But I doubt you can do that with pure SQL. You'll need some external script in some other language doing the parsing and query construction – derpirscher Apr 03 '19 at 18:04
  • Checking if a certain column exists on either definition should be quite easy. It may be a bit more complicated, when columns also may change their definitions – derpirscher Apr 03 '19 at 18:07
  • That's why I am asking here. I know I can always compare by hand but there are hundreds of tables in the file I get which I have to convert them to string and try to grep the column name for each table to compare with those existing ones. Also, I feel really buggy by doing in this way! – Paul Apr 03 '19 at 18:10
  • As I said, I don't think it's possible with sql only (especially if you have the new Schema given as a textfile) but you may need to code this with whatever scripting language you are comfortable with (eg js, c#, python, ...) There is definitely no `create or alter table` command in MySQL – derpirscher Apr 03 '19 at 18:22
  • Yea I know, I write my whole backend in Node.js. I am just asking if there's a way in pure sql. I can just compare the queries I get with those existing columns. But it's just I feel like this is not a great way since I am taking the sql as string and try to grep the column name. – Paul Apr 03 '19 at 18:37

2 Answers2

0

You should be able to do it by looking at the table definitions in the metadata tables (information_schema).

John
  • 3,458
  • 4
  • 33
  • 54
0
  1. You can always look into the existing schema using the information_schema database, which holds the metadata.
  2. You can then import your new schema into a temporary database, creating all tables according to the new schema and then again look into the metadata.
  3. You might be able to use dynamic sql inside a stored procedure to execute alter table statements created from that differences at runtime

But I think, this is a lot easier from the backend nodejs server, because you can easily do step 1 and 2 also from nodejs (it's in fact just querying a bunch of tables) and you have way more possibilities to calculate the differences, create and execute the appropriate queries.

EDIT 1

If you don't have the possiblility of creating a temporary database from the new schema, you will have to find some other way, to extract information from it. I suspect you have a sql-script with (among others) a bunch of CREATE TABLE ... statements, because that's typically what mysqldump creates. So you'll have to parse this script. Again, this seems to be way easier in javascript, if it even is possible in a MySQL stored procedure. If your schema is as well structured as your examples, it's actually just a few lines of code.

EDIT 2

And maybe you can event get some inspiration from here: Compare two MySQL databases There are some tools mentioned which do a synchronization between databases.

derpirscher
  • 14,418
  • 3
  • 18
  • 35