I'm trying to use a user-defined variable to stand-in for a table name so that I can just use mysql and not require any other languages.
This example is way simplistic - the real script uses the table name a LOT and it'd be easier for end-users to be able to just change the variable rather than search/replace.
SET @tablename = 'mytable';
SELECT * FROM @tablename;
This won't work, but shows the end result I need.
Use prepared statements:
SET @tablename = 'mytable';
SET @query = CONCAT('SELECT * FROM ', @mytable);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
If your actual query is far more complicated, you could wrap the building of the query in some stored function:
DELIMITER //
CREATE FUNCTION GetQuery(tableName VARCHAR(50)) RETURNS VARCHAR(100)
BEGIN
DECLARE finalQuery VARCHAR(100);
SET finalQuery = CONCAT('SELECT * FROM ', tableName);
-- do something fancier with finalQuery here
RETURN finalQuery;
END //
DELIMITER ;
SET @query = GetQuery('mytable');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Even better: do everything in a stored procedure:
DELIMITER //
CREATE PROCEDURE QueryTable(tableName VARCHAR(50))
BEGIN
SET @finalQuery = CONCAT('SELECT * FROM ', tableName);
PREPARE stmt FROM @finalQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @finalQuery = NULL;
END //
DELIMITER ;
CALL QueryTable('mytable');
information_schemaas the table name? This would expose almost all your database information.