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-20 19:36:23Z

11

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, 2018 at 19:36

answered Apr 20, 2017 at 17:18

codeforester

43.8k21 gold badges122 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jimmy P Over a year ago

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?

codeforester Over a year ago

Used CONCAT instead of FIELDS TERMINATED BY - that should work. Looks like the latter works only when output is redirected to a file.

Jimmy P Over a year ago

Wouldn't using CONCAT in this case now cause problems when value contains commas though?

Jimmy P Over a year ago

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?

Lenka Pitonakova Over a year ago

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

stobiewankenobi · Accepted Answer · 2017-04-20 17:30:52Z

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

yg-dba · Accepted Answer · 2024-06-27 18:11:17Z

0

You can change the IFS (Input Field Separator) to only tab instead of the default of newline, space, and tab. You need to reset the IFS back to the default at the end of the script.

This works simply and perfectly:

generated_queries="
SELECT 
CONCAT(a.table_schema,'.',a.table_name) tbl, 
CONCAT('UPDATE ',a.table_schema,'.',a.table_name,' t SET ',
GROUP_CONCAT(CONCAT('t.',column_name, ' = 0'))
) query,
GROUP_CONCAT(column_name) columns
FROM information_schema.columns a
WHERE table_schema = 'db_name'
and column_name IN ('column1','column2')
GROUP BY 1"

OLDIFS=$IFS
IFS=$'\t'
while read table query columns; do
   echo "$query"
done < <( mysql -NB -e "$generated_queries")
IFS=$OLDIFS

answered Jun 27, 2024 at 18:11

yg-dba

3431 silver badge6 bronze badges

Collectives™ on Stack Overflow

Read MySQL result set with multiple columns and spaces

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related