Is there any way in MySQL to put the name of the database into a variable? For example, when I have a database called 'db1', can I do something like this:
set @db= 'db1';
select * from @db.mytable;
EDIT: There is another example of what I want to do:
set @dbfrom= 'db1';
set @dbto= 'db2';
insert into @dbto.mytable (col1,col2,col3) select col2,col1,col3 from @dbfrom.mytable;
With considerable effort, yes.
SET @db = 'db1';
SET @q = CONCAT('SELECT * FROM ', @db, '.mycol');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Using Chaos' 'Prepare Statement' solution I managed to create a Stored Procedure which uses a variable database name.
Works like a charm for migrating data from one database to another with a Stored Procedure. This way the code isn't pinned to a single database.
DELIMITER $$
DROP PROCEDURE IF EXISTS `SampleProcedure` $$
CREATE PROCEDURE `SampleProcedure`(IN HubDatabaseName VARCHAR(255))
BEGIN
SET @db = HubDatabaseName;
SET @q = CONCAT('
/* Import data from Hub database to local database */
INSERT INTO `table_name_in_local_database`
SELECT
*
FROM
', @db ,'.`tablename`
');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
If you have PHP installed you could use this script to replace the mysql variables to there actual value:
<?php
$sqlFile = 'migration.sql';
$fromDb = 'db1';
$toDb = 'db2';
echo str_replace(['@fromDb', '@toDb'], [$fromDb, $toDb], file_get_contents($sqlFile));
