How exit a MySQL script if a database exists

Question

I have a SQL script in docker, but I only want to run the script if it has not been run before. So, I though I would have something like:

IF dbname EXISTS
THEN
    exit;
END IF;

Looking around I managed to find code that will return 0 or 1 if the database exists:

SELECT IF(EXISTS(SELECT SCHEMA_NAME 
    FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = 'mysql'),true,false);

but I cannot figure out how to turn this into an exit statement

IF(EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql')
THEN
    exit;
END IF;

gives multiple syntax errors.

I am working with CentOS 6.7 and MySQL server 5.1.73 (forgot it is not MariaDB until CentOS 7)

See also stackoverflow.com/questions/27734205/… which is unanswered — vossman77
– vossman77, Commented Jan 29, 2016 at 15:05

Vancold.at · Accepted Answer · 2016-01-28 17:02:38Z

1

What do you mean? Does the script create anything? If yes you could use this trick:

IF SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = 'demo-database' then

  CREATE DATABASE `demo-database`
  // do other stuff ect

ENDIF

That might work. Atleast for me it did ;)

answered Jan 28, 2016 at 17:02

Vancold.at

1387 bronze badges

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

2 Comments

vossman77 Over a year ago

my SQL script creates the database and inserts data. It is almost 8000 lines, so I do not want to put the whole thing into an if statement.

vossman77 Over a year ago

This is the source of my confusion. If I enter your code in mysql interactive I get: ERROR 1064 (42000): 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 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'dem' at line 1

vossman77 · Accepted Answer · 2016-01-30 22:27:03Z

0

After further thought and research, I think this is a limitation of the MySQL console. I was treating my SQL "script" as a script where it is actually just a list of commands. The IF statement in MySQL really designed to be part of a SELECT statement. So, I make this decision to run the SQL commands within a bash script.

answered Jan 30, 2016 at 22:27

vossman77

1,42715 silver badges14 bronze badges

Comments

Mm-Art-In · Accepted Answer · 2016-01-30 23:01:55Z

0

Rather than causing a certain action if the script exists, have your original action occur only if the script does NOT exist, such as

CREATE DATABASE IF NOT EXISTS DBName;
///all the rest of your SQL,

This means the SQL will only run if the data structure is not already there. This looks like what you're wanting.

answered Jan 30, 2016 at 23:01

Mm-Art-In

23.1k13 gold badges81 silver badges144 bronze badges

Collectives™ on Stack Overflow

How exit a MySQL script if a database exists

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related