How do I select a schema based on a variable? How do I select a schema based on a variable? sql sql

How do I select a schema based on a variable?

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

This article goes over both of these in excellent detail:

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;

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.

I would suggest you write a stored procedure:

DELIMITER $$CREATE PROCEDURE (IN DBname varchar(255)                , IN AKey varchar(255)                , IN AValue varchar(255))BEGIN  DECLARE query VARCHAR(1000);  -- First check the DBName against a list of allowed DBnames,   -- to prevent SQL-injection with dynamic tablenames.  DECLARE NameAllowed BOOLEAN;  SELECT 1 INTO NameAllowed WHERE DBName IN ('validDB1','validDB2');  IF (NameAllowed = 1) THEN BEGIN  -- DBName is in the whitelist, it's safe to continue.    SET query = CONCAT('INSERT INTO '                      ,DBName                      ,'.MetaData (`key`,`value`) values (?,?));    -- note the use of parameter placeholders, to prevent SQL-injection.    PREPARE stmt FROM query;    EXECUTE stmt USING Akey, AValue;    DEALLOCATE PREPARE stmt; -- clears the query and its result from the cache.  END; END IF;END $$DELIMITER ;