CODEWITHSUNDEEP
mysqllinuxbash
Luca Davanzo
Luca Davanzo

Reputation: 21520

Store query in array in bash

My script need to store in a structure the result of a query:

 #!/bin/bash
 user="..."
 psw="..."
 database="..."
 query="select name, mail from t"
 customStructure=$(mysql -u$user -p$psw $database -e "$query";)

I've no idea how store the array of {name, mail} from query result..

I need structure like this:

 array=[ [name1,mail1] , [name2,mail2], ....., [nameN, mailN] ]

Is there a way to do this in bash?

Upvotes: 14

Views: 29955

Answers (2)

AzizSM
AzizSM

Reputation: 6289

Field level record can be accessed via read -a command and IFS is set to the empty string to prevent read from stripping leading and trailing whitespace from the line.

#!/bin/bash
user="..."
psw="..."
database="..."
query="select name, mail from t"

OIFS="$IFS" ; IFS=$'\n' ; oset="$-" ; set -f

while IFS="$OIFS" read -a line 
do 
  echo ${line[0]}   
  echo ${line[1]}   
done < <(mysql  -u${user} -p${psw} ${database} -e "${query}")

Upvotes: 3

Alex V
Alex V

Reputation: 3644

Bash arrays are initialized like so:

myarray=("hi" 1 "2");

To capture the individual portions of output of a command into an array, we must loop through the output, adding it's results to the array. That can be done like so:

for i in `echo "1 2 3 4"`
do
    myarray+=($i)
done

In your example, it looks like you wish to get the output of a MySQL command and store the parts of it's output lines into subarrays. I will show you how to capture lines into arrays, and given that, you should be able to figure out how to put subarrays into that yourself.

while read line
do 
    myarray+=("$line")
done < <(mysql -u${user} -p${psw} ${database} -e "${query}")

It's also worth mentioning that for this kind of MySQL operation, where you don't need output metadata (such as pretty formatting and table names), you can use MySQL's -B option to do 'batch output'.

Upvotes: 17

Related Questions