Oct 2 2014
Calling Mysql Iteratively and Passing Parameters from Bash Loop
Below we see an example of calling a mysql script from a file and passing a param.
sql script
So make a file with the following (or whatever else you wish):
SET @i = 1;
use databaseName;
SET @pID=@i+100;
SET @partnerID=concat('partner',@pID);
select @i, @pID , @partnerID;
Now, this will print out the values of i, pID, and partnerID.
Below that you could include insert statements or whatever else you wish.
Now how do we call this from bash and set the @i param?
bash script
user=root
password=mysqlPass
database=db
for i in {300..400..5}
do
sed -i "1 s/.*/set @i=$i;/" sample.sql
mysql --user="$user" --password="$password" < sample.sql
done
So how does this work?
We use sed to replace the first line completely in sample.sql with a new “set @i=” command containing the i param we want.
We then call mysql and feed the rewritten file.
Another way to do this would be to concatenate the sample.sql contents to the set command, and feed them to mysql in memory.
