1

How can I execute multiple SQL queries in the bash script?

I read these two posts from previous years:

A better way to execute multiple MySQL commands using shell script

How to execute a MySQL command from a shell script?

They brought some clarification, but there is still something I do not understand.

I have multiple queries for deleting information about subject with defined subject_id. Unfortunately I need to run all of them since the table is not in the "cascade" mode.

Is there a way, to create a bash script in which I can use the "user given" variable (by that I mean for example [ read -p 'Subject ID' SUBJECT_ID ]) that will be used inside as the subject_id in each of the queries? Do I still have to adjust everything to this:

mysql -h "server-name" -u root "password" "database-name" < "filename.sql"

or is there a way to just run this script with connection to db from .cnf file inside it?

Improve this question
2

1 Answer 1

Reset to default
2

There are two questions above. One is how to get a bash variable into your SQL script. I would do this:

read -p 'Subject ID' SUBJECT_ID

mysql -e "SET @subject = '${SUBJECT_ID}'; source filename.sql;"

Bash will expand ${SUBJECT_ID} into the string before it uses it as an argument to the mysql -e command. So the MySQL variable is assigned the string value of SUBJECT_ID.

This will be tricky if SUBJECT_ID may contain literal single-quote characters! So I suggest using Bash syntax for string replacement to make each single-quote in that into two single-quotes:

mysql -e "SET @subject = '${SUBJECT_ID//'/''}'; source filename.sql;"

Note you must put a semicolon at the end after the filename.

The second question is about specifying the host, user, and password. I would recommend putting these into an options file:

[client]
host=server-name
user=root
password=xyzzy

Then when you invoke the mysql client:

mysql --defaults-extra-file myoptions.cnf -e '...'

This is a good idea to avoid putting your plaintext password on the command-line.

Read https://dev.mysql.com/doc/refman/8.0/en/option-files.html for more details on option files.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you very much for the answer. Is it wiser to use the Bash syntax even if I am sure that the ID is an integer in all of the cases, just to be safe?
How can you be sure the ID is an integer if it is taken from user input?
I see your point, but it will be done by someone who knows they are always integers. But just in case of a mistake, I will do it properly.
Yes, because someday that person will change jobs and a new person needs to take over. Perhaps that person won't be so good at remembering the required format for the input.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.