MySQL创建数据库（如果不存在）

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

我有一个T-SQL查询,如果它还不存在,它会创建数据库:

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:

当我想在MySQL中使用它时,我收到一个错误:

'IF' is not valid input at this postion

'IF'在此位置无效输入

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?

如何在MySQL中创建此查询?

2 个解决方案

#1

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

以下是辅助(永久)数据库中的示例。该db的名称是永久性的

One time db create:

一次db创建:

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

#2

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

#1