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
-
Following link might help : databobjr.blogspot.com/2011/07/…Anand Shah– Anand Shah2012-12-31 06:40:01 +00:00Commented Dec 31, 2012 at 6:40
Add a comment
|
6 Answers
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
Comments
The command line call from vidyadhar worked for me, but only with a small modification :
mysql -u root -p -e"set @temp=1; `cat /home/mysql/Desktop/a.sql`"
Comments
This may work for you
mysql -u root -p -e"set @temp=1;" < /home/mysql/Desktop/a.sql
and
mysql> set @temp=some_value;
mysql> source file.sql
this almost similar to your problem just try it
3 Comments
silverdr
Here the first one does not work. The second one does.
David Wesby
@silverdr, what problem did you have with the first one?
silverdr
@DavidWesby - I am sorry but it was five years ago… I honestly don't remember
For bash scripting:
tblName=$1
searchFor="%something%"
echo "select * FROM $tblName LIMIT 10;
select * From $tblName where somecolumn like '$searchFor';
" | mysql
For powershell:
$tblName="MyTable"
$searchFor="%something%"
"select * FROM $tblName LIMIT 10;
select * From $tblName where somecolumn like '$searchFor';
" | mysql
Comments
The answer from @user4150422 above almost worked from me, but cat gave me permission issues so I had to use a slight alternative. Weirdly source command didn't work in place of cat either, but \. did
mysql -u root -p -e"set @temp=1; `\. /home/mysql/Desktop/a.sql`"
Comments
The advanced solution for use bash variables via mysql variables like @tblName in order to INSERT values into specific table:
The core file insert.preps included prepared statements procedural (SET > PREPARE > EXECUTE > DEALLOCATE):
SET @query = CONCAT("INSERT INTO ", @tblName, " (id, time, file, status) VALUES (?, ?, ?, ?)");
PREPARE stmt FROM @query;
SET @id = '0';
EXECUTE stmt USING @id, @time, @file, @status;
DEALLOCATE PREPARE stmt;
*SET @id = '0'; in the file predefined because id is usually is INT AUTO_INCREMENT PRIMARY KEY. It will auto-assign proper id for all lines.
Invoking it in the shell or in bash script with using variables:
mysql --defaults-extra-file=<(echo $'[client]\npassword='"$db_password") --user=$db_user $db_name -e "
SET @tblName = '$table';
SET @time = '$timeStamp';
SET @file = '$file';
SET @status = '$status';
`cat /path/to/insert.preps`"
