decapo decapo - 2 months ago 20
MySQL Question

Pass parameters to sql script

I have a MySQL script file named

query1.sql
which contains:

select * FROM $(tblName) LIMIT 10;


I am in MySQL console, how do I pass the parameter to the script? This does not forward the variable:

mysql> \. query1.sql -v tblName=Users

MvG MvG
Answer

You can use user variables to achieve the behaviour you describe. As you use the variable as a schema identifier, not a data value, you'll have to use a prepared statement so you can compose the query dynamically.

query1.sql:

SET @query = CONCAT('Select * FROM ', @tblName, ' LIMIT 10');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Invoked as

mysql> SET @tblName = 'Users'; \. query1.sql