Store mysql query output into a shell variable

Question

I need a variable to hold results retrieved from the database. So far this is basically what I'm trying with no success.

myvariable=$(mysql database -u $user -p $password | SELECT A, B, C FROM table_a)

My understanding of bash commands is not very good as you can see.

are you storing $user and $password values in seperate line? — Murtaza Haji, Jan 07 '22 at 21:52

score 91 · Answer 1 · edited Dec 04 '17 at 16:12

91

A more direct way would be:

myvar=$(mysql mydatabase -u $user -p$password -se "SELECT a, b, c FROM table_a")

edited Dec 04 '17 at 16:12

Charles Okolms

11
1
1
5

answered Oct 28 '09 at 12:40

ennuikiller

46,381
14
112
137

12

I was searching the s param, though you need to supply it twice to suppress the column names to be printed – Dominic Apr 20 '14 at 10:08
1

This shoud be the best answer, it does not return column name! – Ivan Borshchov Jun 05 '15 at 09:51
2

I think the white space between `-p` and the password must be removed. – frb Oct 02 '15 at 11:04
As frb said, you need remove the whitespace between -p and password. – fred Jul 17 '17 at 22:58
This works for me: myvar=$(mysql mydatabase -h -u $user -p$password -se "SELECT a, b, c FROM table_a;") => I added 2 elements: 1) host address and 2) semicolon into SELECT query. – Andrii Apr 04 '21 at 14:59

score 57 · Accepted Answer · edited Jan 28 '14 at 10:33

57

I don't know much about the MySQL command line interface, but assuming you only need help with the bashing, you should try to either swap the commands around like so:

myvariable=$(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p$password)

which echos the string into MySQL. Or, you can be more fancy and use some new bash-features (the here string)

myvariable=$(mysql database -u $user -p$password<<<"SELECT A, B, C FROM table_a")

