Creating and then using a database with a declared variable name

Question

I'm trying to create a database named 'uppg' and then use that database once it's created. The first statement executes as it should, but nothing happens when I try to use the database with the variable. I have tried multiple variations but I don't know why it's not working.

SET @dbname = 'uppg';

SET @q1 = CONCAT(
'CREATE DATABASE `', @dbname, '`');
PREPARE stmt1 FROM @q1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET @q2 = CONCAT(
'USE ', @dbname,
);

PREPARE stmt2 FROM @q2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

Akina · Accepted Answer · 2022-10-11 12:51:44Z

2

There exists complete list of statements allowed in PREPARE: SQL Syntax Permitted in Prepared Statements.

USE is not mentioned in it. So you cannot alter current database using dynamic SQL.

The simplest solution: use complete tables names (including database name) in your queries. In this case the current DB can be any (but not none) - this won't effect your queries.

answered Oct 11, 2022 at 12:51

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Karwin Over a year ago

Cf. dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html

Collectives™ on Stack Overflow

Creating and then using a database with a declared variable name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related