Bash / MySQL queries…

Reduce connection counts to MySQL by using an array to get many values at once (assuming they’re single ‘word’ values)

I “woke up” and realised I often do this wrong …. (too many connections/individual queries on MySQL) when the query is returning simple values (single word like things) for each field.

As a means of example :

So a before query :

MYSQL="mysql -NB my_database"
IDENTIFIER=some_value

# Obviously $VAR1/$VAR2 can contain spaces etc in this example
VAR1=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
VAR2=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
....

And perhaps a nicer way might look more like :

#!/bin/bash
# Chuck output from a query into an array (retrieve many fields at once)
# coalesce( ... ) copes with a field that may be null.

BITS=( $(echo "SELECT coalesce(field1,200) as field1, field2, field3 FROM some_table WHERE some_key = '${IDENTIFIER}'" | $MYSQL ) )

# and then check we had enough values back
if [ ${#BITS[@] -lt 3 ]; then
    echo "handle this error..."
fi
# This will not work if field1/field2/field3 contain spaces.
VAR1=${BITS[0]}
VAR2=${BITS[1]}
VAR3=${BITS[2]}