6

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?

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

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.

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.

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