MySQL If Database Not Exists Create DB - Else Alter Table

I'm using MySQL 5.5.15. I have a .sql file I'm using as part of an installer.  I need to check whether the database exists or not.

If it doesn't exist then create a new database and tables.
If it DOES exist then update the database tables with additional columns.

I'd like to check this with a simple IF statement:

If NOT EXISTS(SELECT SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'MyDatabase')
THEN
     Create MyDatabase;
     Create Tables;
Else
    Alter Table1;
    Alter Table2;
    ...
    Alter TableN;
End If;

Why doesn't this work?  The problem is the IF statement. It seems logical to me but I'm obviously thinking differently than MySQL.
Jim LittleVP Technology & DevelopmentAsked:
Who is Participating?
 
Steve BinkConnect With a Mentor Commented:
The IF statement is only available in the context of compound statements, like stored procedures.  To do what you want, create a stored procedure in your script, then call it.  For example:
USE MyDatabase;
DROP PROCEDURE IF EXISTS MyStructure;
DELIMITER //
CREATE PROCEDURE MyStructure AS 
  BEGIN
    SELECT 1 INTO @localVar FROM information_schema.schemata WHERE schema_name='MyOtherDatabase';
    IF @localVar IS NULL THEN
      BEGIN
        /* Database does not exist */
        CREATE DATABASE MyOtherDatabase;
      END;
    ELSE
      BEGIN
      /* Database exists, do whatever here */
      END;
    END IF;
  END;
//
DELIMITER ;
CALL MyStructure;

Open in new window

Disclaimer: untested...just wrote off the top of my head.  You might need some additional syntax, etc.
0
 
ZberteocConnect With a Mentor Commented:
If you check the CREATE DATABASE syntax on mysql website:

http://dev.mysql.com/doc/refman/5.0/en/create-database.html

It actuallky is:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

Same goes for tables.
so actually you don't need the IF statement. You could do something like:
Create MyDatabase If Not Exists;

USE MyDatabase;	 
	 
Create Table1 If Not Exists(
...);
Create Table2 If Not Exists(
...);
...
Create TableN If Not Exists(
...);

Alter Table1;
Alter Table2;
...
Alter TableN;

Open in new window

One question, tough. When you say "doesn't work" what do you mean? Is there an error that you can post?
0
 
ZberteocCommented:
Correct. I forgot that about MySQL. :)
0
All Courses

From novice to tech pro — start learning today.