resulting in the same thing (assuming you're using a recent enough bash version), without involving echo.

Please note that the -p$password is not a typo, but is the way MySQL expects passwords to be entered through the command line (with no space between the option and value).

Note that myvariable will contain everything that MySQL outputs on standard out (usually everything but error messages), including any and all column headers, ASCII-art frames and so on, which may or may not be what you want.

EDIT:
As has been noted, there appears to be a -e parameter to MySQL, I'd go for that one, definitely.

edited Jan 28 '14 at 10:33

oradwell

392
1
12

answered Oct 28 '09 at 12:39

falstro

34,597
9
72
86

1

Thanks, the mysql command also did not like the -p.. I needed to use --password=$password. – imnotneo Oct 28 '09 at 13:29
1

You can also add a configuration file (called .my.cnf) to your home folder which would plug in your options automatically. That would save some confusion in your script – Isaac Sep 14 '11 at 20:34
1

+1 of .my.cnf -especially to get passwords out of scripts. The -N option is useful to trim field names. – ErichBSchulz Jan 19 '14 at 02:49
the -s option in the more voted answer remove the column name and give the value only – Glasnhost Feb 19 '19 at 12:57
1

Trivia: about `-p$password` option. The [POSIX standard](https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap12.html) allows, although discourage, optional option-attributes. Unlike mandatory option-attributes, which are shown separated from their options by characters (like in `-u $user`), optional option-arguments are included within the same argument string as the option (i.e., no spaces). If you find this syntax confusing (I do) you can use the GNU long argument syntax: `--password=$password`. Anyway, I also advise using a my.cnf file as it has been suggested. – David Apr 10 '20 at 16:06

score 19 · Answer 3 · edited Sep 21 '13 at 09:57

You have the pipe the other way around and you need to echo the query, like this:

myvariable=$(echo "SELECT A, B, C FROM table_a" | mysql db -u $user -p $password)

Another alternative is to use only the mysql client, like this

myvariable=$(mysql db -u $user -p $password -se "SELECT A, B, C FROM table_a")

(-s is required to avoid the ASCII-art)

Now, BASH isn't the most appropriate language to handle this type of scenarios, especially handling strings and splitting SQL results and the like. You have to work a lot to get things that would be very, very simple in Perl, Python or PHP.

For example, how will you get each of A, B and C on their own variable? It's certainly doable, but if you do not understand pipes and echo (very basic shell stuff), it will not be an easy task for you to do, so if at all possible I'd use a better suited language.

Thanks for the -s to avoid ASCII. – Matt Komarnicki Oct 16 '20 at 10:10 — Matt Komarnicki, Oct 16 '20 at 10:10

score 18 · Answer 4 · edited Jun 03 '13 at 06:51

18

To read the data line-by-line into a Bash array you can do this:

while read -a row
do
    echo "..${row[0]}..${row[1]}..${row[2]}.."
done < <(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p $password)

Or into individual variables:

while read a b c
do
    echo "..${a}..${b}..${c}.."
done < <(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p $password)

edited Jun 03 '13 at 06:51

TerryA

58,805
11
114
143

answered Oct 28 '09 at 16:06

Dennis Williamson

346,391
90
374
439

`syntax error near unexpected token `<' cut.sh: line 26: `done < <` – thevoipman Jul 20 '15 at 09:28
@thevoipman: It's likely your script isn't using Bash. What does your shebang say? Also, since I can't see your script, I can't see where the error might be. – Dennis Williamson Jul 20 '15 at 11:42
@thevoipman The process substitution seems to be your issue (yes, I know, a year ago, just if anyone comes looking), but it might be possible to do the reverse using a pipeline instead in whatever shell you're using, i.e. `echo ... | mysql ... | while read -a row; do ...; done`. Keep in mind the while body is in a subshell and will not affect the shell around it. – falstro Oct 10 '16 at 18:19

score 10 · Answer 5 · edited Oct 22 '16 at 18:11

10

If you want to use a single value in bash use:

  companyid=$(mysql --user=$Username --password=$Password --database=$Database -s --execute="select CompanyID from mytable limit 1;"|cut -f1)

  echo "$companyid"

edited Oct 22 '16 at 18:11

georgiecasey

21,793
11
65
74

answered Apr 17 '13 at 13:43

Isidoros

101
1
2

3

mysql accepts `--skip-column-names (-N)` rather than `| cut -f1` – jon Aug 01 '21 at 08:31

score 5 · Answer 6 · answered Dec 03 '19 at 13:24

5

Other way:

Your Script:

#!/bin/sh

# Set these variables
MyUSER="root"   # DB_USERNAME
MyPASS="yourPass"   # DB_PASSWORD
MyHOST="yourHost"    # DB_HOSTNAME
DB_NAME="dbName"
CONTAINER="containerName" #if use docker

# Get data
data=$($MyHOST -u $MyUSER -p$MyPASS $DB_NAME -h $CONTAINER -e "SELECT data1,data2 from table_name LIMIT 1;"  -B --skip-column-names)

# Set data
data1=$(echo $data | awk '{print $1}')
data2=$(echo $data | awk '{print $2}')

# Print data
echo $data1 $data2

answered Dec 03 '19 at 13:24

Ivan Mendoza

61
1
1

4

``-N`` can be used for ``--skip-column-names`` – Steven Jun 01 '20 at 15:08
This answer is most useful! – Rohit Parte Feb 05 '21 at 04:33

score 3 · Answer 7 · edited Dec 16 '11 at 16:22

3

myvariable=$(mysql database -u $user -p$password | SELECT A, B, C FROM table_a)

without the blank space after -p. Its trivial, but without don't work.

edited Dec 16 '11 at 16:22

Yuck

49,664
13
105
135

answered Dec 16 '11 at 16:19

puchrojo

31
2

If a password contains special characters you may need to add quotes: -p"YOURCOMPLEXPASSWORD" – webcoder.co.uk Oct 13 '20 at 17:29

score 2 · Answer 8 · edited Nov 27 '18 at 01:07

2

If you have particular database name and a host on which you want the query to be executed then follow below query:

outputofquery=$(mysql -u"$dbusername" -p"$dbpassword" -h"$dbhostname" -e "SELECT A, B, C FROM table_a;" $dbname)

So to run the mysql queries you need to install mysql client on linux

edited Nov 27 '18 at 01:07

codeforester

39,467
16
112
140

answered Feb 27 '18 at 11:08

Prasad Tamgale

325
1
12

score 0 · Answer 9 · answered Aug 17 '12 at 10:55

0

myvariable=$(mysql -u user -p'password' -s -N <<QUERY_INPUT
    use databaseName;
    SELECT fieldName FROM tablename WHERE filedName='fieldValue';
QUERY_INPUT
)
echo "myvariable=$myvariable"

answered Aug 17 '12 at 10:55

Dima

1

1

When providing code that solves the problem, it is best to also give at least a short explanation of how it works so that folks reading won't have to mentally parse it line by line to understand the differences. – Fluffeh Sep 28 '12 at 09:11

score 0 · Answer 10 · answered Feb 03 '18 at 10:23

Another example when the table name or database contains unsupported characters such as a space, or '-'

db='data-base'

db_d=''
db_d+='`'
db_d+=$db
db_d+='`'

myvariable=`mysql --user=$user --password=$password -e "SELECT A, B, C FROM $db_d.table_a;"`

Aritz · Answer 11 · 2020-03-03T10:42:16.323

0

My two cents here:

myvariable=$(mysql database -u $user -p$password -sse "SELECT A, B, C FROM table_a" 2>&1 \
   | grep -v "Using a password")

Removes both the column names and the annoying (but necessary) password warning. Thanks @Dominic Bartl and John for this answer.

edited Mar 03 '20 at 10:42

answered Mar 03 '20 at 10:23

Aritz

30,971
16
136
217

Store mysql query output into a shell variable

11 Answers11

Linked