12

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?

2
  • Is there a specific reason you're not using --batch for your mysql invocation? That's what it's there for.
    – DopeGhoti
    May 29, 2018 at 17:26
  • My advice is to just not use bash. Use a proper scripting language.
    – Alexander
    May 30, 2018 at 4:07

3 Answers 3

14

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.

3
  • So there is no way to do this with 2+ lines returned from MySQL?
    – Ken Ingram
    Jan 26, 2020 at 9:25
  • What is in results?
    – Dims
    Oct 4, 2021 at 15:57
  • 1
    @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.
    – ilkkachu
    Oct 4, 2021 at 17:28
7

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.

1
  • If one wishes to use the results elsewhere, they are not accessible outside the while.
    – Ken Ingram
    Jan 26, 2020 at 9:19
1

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
2
  • 1
    Using redirection wc -l </tmp/query.csv avoids the need to remove the filename May 29, 2018 at 23:26
  • Thanks for the suggestion, i updated my answer
    – Arushix
    May 31, 2018 at 13:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .