Mysql how to find current database, assign it into a string variable

Question

I am trying to do something like this, 1.get the database name (db1) and assign to a string variable 2.the data I need are in db1.out 3.get the data into a table from db1_out

set @dbname = SELECT DATABASE();
set @newdb  = concat(@dbname,'_out');
drop table if exists AAA;
create AAA
Select *
from @newdb.BBB;

Can someone please help me with this?

Thank you

Using a dot as database name is not allowed within MySQL. This is the regex [0-9,a-z,A-Z$_] that validates a valid database name so you need to replace the dot with a underscore. — Raymond Nijland
– Raymond Nijland, Commented Apr 9, 2018 at 14:31
thanks, but set @dbname = SELECT DATABASE(); this does not work, so I am not sure how to assign this variable — Jiaoyan Huang
– Jiaoyan Huang, Commented Apr 9, 2018 at 14:35
Is this running in the MySql console or in an application? I'm asking because this would never work in the MySQL console, since the default behavior is to have the own mysql database selected as you connect to it. It is up to the user that is connecting to MySql to either change the database through the use command or via parameter — Jorge Campos
– Jorge Campos, Commented Apr 9, 2018 at 14:39
I am using MySQL workbench 6.0 on console, and it is not working — Jiaoyan Huang
– Jiaoyan Huang, Commented Apr 9, 2018 at 14:40

Raymond Nijland · Accepted Answer · 2018-04-09 14:37:57Z

3

Your SQL should be something like this.

SET @dbname = DATABASE();
SET @newdb  = concat(@dbname,'_out');

DROP TABLE IF EXISTS AAA;

PREPARE s FROM CONCAT('CREATE AAA SELECT * FROM ', @newdb, '.BBB'); 
EXECUTE s;
DEALLOCATE PREPARE s;

answered Apr 9, 2018 at 14:37

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Mysql how to find current database, assign it into a string variable

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