Store MySQL query output into a shell variable

Multiline and multicolumn outputs handling

Multiline outputs

When your MySQL query returns multiple lines, read and iterate:

IFS=$'\n' read -r -d '' -a queryResults <<< "$(mysql -uuser -ppassword -Ddbname -se"SELECT column FROM table WHERE condition;")"
for result in "${queryResults[@]}"; do
    echo "$result"
    # This way we ensure not a line is left behind
done


Above, we're using read within a while loop, buffering each line of the output into an array.

Multicolumn outputs

For multicolumn outputs, handle each column individually:

while read -r col1 col2 col3; do
    echo "Column 1: $col1 - Column 2: $col2 - Column 3: $col3"
    # Who said we couldn't handle more than one column at once?
done < <(mysql -uuser -ppassword -D dbname -se"SELECT col1, col2, col3 FROM table WHERE condition;")


While looping, read with -r takes care of the multicolumn reading.

Data security

Sensitive data needs to be handled appropriately:

read -sp "Enter password:" mysql_password
# Entered password is hidden like a ninja
queryResult=$(mysql -u"user" -p"$mysql_password" -Ddbname -se"SELECT column FROM table WHERE condition;")
unset mysql_password
# Making sure no traces are left behind


Here, for security, the entered password is hidden with read -sp and removed once used with unset.

Advanced Cases

Selecting Specific Columns

Need to dissect your result, maybe grab a certain column?

queryOutput="$(mysql -uuser -ppassword -Ddbname -se"SELECT col1, col2, col3 FROM table WHERE condition;")"
desiredColumn="$(echo "$queryOutput" | cut -f2)"
echo $desiredColumn
# Voila! We've grabbed the exact piece we wanted.


Using cut command, you can slice and dice your outputs anyway you want.

Implementing Error Handling

Life's not all rainbows. Errors happen, and they should be handled:

if ! output=$(mysql -uuser -ppassword -Ddbname -se"SELECT column FROM table WHERE condition;"); then
    echo "An error occurred: $output"
    exit 1
    # Oops, we've hit a bump. But we're prepared!
fi


When things get complicated

For complex outputs, other languages like Python or Perl might make your life easier:

import mysql.connector
# Python, the caveman's Swiss knife

cnx = mysql.connector.connect(user='user', password='password', database='dbname')
cursor = cnx.cursor()
cursor.execute("SELECT column FROM table WHERE condition")

for (column,) in cursor:
    print(column)
    
cursor.close()
cnx.close()
# Neatly wrapped, 'cause nobody likes messy code!


Python's mysql.connector allows you to juggle with complex datasets.