Blog

How To Store MySQL Result To Array From Bash

Tags: , , , , , ,
You are currently viewing How To Store MySQL Result To Array From Bash
How To Store MySQL Result To Array From Bash

Every programming language offers drivers or extensions to query MySQL and fetch the result. In other words to store MySQL result to array. Sometimes you need to do the same using Bash.

Recently I migrated pontikis.net from its custom CMS to WordPress. I plan to post my experience from this migration, which was based on “The command-line interface for WordPress”, aka WP-CLI.

Each migration process uses the logic of the following scripts. This is the main reason for this post.

The MySQL CLI client

MySQL or MariaDB offers a command-line client to query the database. You get the result as the command output. Example:

MariaDB [pnet_demo]> SELECT lastname, firstname FROM customers \                                                 
    ->  WHERE lk_genders_id=1 ORDER BY lastname LIMIT 0,5;
+----------+-----------+
| lastname | firstname |
+----------+-----------+
| Albert   | Sean      |
| Allison  | Trevor    |
| Atkins   | Lawrence  |
| Austin   | Harper    |
| Bailey   | Benjamin  |
+----------+-----------+
5 rows in set (0.001 sec)

The BASH solution

How you can parse the output inside a bash script?

# read data from MySQL
sql="SELECT lastname, firstname FROM customers \
WHERE lk_genders_id=1 ORDER BY lastname LIMIT 0,5";
i=0
while IFS=$'\t' read lastname, firstname ;do
    LASTNAME[$i]=$lastname
    FIRSTNAME[$i]=$firstname
    ((i++))
done  < <(mysql MYDB --default-character-set=utf8 -u USERNAME -pPASSWORD -N -e "$sql")

IFS

IFS will do the trick here. The IFS (internal field separator) is a special shell variable. $IFS defaults to whitespace.

We change IFS to tab \t in order to parse the mysql command output. See Line 5.

Encoding

Take care of the encoding (see line 9). Use --default-character-set=utf8 in mysql command.

Iterate in MySQL result

# iterate in result
for (( e=0; e<$i; e++ ))
do
    echo -e "${LASTNAME[$e]}. ${FIRSTNAME[$e]}"
done

IFS=' ' # reset to default

Here is the output:

Albert Sean
Allison Trevor
Atkins Lawrence
Austin Harper
Bailey Benjamin

Bash is extremely powerful!