Mysql custom variable in query
I'm trying to use a custom login variable for the table name so that I can just use mysql and not require any other languages.
This example is oversimplified - the real script uses the table name LOT and it would be easier for end users to just change the variable rather than search / replace.
SET @tablename = 'mytable';
SELECT * FROM @tablename;
This won't work, but it does show the end result I want.
+3
source to share
1 answer
Use prepared instructions:
SET @tablename = 'mytable';
SET @query = CONCAT('SELECT * FROM ', @mytable);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
If your actual query is much more complex, you can wrap your query construction 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;
Better yet, 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');
+5
source to share