CODEWITHSUNDEEP
mysqlvariablesscripting
Tom Burke
Tom Burke

Reputation: 253

How do I pass a variable to a mysql script?

I know that with mysql you can write SQL statements into a .sql file and run the file from the mysql command line like this:

mysql> source script.sql

How do I pass a variable to the script? For example, if I want to run a script that retrieves all the employees in a department, I want to be able to pass in the number of the department as a variable.

I am not trying to run queries through a shell script. There are simple queries I run from the mysql command line. I'm tired of retyping them all the time, and writing a shell script for them would be overkill.

Upvotes: 25

Views: 30601

Answers (2)

Yordan Georgiev
Yordan Georgiev

Reputation: 5420

    #!/bin/bash

    #verify the passed params
    echo 1 cmd arg : $1
    echo 2 cmd arg : $2

    export db=$1
    export tbl=$2

    #set the params ... Note the quotes ( needed for non-numeric values )
    mysql -uroot -pMySecretPaassword \
    -e "set @db='${db}';set @tbl='${tbl}';source run.sql ;" ;

    #usage: bash run.sh my_db my_table
    #
    #eof file: run.sh

    --file:run.sql

    SET @query = CONCAT('Select * FROM ', @db , '.' , @tbl ) ;
    SELECT 'RUNNING THE FOLLOWING query : ' , @query ;
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    --eof file: run.sql

you can re-use the whole concept from from the following project

Upvotes: 25

Brad Choate
Brad Choate

Reputation: 600

Like this:

set @department := 'Engineering';

Then, reference @department wherever you need to in script.sql:

update employee set salary = salary + 10000 where department = @department;

Upvotes: 23

Related Questions