creating a non-destructive mysql query with the ability to create tables or update tables with missing field

Question

I have exported a database (structure only) from phpMyAdmin which I want to use as the bases for a script that can update a previous version of the same database structure and add columns and tables if they don't exist.

For example, if the CREATE TABLE statement looks like this:

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(52) NOT NULL,
  `password` varchar(40) NOT NULL,
  `dev` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21;

How can I adjust this so if a field in the table does not exist it will be created even if the table already exists in the database.

The aim of this script is to allow updating of already existing databases, adding columns that may be missing, but it should not remove any tables or fields.

Answer 1

According to this, it seems the easiest way to accomplish this is to create a temporary table (by which I don't necessarily mean of type TEMPORARY, just that the use will be short and limited; although in most cases type TEMPORARY may be a reasonable choice here), copy the data from your old table to the temporary one, create the new table with the updated structure, then bring everything back over from the temporary table. Finally, your script may rename the old table as a backup or drop it and the temporary table entirely.