MySQL command line return values and Bash scripting

As I already told in Suppress column names on MySQL command line output I use the command line interface of MySQL quite often. Especially if you want to do some shell scripting there are not many alternatives for this.

If you want to use the return value of SQL statement and assign it to a variable you usually do something like this:

  sql="select name from table where id = 123"
  name=$(mysql -e "$sql" -sN -u user my_database)
  echo "name: $name"

But what to do if you want to fetch more than one value? Using the same query for different fields is probably not an option, well, not if you’re not really insane of course.

Since Bash offers array as well, it would be great to assign the values to an array directly.

The straight forward approach is probably surrounding the subshell call by parentheses and that’s it like the example illustrates.

  sql="select name from table where id = 123"
  data=($(mysql -e "$sql" -sN -u user my_database))
  echo "name: ${data[0]}, age: ${data[1]}"

However this doesn’t work. But why not?

The problem is that the field delimiter of the output is a tab while the bash expects a space (is there an options to change that? Comments are welcome!). Therefore I used sed to work that out.

  sql="select name, age from table where id = 123"
  data=($(mysql -e "$sql" -sN -u user my_database \\
    | sed 's/\t/ /g'))
  echo "name: ${data[0]}, age: ${data[1]}"

This works fine for me. In case you have another solution please let me know :)

Update: Nathan pointed out the using the $IFS is a lot easier then piping to sed. That’s most probably true and the code changes to

  export IFS="\t"
  sql="select name, age from table where id = 123"
  data=($(mysql -e "$sql" -sN -u user my_database))
  echo "name: ${data[0]}, age: ${data[1]}"