Szymson Szymson - 1 month ago 7
MySQL Question

MySQL create database if not exist

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?

Answer

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
Comments