How to pass a variable from a shell script to a mysql script

Question

I have a bunch of mysql scripts that use name1 as the database name and populate that database with my data. However, I want to be able to choose the name of the database, instead of always naming it name1 everywhere in my sql scripts.

I already have a wrapper shell script that takes in the path of my data file and now I would like it to also take in the name of the database.

In my shell script, i assign:

DATABASE_NAME=$2

Is there any way at the top of my mysql scripts where I can use $DATABASE_NAME? So in the mysql scripts, it would be:

USE DATABASE_NAME;

instead of what I have right now:

USE name1;

How many databases are you using at one time while you are in a script? If 1, then clean up your scripts once. — Drew
– Drew, Commented Jun 27, 2016 at 18:02
I am only using 1 database, called name1. But I have nearly 10 mysql scripts that reference it. Also, what do you mean by "clean up your scripts once"? — who-went-bankrupt-buying-legos
– who-went-bankrupt-buying-legos, Commented Jun 27, 2016 at 18:08
I just don't see the point of people doing select blah from mydb.table1 ... with mydb. all over the place — Drew
– Drew, Commented Jun 27, 2016 at 18:09

Norbert · Accepted Answer · 2016-06-27 18:15:53Z

1

You can use mysql -e to execute your code:

mysql -e "USE ${DATABASENAME}; Some sql"

That way any replace works.

Optionally you can write temporary sql files or pipeline code into your mysql in which you just replace the ${DATABASENAME} when parsing that file. (The -e option is the most transparent and easiest)

answered Jun 27, 2016 at 18:15

Norbert

6,1043 gold badges19 silver badges40 bronze badges

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

5 Comments

who-went-bankrupt-buying-legos Over a year ago

Ok if I do that, I may have something like mysql -e "USE ${DATABASENAME}; DataLoad.sql". In the DataLoad.sql file, how do I reference the same database name? (for something like SELECT COUNT(*) FROM name1.Load where I want name1 to be replaced by the DATABASENAME)

Barmar Over a year ago

@Jain The USE command sets the default database. So just leave the database name out of your SQL, e.g. SELECT COUNT(*) FROM Load.

Barmar Over a year ago

You can use source filename.sql to load the SQL from a file.

who-went-bankrupt-buying-legos Over a year ago

@Barmar I tried that but for some of my sql scripts, it says that the table doesnt exist if I dont have name1.Load. I agree with you on the logic, but maybe mysql doesnt

who-went-bankrupt-buying-legos Over a year ago

No its always the same one. Not sure why it isnt working

Collectives™ on Stack Overflow

How to pass a variable from a shell script to a mysql script

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related