Search code examples
mysqldatabasedeploymentconnection

MySQL change database with variable value

I am setting a variable, and then I want to use the USE command to change the database. Unfortunately USE doesn't evaluate the variable.

SET @con="testDB";
Query OK, 0 rows affected (0.00 sec)
select @con;
+--------+
| @con   |
+--------+
| testDB |
+--------+
1 row in set (0.00 sec)
use @con;
ERROR 1049 (42000): Unknown database '@con'

So the value of the variable is not evaluated when I try to connect. Any ideas?

Solution

  • I have found my own solution, so I am going to answer my own question, in case anybody is interested.

    @outis you are right, it's not possible to use a variable with the command USE.

    However, due to the fact that I want to create a table in a database specified at runtime, my solution would be to use dynamic SQL:

    set @schema="testDB";
set @front="CREATE TABLE IF NOT EXISTS ";
set @endpart=".`TEST1` (DIM_ID INT(16)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;";
set @stat=concat(@front,@schema,@endpart);
prepare command from @stat;
execute command;

    So basically this solution builds up the statement, prepares it and executes it. @schema parameter can even be past down to the script. This way I dynamically build the create statement.