MySQL MySQL如果不存在则创建数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40333251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL create database if not exist
提问by Szymson
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 中创建此查询？
采纳答案by Drew
Here is the example in a helper (permanent) database. That db's name is permanent
这是助手（永久）数据库中的示例。那个数据库的名字是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
回答by Dec Sander
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
