Select mysql query with bash

Question

How to select mysql query with bash so each column will be in a separate array value?

I've tried the following command but it only works if the content is one word. for example: id= 11, text=hello, important=1

if I've an article for instance in text. the code will not work properly. I guess I can use cut -f -d but if "text" contains special characters it wont work either.

while read -ra line; do
    id=$(echo "${line[1]}")
    text=$(echo "${line[2]}")
    important=$(echo "${line[3]}")

    echo "id: $id"
    echo "text: $text"
    echo "important: $important"

done < <(mysql -e "${selectQ}" -u${user} -p${password} ${database} -h ${host})

Community · Accepted Answer · 2017-05-23 11:58:31Z

5

Bash by default splits strings at any whitespace character. First you need a unique column identifier for your output, you can use mysql --batch to get tab-separated csv output.

From the MySQL man page:

--batch, -B

Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option

You want the result to be escaped, so don't use --raw, otherwise a tab character in your result data will break the loop again.

To skip the first row (column names) you can use the option --skip-column-names in addition

Now you can walk through each line and split it by tab character. You can force bash to split by tab only by overriding the IFS variable (Internal Field Separator) temporarily.

Example

# myread prevents collapsing of empty fields
myread() {
    local input
    IFS= read -r input || return $?
    while (( $# > 1 )); do
        IFS= read -r "$1" <<< "${input%%[$IFS]*}"
        input="${input#*[$IFS]}"
        shift
    done
    IFS= read -r "$1" <<< "$input"
 }


# loop though the result rows
while IFS=$'\t' myread id name surname url created; do
     echo "id: ${id}";
     echo "name: ${name}";
     echo "surname: ${surname}"; 
     echo "url: ${url}";
     echo "created: ${created}";
done < <(mysql --batch --skip-column-headers -e "SELECT id, name, surname, url, created FROM users")

myread function all credits to this answer by Stefan Kriwanek

Attention: You need to be very careful with quotes and variable delimiters. If you just echo $row[0] without the curly brackets, you will get the wrong result

EDIT You still have a problem , when a column returns empty string because the internal field separator matches any amount of the defined char:

row1\t\trow3 will create an array [row1,row3] instead of [row1,,row3] I found a very nice approach to fix this, updated the example above. Also read can directly seperate the input stream into variables.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jan 14, 2014 at 8:56

Michel Feldheim

18.3k5 gold badges64 silver badges77 bronze badges

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

2 Comments

Michel Feldheim Over a year ago

it's not as simple as I thought it would be - hold on for a actually working solution

Michel Feldheim Over a year ago

ok, sorry for the confusion, example is working now. Make sure you use the curly brackets, that's important

Collectives™ on Stack Overflow

Select mysql query with bash

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related