How do I select a schema based on a variable?

Question

Consider:

SET @PREFIX='DEV_';

SET @REFRESHDB=CONCAT(@PREFIX,'Refresh');

CREATE TABLE @REFRESHDB.`Metadata`
(
    `Key` VARCHAR(30) NOT NULL,
    `Value` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`Key`)
) ENGINE = InnoDB;

INSERT INTO @REFRESDB.`Metadata` (`Key`, `Value`) VALUES ("Version", "0");

This does not seem to be valid: mysql comes back with:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@REFRESHDB.`Metadata`

As far as I can tell I've done things correctly according to the documentation. Yet MySQL says it's not allowed. Is this some limitation of MySQL (not allowing use of variables as identifiers) or something else?

Answer 1

You will have to use prepare statement/dynamic sql to do this.

This article goes over both of these in excellent detail:

http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html

Try this:

SET @PREFIX='DEV_';

SET @REFRESHDB=CONCAT(@PREFIX,'Refresh');

SET @st = CONCAT('CREATE TABLE ', @REFRESHDB,'.`Metadata`
(
    `Key` VARCHAR(30) NOT NULL,
    `Value` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`Key`)
) ENGINE = InnoDB');

PREPARE tStmt FROM @s;
EXECUTE tStmt;


SET @s = CONCAT('INSERT INTO ', @PREFIX, '.`Metadata` (`Key`, `Value`) VALUES ("Version", "0")');

PREPARE stmt FROM @s;
EXECUTE stmt;

Answer 2

Documentation states:

"User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value"

You are trying to use the variable as an object. This is not supported.