1. Overview
When working with databases, we often need to store the result set of a query for further processing. One way to store the result is using a Bash array, whereby each row of the result set becomes a single element of the array.
In this tutorial, we’ll explore how to save multiple rows of output from a MySQL query into a Bash array.
2. Sample Task
Let’s suppose we have a MySQL database named db that contains a table named items.
We can access the content of this table using a SELECT statement in SQL. In particular, we use the mysql -e command to issue the SQL statement from the Bash command line:
$ mysql --user=sysadmin --password=mypassword -e 'SELECT * FROM db.items;'
+----------+-------+-------+
| STOCK_ID | NAME | PRICE |
+----------+-------+-------+
| AB12CD | pen | 1 |
| EF23GH | paper | 2 |
| IJ45KL | book | 10 |
+----------+-------+-------+
Here, the username and password follow the –user and –password options respectively. The resulting table consists of four rows, namely a header, followed by three rows of data.
Our objective is to store each row of the table as a single element in a Bash array. This enables us to further process each element of the array separately if needed.
By default, the result appears in tabular format. However, to facilitate saving the output into an array, we can print the result in a non-tabular, batch format by using the -B option:
$ mysql --user=sysadmin --password=mypassword -Be 'SELECT * FROM db.items;'
STOCK_ID NAME PRICE
AB12CD pen 1
EF23GH paper 2
IJ45KL book 10
Notably, the column separator in batch mode is the tab character.
Now that the output is in the required format, let’s explore several ways to save the result into a Bash array.
3. Using Command Substitution
We can use command substitution within an array assignment to save the result into an array:
$ set -f
$ OIFS="$IFS"
$ IFS=$'\n'
$ results=( $(mysql --user=sysadmin --password=mypassword -Be 'SELECT * FROM db.items;') )
$ IFS="$OIFS"
$ set +f
Let’s break down the commands we used:
- use set -f to disable globbing
- save the default Internal Field Separator (IFS) to a variable named OIFS so that we can restore it later
- set the value of the IFS variable solely to the newline character
- use command substitution to obtain the output of the SQL statement, and save it in an array named results
- restore the IFS variable to its default value
- use set +f to enable globbing
Since we set the IFS variable to the newline character before the array assignment, each line of the output is stored as an element in the results array. Moreover, we disable globbing so that wildcard characters, such as the asterisk character (*), are interpreted literally, i.e., without leading to filename expansion.
Let’s verify the outcome by printing the elements of the results array:
$ for element in "${results[@]}"; do echo "$element"; done
STOCK_ID NAME PRICE
AB12CD pen 1
EF23GH paper 2
IJ45KL book 10
The output shows four lines in total. We can check the number of elements in the array:
$ echo "${#results[@]}"
4
There are four elements, each corresponding to a row of output.
We can also process each row separately. For example, we can obtain the values of the three columns in the header row:
$ IFS=$'\t' read -r col1 col2 col3 <<< "${results[0]}"
In this case, we first set the IFS variable to the tab character. Then, we use the read command to store the tab-separated values of the header into the col1, col2, and col3 variables. The -r option is often used with read to prevent interpreting the backslash as an escape character.
Notably, the header data represented by ${results[0]} is passed as input to the read command via a here-string.
Let’s check the values of col1, col2, and col3:
$ echo "$col1"
STOCK_ID
$ echo "$col2"
NAME
$ echo "$col3"
PRICE
We succeeded at processing the header row and capturing each column value in a separate variable.
4. Using the mapfile Command
Alternatively, we can use the mapfile command to save the output of an SQL statement into a Bash array:
$ mapfile -t results < <(mysql --user=sysadmin --password=mypassword -Be 'SELECT * FROM db.items;')
The mapfile command automatically reads lines and saves them into an array variable. In this case, the input is provided via a process substitution. Then, the mapfile command saves each line into an array named results. Here, the -t option used with mapfile is for removing the trailing newline character from each line read.
Let’s display the different lines by looping over the array elements and printing them:
$ for element in "${results[@]}"; do echo "$element"; done | column -t
STOCK_ID NAME PRICE
AB12CD pen 1
EF23GH paper 2
IJ45KL book 10
We obtain four lines, as expected. Additionally, we pipe the result to the column -t command to align the columns and display the output as a table.
Importantly, the mapfile command is equivalent to the readarray command. Therefore, we can replace one with the other:
$ readarray -t results < <(mysql --user=sysadmin --password=mypassword -Be 'SELECT * FROM db.items;')
This way, we obtain the same outcome as before.
5. Using the read Command
Another approach for storing the output of an SQL statement into a Bash array is the read command alone:
$ results=()
$ while read -r line; do results+=("$line"); done < <(mysql --user=sysadmin --password=mypassword -Be 'SELECT * FROM db.items;')
In this case, we first initialize the results variable to an empty array. Then, we use the read command within a while loop to process each line of input and append it as an element in the results array. Moreover, we use process substitution to provide the input.
Let’s verify the content of the results array by printing out its elements:
$ for element in "${results[@]}"; do echo "$element"; done | column -t
STOCK_ID NAME PRICE
AB12CD pen 1
EF23GH paper 2
IJ45KL book 10
Notably, we obtain the same outcome as with the other methods.
6. Conclusion
In this article, we explored different ways of storing a MySQL result set in a Bash array.
First, we can use the batch mode when calling SQL statements via the mysql -Be command. Then, we can save the result into an array using command substitution and array assignment. Alternatively, we can use the mapfile or readarray command along with process substitution. Finally, another approach is to use the read command in a while loop to save each row as an element of a Bash array.