How do I capture a MySQL result set in a bash array?

Question

I'm using bash shell on CentOS 7. I want to run a MySQL query from a shell script and iterate over each row of results. If there were 4 rows returned, I thought I could capture the four rows in an array like so:

query="select p.id, p.ebook_id, es.id FROM ...";
echo "$query" > /tmp/query.sql
mysql -u user --password=pass db_id < /tmp/query.sql > /tmp/query.csv

linesIN=`cat /tmp/query.csv | sed 's/\t/,/g'`
arraylength=${#linesIN[@]}
echo $arraylength

However, $arraylength always outputs 1 even though I can see multiple result rows returned. How can I modify the above to correctly create an array of results where each element in the array represents one row from the result set?

Solution 1

With the --batch option, mysql should output the result one record on a line, and columns separated by tabs. You can read the lines to an array with Bash's mapfile and process substitution, or command substitution and array assignment:

mapfile results  < <( mysql --batch ... < query.sql )

or

set -f        # disable globbing
IFS=$'\n'     # set field separator to NL (only)
results=( $(mysql --batch ... ) )

(Note that IFS stays modified and globbing disabled after this.)

Then, if you want to split the columns of a row to some variables:

IFS=$'\t' read -r col1 col2 col2 ... <<< "${results[0]}"

---

Your assignment

linesIN=`cat /tmp/query.csv | sed 's/\t/,/g'`

is not an array assignment (it's missing the parenthesis). It just assigns the output of the command substitution to a regular string variable. (Any newlines will be embedded there, but it'll still be a single string.) ${#linesIN[@]} still works since in Bash/ksh single-element arrays and scalar variables act the same.

Solution 2

Another way to do it would be to pipe the output of the command to a while loop. Note you want to include the -N or the results include the column name.

#!/bin/bash
#Script to read output from a mysql command line by line 

mysql -uroot -p example -N -e "select column from table" | while IFS= read -r loop
do
    echo "$loop"
done 

And if you just want a count, you'd do a select count(columnName) and print out the results.

Solution 3

If you want the number of lines in a file, which will be equal to the number of rows returned from query, just use wc to count number of lines in the file

  arraylength=$( wc -l < /tmp/query.csv)
  echo $arraylength

Comments

[Pankaj Goyal]

Is there a specific reason you're not using --batch for your mysql invocation? That's what it's there for.

[Alexander]

My advice is to just not use bash. Use a proper scripting language.

[dave_thompson_085]

Using redirection wc -l </tmp/query.csv avoids the need to remove the filename

[Arushix]

Thanks for the suggestion, i updated my answer

[Ken Ingram]

If one wishes to use the results elsewhere, they are not accessible outside the while.

[Ken Ingram]

So there is no way to do this with 2+ lines returned from MySQL?

[Dims]

What is in results?

[ilkkachu]

@Dims, with mapfile results < <(foo), whatever the command foo printed, split to one line per array element. The second snippet would do pretty much the same.