Updating Table Structure - Leave data in tact

[DR.Wong]

Hey there everyone,

I've hit a bit of a brick wall on this, but I'm sure it's something someone with proper knowledge can do blind folded.

I have a web app.. got a few buddies running it.

I now have an update for that web app.

The MySQL database has a table structure something like :

Table : Countries
Columns : id | country | country_code

They have lots of info (many rows) in this table by now.

The new version of my app needs to update the table to :

Table : Countries
Columns : id | country | country_code | population | capital

I have many changes like this all over my database in many tables.

I even have a table or two to be added.

I have tried exporting a blank database from phpMyAdmin and then importing it into the existing database but this seems to have no effect adding new columns to a table.

Does anyone have a good solution to do this?

[abduraooft]

Does anyone have a good solution to do this? An easy way is to write some php or other serverside code to fetch the rows from your old table and insert into the new one after required changes.

[DR.Wong]

That's a good idea!

Just for interest sake, is there a IF NOT EXIST operator that works on tabled columns?

[abduraooft]

That's a good idea!

Just for interest sake, is there a IF NOT EXIST operator that works on tabled columns?

Why do you need that? Write a query to create your new table like
mysql_query("Create table if not exists `new_table` ...."); and then write queries to fetch the data from old table and query to insert into new table.

[DR.Wong]

Why do you need that? Write a query to create your new table like
mysql_query("Create table if not exists `new_table` ...."); and then write queries to fetch the data from old table and query to insert into new table.

Ah.. I see.

So :

1) Rename old table
2) Create new table structure
3) Carry old data from old table into new table
4) Delete old table

Sound about right? How do you rename tables?

[abduraooft]

http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

[Old Pedant]

Pardon me, but this is all WAY too much work and WAY WAY too complex!!!

If all you need to do is change:

Table : Countries
Columns : id | country | country_code

to

Table : Countries
Columns : id | country | country_code | population | capital

then it is *TRIVIAL* to do! Read the docs:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

ALTER TABLE Countries ADD COLUMN population INT, capital VARCHAR(100);

Presto.

I do this ALL THE TIME! And, yes, you can add indexes, etc., etc., etc.

Is it a good idea to back up the data, first? Of course! But if you aren't backing up daily or at least weekly then you are asking for trouble, anyway.