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