Solved

MySQL If Database Not Exists Create DB - Else Alter Table

Posted on 2014-09-26
3
742 Views
Last Modified: 2014-09-30
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.
0
Comment
Question by:jrlittle86
  • 2
3 Comments
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 40347565
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
 
LVL 50

Accepted Solution

by:
Steve Bink earned 250 total points
ID: 40347703
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40347710
Correct. I forgot that about MySQL. :)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question