CODEWITHSUNDEEP
mysqlsqlbashscripting
imnotneo
imnotneo

Reputation: 729

Store mysql query output into a shell variable

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.

Upvotes: 54

Views: 220850

Answers (11)

Aritz
Aritz

Reputation: 31649

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.

Upvotes: 0

Ivan Mendoza
Ivan Mendoza

Reputation: 61

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

Upvotes: 5

Prasad Tamgale
Prasad Tamgale

Reputation: 345

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

Upvotes: 2

snex
snex

Reputation: 1012

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;"`

Upvotes: 0

ennuikiller
ennuikiller

Reputation: 46965

A more direct way would be:

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

Upvotes: 93

Isidoros
Isidoros

Reputation: 101

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"

Upvotes: 10

falstro
falstro

Reputation: 35667

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.

Upvotes: 58

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340271

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.

Upvotes: 20

Dennis Williamson
Dennis Williamson

Reputation: 360105

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)

Upvotes: 18

Dima
Dima

Reputation: 1

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

Upvotes: 0

puchrojo
puchrojo

Reputation: 31

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.

Upvotes: 3

Related Questions