CODEWITHSUNDEEP
mysqlif-statementexit
vossman77
vossman77

Reputation: 1417

How exit a MySQL script if a database exists

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)

Upvotes: 1

Views: 4325

Answers (3)

Martin
Martin

Reputation: 22760

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.

Upvotes: 0

vossman77
vossman77

Reputation: 1417

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.

Upvotes: 0

Vancold.at
Vancold.at

Reputation: 138

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 ;)

Upvotes: 1

Related Questions