MySQL create database if not exist

Question

I have a T-SQL query which create database if it does not exist yet:

IF (NOT EXISTS (SELECT name 
                FROM master.dbo.sysdatabases 
                WHERE ('[' + 'DBName' + ']' = 'DBName'
                   OR name = 'DBName')))
BEGIN 
    CREATE DATABASE DBName

    PRINT 'DATABASE_CREATED' 
END 
ELSE 
    PRINT 'DATABASE_EXIST'

When I want use this in MySQL I get an error:

'IF' is not valid input at this postion

I change this script as

IF(SELECT COUNT(*) FROM SCHEMA_NAME 
   FROM INFORMATION_SCHEMA.SCHEMATA 
   WHERE SCHEMA_NAME = 'DBName') > 0)
THEN BEGIN 
    CREATE DATABASE DBName

    PRINT 'DATABASE_CREATED'
ELSE 
    PRINT 'DATABASE_EXIST'`

but it still doesn't work

How can I create this query in MySQL?

You can see this SO posts too - stackoverflow.com/questions/838978/… — Wasi Ahmad
– Wasi Ahmad, Commented Oct 30, 2016 at 20:25
@MartinSmith but look at the rest of it, master.dbo.sysdatabases ... I have no clue what they are referencing other than mister softie tables — Drew
– Drew, Commented Oct 30, 2016 at 21:14

Dec Sander · Accepted Answer · 2016-10-30 20:24:47Z

127

I'm not sure exactly how you'd check, but if you just want to create it if it doesn't exist, then you can do

CREATE DATABASE IF NOT EXISTS DBname

answered Oct 30, 2016 at 20:24

Dec Sander

1,5072 gold badges10 silver badges5 bronze badges

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

5 Comments

Szymson Over a year ago

I know that that I can use IF NOT EXISTS but i need return string 'DATABASE_CREATED' or 'DATABASE_EXIST'

Drew Over a year ago

then make a stored proc out of it and pass a parameter

Drew Over a year ago

@Szymson It would also help if you knew what server type you have. I appears to not even be mysql so I retagged your question to microsoft

Szymson Over a year ago

stored proc can be added to database but I need create database first

Drew Over a year ago

Right. A call to a stored proc in a helper db.

Drew · Accepted Answer · 2016-10-30 21:42:42Z

11

Here is the example in a helper (permanent) database. That db's name is permanent

One time db create:

create schema permanent;

Now make sure you

USE permanent;

then

Stored Proc:

DROP PROCEDURE IF EXISTS createDB;  
DELIMITER $$
CREATE PROCEDURE createDB(IN pDbName VARCHAR(100))  
BEGIN
    DECLARE preExisted INT;
    DECLARE ret VARCHAR(50);

    SET ret='DATABASE_EXIST';
    SELECT COUNT(*) INTO preExisted
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE SCHEMA_NAME=pDbName;
    IF preExisted=0 THEN
        SET @sql=CONCAT('CREATE SCHEMA ',pDbName); -- add on any other parts of string like charset etc
        PREPARE stmt1 FROM @sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
        -- right here you could assume it worked or take additional
        -- step to confirm it
        SET ret='DATABASE_CREATED';
    END IF;
    SELECT ret as 'col1';
END$$
DELIMITER ;

Test:

use permanent;
call createDB('xyz');
-- returns col1 DATABASE_CREATED
call createDB('xyz');
-- returns col1 DATABASE_EXIST

answered Oct 30, 2016 at 21:42

Drew

25k10 gold badges47 silver badges81 bronze badges

2 Comments

V. Bozz Over a year ago

Late question. Do you have something to say against CREATE DATABASE IF NOT EXISTS DBname ?

Drew Over a year ago

@ValerioBozz if one reads all parts of Q&A's above, there was confusion as to MSSQL or mysql, and from what context it was called from. If you find success with what you suggest then certainly run with it :) Also note that the OP wanted output for DATABASE_CREATED vs DATABASE_EXIST so I was attempting to stay on-topic

Collectives™ on Stack Overflow

MySQL create database if not exist

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related