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'`
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?

  • 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


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 )


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.

  • 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

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.

#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
    echo "$loop"

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

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

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
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.