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 - http://stackoverflow.com/questions/838978/how-to-check-if-mysql-database-exists — Wasi Ahmad, Oct 30 '16 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, Oct 30 '16 at 21:14

score 109 · Answer 1 · answered Oct 30 '16 at 20:24

109

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 '16 at 20:24

Dec Sander

1,327
1
9
5

I know that that I can use `IF NOT EXISTS` but i need return string `'DATABASE_CREATED'` or `'DATABASE_EXIST'` – Szymson Oct 30 '16 at 20:29
1

then make a stored proc out of it and pass a parameter – Drew Oct 30 '16 at 20:33
@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 – Drew Oct 30 '16 at 20:37
stored proc can be added to database but I need create database first – Szymson Oct 30 '16 at 21:04
1

Right. A call to a stored proc in a helper db. – Drew Oct 30 '16 at 21:15

score 9 · Accepted Answer · answered Oct 30 '16 at 21:42

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

Thanks it was what i needed :) – Szymson Oct 30 '16 at 21:45 — Szymson, Oct 30 '16 at 21:45

MySQL create database if not exist

2 Answers2

Linked