BASH MySQL output to dynamic variables

Question

I'm using the following BASH mysql statement to get data from my DB.

mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'" | while read A B C D E F G H I J K L M N O P Q R S T U V W X Y Z; do
    echo $A
    echo $B
done

To get all the values for A to Z this seems a long winded way.

How can I do this better and create variables based on the read value name and results. ?

eg: create var A which contains value of $A and do this all the way upto var Z containing the value of $Z

This is only an example, the values won't be called A-Z, but have proper names. Is there any way to create variable based on the true proper name and then associate the value to it ?

eg:

mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'" | while read site_id site_location site_size etc.......; do

Léa Gris · Accepted Answer · 2020-04-02 15:37:28Z

The mysql call needs to be piped-in to the while loop this way:

while read -r site_id site_location site_size _ ; do
  echo "$site_id" "$site_location" "$site_size"
done < <(
  mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'"
)

The < <(command) redirection allows the command to execute in a sub-shell, but feed the main thread's read. So when it read -r var1 var2 ... varn < <(command), variables are valid for the main thread.

Whereas command | read -r var has command to execute in the main thread and read execute in a sub-shell, and var is only valid within the sub-shell.

Getting variable names matching each column name from the table

This script reads the table description and creates a vars array containing the column names.

It reads, creates and prints each shell variable of these names using indirection.

#!/usr/bin/env bash

# Pupulate the vars array with the column names from the table
IFS=$'\n' read -r -d '' -a vars < <(
  mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e 'SHOW COLUMNS FROM site' |
    cut -d ' ' -f1
)

# Output column headers
printf '%s\t' "${vars[@]}"

# read and create variables names matching column names
while read -r "${vars[@]}"; do
  for varname in "${vars[@]}"; do
    # Use ! indirection to print the value for the variable
    printf '%s\t' "${!varname}"
  done
done < <(
  mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'"
)

Collectives™ on Stack Overflow

BASH MySQL output to dynamic variables

1 Answer 1

Getting variable names matching each column name from the table

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Getting variable names matching each column name from the table

Comments

Your Answer

Sign up or log in

Post as a guest

Related