CODEWITHSUNDEEP
mysqlbash
Orlo
Orlo

Reputation: 828

Select mysql query with bash

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})

Upvotes: 3

Views: 4607

Answers (1)

Michel Feldheim
Michel Feldheim

Reputation: 18250

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.

Upvotes: 5

Related Questions