我有一个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'

当我想在MySQL中使用它时,我会得到一个错误:
“if”不是此位置的有效输入
我将此脚本更改为
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'`

但还是不行
如何在MySQL中创建此查询?

最佳答案

下面是助手(永久)数据库中的示例。数据库的名称是
一次性数据库创建:

create schema permanent;

现在确定你
USE permanent;

然后
存储过程:
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 ;

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

关于mysql - MySQL创建数据库(如果不存在),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/40333251/

10-10 23:20
查看更多