[SOLVED] How to save multiple columns to bash arrays with 1 select in mysql

Issue

I'm trying to save multiple columns in bash arrays with one query, but I can't figure it out. Now I have 3 SELECTs like this:

read -d \t -ra data1<<< $(mysql -u root -p'password' -D'users_info' -se 'SELECT data1 FROM users_logs')
read -d \t -ra data2<<< $(mysql -u root -p'password' -D'users_info' -se 'SELECT data2 FROM users_logs')
read -d \t -ra data3<<< $(mysql -u root -p'password' -D'users_info' -se 'SELECT data3 FROM users_logs')

and it's working, but I wonder, if this is an optimal way. I think that I could achieve this with one query. I was trying to do something with mysql --batch and mapfile, but couldn't make it.

and output of running:

mysql -u root -p'password' -D'users_info' -se 'SELECT data1, data2, data3 FROM users_logs'

is:

[email protected] 2 2z7bhxb55d3
[email protected] 2 we3cq3micu9cn
[email protected] 1 we3cq1dicu9cn

All those data are varchar(30).

Solution

Assuming the objective is to read a grid of data into multiple arrays (with each column being loaded into a separate array) ...

For dynamically generating/populating arrays using bash namerefs take a look at this answer.

If the number of arrays (and their names) are predefined ...

First some data to simulate the OP's output:

$ cat mysql.out
[email protected] 2 2z7bhxb55d3
[email protected] 2 we3cq3micu9cn
[email protected] 1 we3cq1dicu9cn

NOTE: The following code assumes the column data does not include white space, eg, the above file has 3 columns and not 1 column with embedded spaces; otherwise OP will need to insure the stream of input has a well-defined column delimiter that can be used by the while loop

One bash loop idea:

unset      data1 data2 data3
typeset -a data1 data2 data3

i=0
while read -r col1 col2 col3
do
    (( i++ ))
    data1[${i}]="${col1}"
    data2[${i}]="${col2}"
    data3[${i}]="${col3}"
done < mysql.out                                     # replace this with ...
# done < <(mysql ... SELECT data1,data2,data3 ...)   # this to have mysql results fed directly into 'while' loop

This gives us:

$ typeset -p data1 data2 data3
declare -a data1=([1]="[email protected]" [2]="[email protected]" [3]="[email protected]")
declare -a data2=([1]="2" [2]="2" [3]="1")
declare -a data3=([1]="2z7bhxb55d3" [2]="we3cq3micu9cn" [3]="we3cq1dicu9cn")

---

If you don't mind the indices starting @ 0 ...

unset      data1 data2 data3
typeset -a data1 data2 data3

while read -r col1 col2 col3
do
    data1+=("${col1}")
    data2+=("${col2}")
    data3+=("${col3}")
done < mysql.out

This gives us:

$ typeset -p data1 data2 data3
declare -a data1=([0]="[email protected]" [1]="[email protected]" [2]="[email protected]")
declare -a data2=([0]="2" [1]="2" [2]="1")
declare -a data3=([0]="2z7bhxb55d3" [1]="we3cq3micu9cn" [2]="we3cq1dicu9cn")

Answered By - markp-fuso

Answer Checked By - Marie Seifert (WPSolving Admin)