Store mysql result in a bash array variable

Question

I am trying to store MySQL result into a global bash array variable but I don't know how to do it.

Should I save the MySQL command result in a file and read the file line by line in my for loop for my other treatment?

Example:

user password
Pierre aaa
Paul bbb

Command:

$results = $( mysql –uroot –ppwd –se  « SELECT * from users );

I want that results contains the two rows.

Answer 1

Mapfile for containing whole table into one bash variable

You could try this:

mapfile result < <(mysql –uroot –ppwd –se  "SELECT * from users;")

Than

echo ${result[0]%$'\t'*}
echo ${result[0]#*$'\t'}

or

for row in "${result[@]}";do
    echo Name:  ${row%$'\t'*}  pass: ${row#*$'\t'}
done

Nota This will work fine while there is only 2 fields by row. More is possible but become tricky

Read for reading table row by row

while IFS=$'\t' read name pass ;do
    echo name:$name pass:$pass
  done  < <(mysql -uroot –ppwd –se  "SELECT * from users;")

Read and loop to hold whole table into many variables:

i=0
while IFS=$'\t' read name[i] pass[i++];do
    :;done  < <(mysql -uroot –ppwd –se  "SELECT * from users;")

echo ${name[0]} ${pass[0]}
echo ${name[1]} ${pass[1]}

New (feb 2018) shell connector

There is a little tool or on my own site: (shellConnector.sh you could use:

Some preparation:

cd /tmp/
wget -q http://f-hauri.ch/vrac/shell_connector.sh
. shell_connector.sh
newSqlConnector /usr/bin/mysql '–uroot –pspaced\ password'

Note: if your password do contain spaces, you have to escaped them. Don't try to enclose with quotes nor double-quotes!

Add -f to tell mysql to continue after SQL errors.

Creating a temporary table for demo:

Once newSqlConnector run, they create three file descriptors: $SQLIN $SQLOUT and $SQLERR:

declare -p ${!SQL*}

declare -- SQLERR="10"
declare -- SQLIN="60"
declare -- SQLOUT="63"

Don't matter...

cat >&$SQLIN <<eof
CREATE TEMPORARY TABLE users (
  id bigint(20) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30), date DATE);
  INSERT INTO users VALUES (1,'alice','2015-06-09 22:15:01'),
       (2,'bob','2016-08-10 04:13:21'),(3,'charlie','2017-10-21 16:12:11');
eof

Then playing with your temporary table:

Ok, then now:

mySqlReq myarray "SELECT * from users;"
printf "%s\n" "${myarray[@]}"

1   alice   2015-06-09
2   bob     2016-08-10
3   charlie 2017-10-21

declare -p ${!myarray*}

declare -a myarray=([0]=$'1\talice\t2015-06-09' [1]=$'2\tbob\t2016-08-10' [2]=$'3\tcharlie\t2017-10-21')
declare -a myarray_e=()
declare -a myarray_h=([0]="id" [1]="name" [2]="date")

This tool are in early step of built... You have to manually clear your variable before re-using them:

unset myarray
mySqlReq myarray "SELECT name from users where id=2;"
echo $myarray

bob

declare -p ${!myarray*}

declare -a myarray=([0]="bob")
declare -a myarray_e=()
declare -a myarray_h=([0]="name")

unset myarray
mySqlReq myarray "SELECT name from users where wrongCol=42;"

[1]+  Exit 1  coproc COPROC stdbuf -o0 $command "${args[@]}" 2>&$SQLERR

From there, the mysql client die. You could add -f flag to mysql command to avoid this. Anyway:

declare -p ${!myarray*}

declare -a myarray=()
declare -a myarray_e=([0]="ERROR 1054 (42S22) at line 6: Unknown column 'wrongCol' in 'where clause'")
declare -a myarray_h=()