Exit MySQL script if database exists

Question

I have a MySQL script that I want to use only if the database doesn't exist to inject some initial demo data for development. If it does exist I just want to break out of the script. The script starts like

CREATE DATABASE IF NOT EXISTS `demo-database`;
USE `demo-database`;

Is there a way to exit here or above the create database if the database exists so that it wont run through all the table setups and inserts?

I'm still new to SQL in general, @TonyHopkinson, can you give an example? I've tried every combo I can think of with IF at the start and read the docs on the IF/THEN stuff, but I keep getting syntax errors. — Oscar Godson
– Oscar Godson, Commented Jan 1, 2015 at 20:47
@tonyhopkins like I said in my last comment, I tried that. I can't seem to come up with a syntax that works for my use case. I don't think the CREATE DATABASE stuff can be an expression (from what I gather so far) — Oscar Godson
– Oscar Godson, Commented Jan 2, 2015 at 10:49
huh ? of course it can't be an expression. give me a minute. — Tony Hopkinson
– Tony Hopkinson, Commented Jan 2, 2015 at 11:32

Pரதீப் · Accepted Answer · 2015-01-02 02:56:42Z

0

Try this. Use INFORMATION_SCHEMA.SCHEMATA to check the existence of Database

If not exists (SELECT 1 
               FROM   INFORMATION_SCHEMA.SCHEMATA
               WHERE  SCHEMA_NAME = 'demo-database')

CREATE DATABASE `demo-database`
.....

answered Jan 2, 2015 at 2:56

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oscar Godson Over a year ago

Ive played with that a bit but I always get syntax errors. I also checked on a MySQL syntax checker site which also gives errors. But from my script with this in it I get

ERROR 1064 (42000) at line 21: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT 1 FROM   INFORMATION_SCHEMA.SCHEMATA WHERE  SCHEMA_NAME = ' at line 1

Oscar Godson Over a year ago

Oh and the caps IF NOT EXISTS was just me messing with it. I tried with your exact code copy pasted. My MySQL version is 5.6 (mysql Ver 14.14 Distrib 5.6.21, for osx10.8 (x86_64) using EditLine wrapper)

Eric Aya · Accepted Answer · 2019-02-20 16:04:08Z

0

What I believe now is that the construct with an if/else/endif can only be used in a stored program (see also https://dev.mysql.com/doc/refman/8.0/en/if.html)

I do not have a workaround other than creating a stored procedure that contains the code. In my situation I also have code I would to execute only on a test environment. I am creating only the stored procedure on test then as well.

edited Feb 20, 2019 at 16:04

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Feb 20, 2019 at 12:53

Ben Ootjers

3531 silver badge15 bronze badges

Collectives™ on Stack Overflow

Exit MySQL script if database exists

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related