How can I store the output of a mysql command into variables using the shell?

Question

I am using this command:

mysql -u user -ppassword database -e "select distinct entityName,entitySource from AccessControl"

The output is like this:

+-----------------------+--------------+
| entityName            | entitySource |
+-----------------------+--------------+
| low                   | Native       |
| high                  | Native       |
| All Groups            | AD           |
| Help Ser vices Group  | AD           |
| DEFAULT_USER_GROUP    | Native       |
| SYSTEM                | Native       |
| DEFAULT_BA_USER_GROUP | Native       |
| soUsersGrp            | Native       |
+-----------------------+--------------+

My question is: how can I dynamically create an array of variables to store the values entityName and entitySource? What I need to use is use every value of entityName and entitySource to update another table.

Earlier I was trying to store the output in a file and access each line using awk, but that doesn't help because one line may contain multiple words.

Why not use some other script language like ruby/python/php? It will be much simple. — xdazz
– xdazz, Commented Jul 26, 2012 at 11:32
I was thinking the same thing. Setting an environment variable with PHP is stupid simple: php.net/manual/en/function.putenv.php. Just write as small PHP script and execute it from the shell. — Brian
– Brian, Commented Jul 26, 2012 at 11:35
yeah i know, it would have been simpler in C...but i have to do that in this only.. — jayant
– jayant, Commented Jul 26, 2012 at 11:35

jane arc · Accepted Answer · 2013-10-24 00:13:48Z

Sure, this can be done. I'd like to second the idea that piping mysql to mysql in the shell is awkward, but I understand why it might need to be done (such as when piping mysql to psql or whatever).

mysql -qrsNB -u user -p password database \
    -e "select distinct entityName,entitySource from AccessControl" | \ 
        while read record; do
            NAME="`echo $record|cut -d' ' -f 1`"       # that's a tab delimiter
            SOURCE="`echo $record|cut -d'   ' -f 2`"   # also a tab delimiter 
            # your command with $NAME and $SOURCE goes here ... 
            COMMAND="select trousers from namesforpants where entityName='${NAME}'" # ...
            echo $COMMAND | mysql # flags ...
        done

the -rs flags trim your output down so that you don't have to grok that table thing it gives you, -q asks that the result not be buffered, -B asks for batch mode, and -N asks to not have column names.

What you do with those variables is up to you; probably I would compose statements in that loop and feed those to your subsequent process rather than worry about interpolation and quotes as you have mentioned some of your data has spaces in it. Or you can write/append to a file and then feed that to your subsequent process.

As usual, the manual is your friend. I'll be your friend, too, but the manpage is where the answers are to this stuff. :-)

RNZ · Accepted Answer · 2012-07-26 11:48:18Z

0

#!/usr/bin/env bash
mysql -u user -ppassword database -e "select distinct entityName,entitySource from AccessControl" | while read name source; do
    echo "entityName: $name, entitySource: $source"
done

answered Jul 26, 2012 at 11:48

RNZ

215 bronze badges

2 Comments

jayant Over a year ago

friend what gave was helpful but it doesnt serve the purpose...because in case where "entityName is All Groups", the variables doesnt store corrct values...output is.... entityName: All, entitySource: Groups

RNZ Over a year ago

use IFS: while IFS=$'\t' read name source; do ...

kleopatra · Accepted Answer · 2013-12-20 11:02:13Z

0

Please check it, I fixed it through `exec`.

[wcuser@localhost]$ temp=`exec mysql -h10.10.8.36 --port=3306 -uwcuser -pwcuser@123 paycentral -e "select endVersion from script_execution_detail where releaseNo='Release1.0' and versionPrefix='PS'"|tail -1`

[wcuser@localhost]$ echo $temp

19

edited Dec 20, 2013 at 11:02

kleopatra

51.6k28 gold badges103 silver badges220 bronze badges

answered Dec 20, 2013 at 10:41

Prabhat Kumar Kashyap

113 bronze badges

Collectives™ on Stack Overflow

How can I store the output of a mysql command into variables using the shell?

3 Answers 3

Comments

2 Comments

Please check it, I fixed it through `exec`.

19

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Please check it, I fixed it through exec.

19

Comments

Your Answer

Sign up or log in

Post as a guest

Please check it, I fixed it through `exec`.