Database name as variable in MySQL query

Question

I am creating a MySQL dump file, that needs to run with multiple databases. The structure is almost like this :

SET @parent_database = 'db_name';
CREATE OR REPLACE VIEW t_colours AS
   SELECT `key_name`, `value`
   FROM @parent_database. `colours` as COLOURS WHERE 1;

When I run this query next time, my plan is to only change the variable parent_database.

Is this possible?

Everytime now I run this, I receive an error :

#1064 - 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 '@parent_database.`colours` as COLOURS WHERE 1' at line 1

Any way to make this happen?

There are many more views to create.

You can't get data from the database without making connection to the respected database so it won't work; — itzmukeshy7
– itzmukeshy7, Commented Jun 5, 2017 at 9:48

Munjal · Accepted Answer · 2017-06-22 07:35:19Z

1

When you execute the queries the mysql thinks of table names as objects and not as string, and hence this would not work as you would expect.

However, there is a way out and you could use something similar to below snippet.

SET @parent_database = 'db_name';
SET @q = CONCAT('CREATE OR REPLACE VIEW t_colours AS SELECT `key_name`,`value` FROM', @parent_database, '.`colours` as COLOURS WHERE 1;');
PREPARE stmt FROM @q; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;

edited Jun 22, 2017 at 7:35

answered Jun 5, 2017 at 9:34

Munjal

9341 gold badge8 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Database name as variable in MySQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related