mysql - Alter table if exists or create if doesn't

Question

I need to run an installer which can also be an updater. The installer needs to be able to end up having a certain scheme/structure of the mysql database, regardless if some of the tables existed, missed a few columns, or need not to be changed because their structure is up to date.

How can I make an elegant combination of ALTER and CREATE?

I was thinking there must be something like "ADD... IF... Duplicate"

Say I have table A. In one client the table has one column -A1, and another client has the same table but with column A1 and column A2.

I want my sql command to make both clients' table A hold three columns : A1, A2, and A3.

Again, my script is a sql file that I dump to mysql.

How do I do it? Thanks :-)

Answer 1

MySQL INFORMATION_SCHEMA database to the rescue:

-- First check if the table exists
IF EXISTS(SELECT table_name 
            FROM INFORMATION_SCHEMA.TABLES
           WHERE table_schema = 'db_name'
             AND table_name LIKE 'wild')

-- If exists, retreive columns information from that table
THEN
   SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
     FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'tbl_name'
      AND table_schema = 'db_name';

   -- do some action, i.e. ALTER TABLE if some columns are missing 
   ALTER TABLE ...

-- Table does not exist, create a new table
ELSE
   CREATE TABLE ....

END IF;

More information:

• MySQL Reference Manual: Chapter 19. INFORMATION_SCHEMA Tables
• MySQL Reference Manual: The INFORMATION_SCHEMA TABLES Table
• MySQL Reference Manual: The INFORMATION_SCHEMA COLUMNS Table

UPDATE:

Another option, possibly easier, is to drop the existing table and re-create it again with the new schema. To do this, you need:

1. Create temporary table, an exact copy of the existing table
2. Populate the temporary table with the data from the old table
3. Drop the old table
4. Create the new table with new schema
5. Populate the new table with the information from the temporary table
6. Drop temporary table.

So, in SQL code:

CREATE TABLE old_table_copy LIKE old_table;

INSERT INTO old_table_copy
SELECT * FROM old_table;

DROP TABLE old_table;

CREATE TABLE new_table (...new values...);

INSERT INTO new_table ([... column names from old table ...])
SELECT [...column names from old table ...] 
FROM old_table_copy;

DROP TABLE old_table_copy;

Actually the last step, "Drop temporary table.", you could skip for a while. Just in case, you would want to have some sort of backup of the old table, "just-in-case".

More information:

• MySQL Reference Manual: CREATE TABLE Syntax
• MySQL Reference Manual: INSERT Syntax
• MySQL Reference Manual: INSERT ... SELECT Syntax