如何在创建数据库语句中使用变量 [英] how do I use a variable in create database statement
本文介绍了如何在创建数据库语句中使用变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有此过程:
DELIMITER //
create DEFINER = 'root'@'localhost' procedure create_db(name TEXT)
BEGIN
DECLARE temp TEXT;
DECLARE user TEXT;
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = name INTO temp;
if temp = name then
SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'This database already exist';
else
SELECT USER() INTO user;
create database name;
grant all privileges on name.* to user with grant option;
END IF;
END //
DELIMITER ;
它很好地工作,只是它提供字面意义上的名称"而不是变量名称的值.我如何告诉它名称是变量?类似于php中的$ name之类的东西.我浏览了许多文档,但是它们都使用没有前缀的变量.
it works great just it supply literally "name" instead of value of variable name. How do I tell it that name is a variable? something like $name in php or that. I browser many documentations but they all are using variable with no prefixes.
推荐答案
您只需要使用准备好的语句,这是工作代码:
You just need to use prepared statements, here is working code:
DELIMITER //
drop procedure if exists create_db //
create procedure create_db(name TEXT)
BEGIN
DECLARE temp TEXT;
DECLARE user TEXT;
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = name INTO temp;
if temp = name then
SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'This database already exist';
else
SELECT USER() INTO user;
SET @s = CONCAT('CREATE DATABASE ', name);
PREPARE stmt_create FROM @s;
EXECUTE stmt_create;
DEALLOCATE PREPARE stmt_create;
SET @s = CONCAT('GRANT ALL PRIVILEGES ON ', name, '.* TO ', user, ' WITH GRANT OPTION');
PREPARE stmt_grant FROM @s;
EXECUTE stmt_grant;
DEALLOCATE PREPARE stmt_grant;
END IF;
END //
DELIMITER ;
这篇关于如何在创建数据库语句中使用变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文