CODEWITHSUNDEEP
mysqlsql
Scorb
Scorb

Reputation: 1950

mysql CREATE DATABASE using variable

I have written an SQL script to create a database. But I can't seem to figure out how to declare a string variable and reference it afterwards in SQL code. I figure this is a very basic sort of thing, so I must be confused about something here.

All the examples I found use the SET keyword.

SET @dbname = "mydb";
CREATE DATABASE @dbname;

What am I missing here?

Upvotes: 2

Views: 3021

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562891

You can't use variables in place of identifiers (database name, table name, column name, etc). You can only use variables in place of scalar values. To use a variable as an identifier, you have to use PREPARE on a string to make a dynamic SQL statement.

SET @dbname = 'mydb';
SET @query = CONCAT('CREATE DATABASE `', @dbname, '`');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Re your comment:

For initialization scripts, you can format any text you want, usually with a scripting language like python or ruby or even bash, and then run the script as input to the mysql client.

As long as you substitute the name of the database into the CREATE DATABASE statement before sending the statement to the SQL parser, it's fine.

You have to keep in mind that from its earliest conception, SQL was meant to be used in combination from some other programming language. SQL is not as good at being a standalone language as most languages. SQL is a domain-specific language, just for manipulating data and metadata. Other languages are better for general-purpose programming.

Upvotes: 8

Kinzerb
Kinzerb

Reputation: 32

You cannot SET a variable before you DECLARE it, change it to DECLARE and it will work.

EDIT: see code example.

DECLARE @dbname varchar(500) = "mydb"
CREATE DATABASE @dbname

Upvotes: -2

Related Questions