Read MySQL result set with multiple columns and spaces

Question

Pretend I have a MySQL table test that looks like:

+----+---------------------+
| id |     value           |
+----+---------------------+
| 1  | Hello World         |
| 2  | Foo Bar             |
| 3  | Goodbye Cruel World |
+----+---------------------+

And I execute the query SELECT id, value FROM test. How would I assign each column to a variable in Bash using read?

read -a truncates everything after the first space in value:

mysql -D "jimmy" -NBe "SELECT id, value FROM test" | while read -a row;
do
    id="${row[0]}"
    value="${row[1]}"

    echo "$id : $value"
done;

and output looks like:

1 : Hello
2 : Foo
3 : Goodbye

but I need it to look like:

1 : Hello World
2 : Foo Bar
3 : Goodbye Cruel World

I'm aware there are args I could pass to MySQL to format the results in table format, but I need to parse each value in each row. This is just a simplified example of my problem.

codeforester · Accepted Answer · 2018-10-20T19:36:23.960

12

Use individual fields in the read loop instead of the array:

mysql -D "jimmy" -NBe "SELECT id, value FROM test" | while read -r id value;
do
    echo "$id : $value"
done

This will make sure that id will be read into the id field and everything else would be read into the value field - that's how read behaves when input has more fields than the number of variables being read into. If there are more columns to be read, using a delimiter (such as @) that doesn't clash with actual data would help:

mysql -D "jimmy" -NBe "SELECT CONCAT(id, '@', value, '@', column3) FROM test" | while IFS='@' read -r id value column3;
do
    echo "$id : $value : $column3"
done

edited Oct 20 '18 at 19:36

answered Apr 20 '17 at 17:18

codeforester

39,467
16
112
140

I get a syntax error when using `FIELDS TERMINATED BY`: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TERMINATED BY ','' at line 1`. Does this only work when using an outfile? – Jimmy P Apr 20 '17 at 17:28
Used `CONCAT` instead of `FIELDS TERMINATED BY` - that should work. Looks like the latter works only when output is redirected to a file. – codeforester Apr 20 '17 at 17:36
Wouldn't using `CONCAT` in this case now cause problems when `value` contains commas though? – Jimmy P Apr 20 '17 at 17:38
Could you explain why this works? is it because there are only 2 variables and everything after the first delimiter is placed into the second? – Jimmy P Apr 20 '17 at 17:44
also note that value is a keyword in mysql so you have to use escaped ` characters around it like: SELECT CONCAT(id, '@', \\`value\\`, '@', column3) FROM test – Lenka Pitonakova May 21 '19 at 16:51

score 2 · Answer 2 · answered Apr 20 '17 at 17:30

You can do this, also avoid piping a command to a while read loop if possible to avoid creating a subshell.

while read -r line; do
    id=$(echo $line | awk '{print $1}')
    value=$(echo $line | awk '{print $1=""; print $0}'|sed ':a;N;$!ba;s/\n/ /g'| sed 's/^[ \t]*//g')
    echo "ID: $id"
    echo "VALUE: $value"
done< <(mysql -D "jimmy" -NBe "SELECT id, value FROM test")

If you want to store all the id's and values in an array for later use, you can modify it to look like this.

#!/bin/bash

declare -A -g arr

while read -r line; do
    id=$(echo $line | awk '{print $1}')
    value=$(echo $line | awk '{print $1=""; print $0}'|sed ':a;N;$!ba;s/\n/ /g'| sed 's/^[ \t]*//g')

    arr[$id]=$value
done< <(mysql -D "jimmy" -NBe "SELECT id, value FROM test")

for key in "${!arr[@]}"; do
    echo "$key: ${arr[$key]}"
done

Which gives you this output

dumbledore@ansible1a [OPS]:~/tmp/tmp > bash test.sh
1: Hello World
2: Foo Bar
3: Goodbye Cruel World

Read MySQL result set with multiple columns and spaces

2 Answers2