How can a bash function return multiple values?

Yes, bash's return can only return numbers, and only integers between 0 and 255.

For a shell that can return anything (lists of things), you can look at es:

$ es -c "fn f {return (a 'b c' d \$*)}; printf '%s\n' <={f x y}"
a
b c
d
x
y

Now, in Korn-like shells like bash, you can always return the data in a pre-agreed variable. And that variable can be in any type supported by the shell.

For bash, that can be scalar, sparse arrays (associative arrays with keys restricted to positive integers) or associative arrays with non-empty keys (neither key nor values can contain NUL characters).

See also zsh with normal arrays and associative arrays without those restrictions.

The equivalent of the f es function above could be done with:

f() {
  reply=(a 'b c' d "$@")
}
f
printf '%s\n' "${reply[@]}"

Now, mysql queries generally return tables, that is two-dimensional arrays. The only shell that I know that has multi-dimensional arrays is ksh93 (like bash it doesn't support NUL characters in its variables though).

ksh also supports compound variables that would be handy to return tables with their headers.

It also supports passing variables by reference.

So, there, you can do:

function f {
  typeset -n var=$1
  var=(
    (foo bar baz)
    (1 2 3)
  }
}
f reply
printf '%s\n' "${reply[0][1]}" "${reply[1][2]}"

Or:

function f {
  typeset -n var=$1
  var=(
    (firstname=John lastname=Smith)
    (firstname=Alice lastname=Doe)
  )
}

f reply
printf '%s\n' "${reply[0].lastname}"

Now, to take the output of mysql and store that in some variables, we need to parse that output which is text with columns of the table separated by TAB characters and rows separated by NL and some encoding for the values to allow them to contain both NL and TAB.

Without --raw, mysql would output a NL as \n, a TAB as \t, a backslash as \\ and a NUL as \0.

ksh93 also has read -C that can read text formatted as a variable definition (not very different from using eval though), so you can do:

function mysql_to_narray {
  awk -F '\t' -v q="'" '
    function quote(s) {
      gsub(/\\n/, "\n", s)
      gsub(/\\t/, "\t", s)
      gsub(/\\\\/, "\\", s)
      gsub(q, q "\\" q q, s)
      return q s q
    }
    BEGIN{print "("}
    {
      print "("
      for (i = 1; i <= NF; i++)
        print " " quote($i)
      print ")"
    }
    END {print ")"}'
}

function query {
  typeset -n var=$1
  typeset db=$2
  shift 2

  typeset -i n=0
  typeset IFS=' '
  typeset credentials=/path/to/file.my # not password on the command line!
  set -o pipefail

  mysql --defaults-extra-file="$credentials" --batch \
        --skip-column-names -e "$*" "$db" |
    mysql_to_narray |
    read -C var
}

To be used as

query myvar mydb 'select * from mytable' || exit
printf '%s\n' "${myvar[0][0]}"...

Or for a compound variable:

function mysql_to_array_of_compounds {
  awk -F '\t' -v q="'" '
    function quote(s) {
      gsub(/\\n/, "\n", s)
      gsub(/\\t/, "\t", s)
      gsub(/\\\\/, "\\", s)
      gsub(q, q "\\" q q, s)
      return q s q
    }
    BEGIN{print "("}
    NR == 1 {
      for (i = 1; i<= NF; i++) header[i] = $i
      next
    }
    {
      print "("
      for (i = 1; i <= NF; i++)
        print " " header[i] "=" quote($i)
      print ")"
    }
    END {print ")"}'
}

function query {
  typeset -n var=$1
  typeset db=$2
  shift 2

  typeset -i n=0
  typeset IFS=' '
  typeset credentials=/path/to/file.my # not password on the command line!
  set -o pipefail

  mysql --defaults-extra-file="$credentials" --batch \
        -e "$*" "$db" |
    mysql_to_array_of_compounds |
    read -C var
}

To be used as:

query myvar mydb 'select "First Name" as firstname, 
                         "Last Name" as lastname from mytable' || exit

printf '%s\n' "${myvar[0].firstname"

Note that the header names (firstname, lastname above) have to be valid shell identifiers.

In bash or zsh or yash (though beware array indices start at 1 in zsh and yash and only zsh can store NUL characters), you could always return one array per column, by having awk generate the code to define them:

query() {
  typeset db="$1"
  shift

  typeset IFS=' '
  typeset credentials=/path/to/file.my # not password on the command line!
  set -o pipefail

  typeset output
  output=$(
    mysql --defaults-extra-file="$credentials" --batch \
          -e "$*" "$db" |
      awk -F '\t' -v q="'" '
        function quote(s) {
          gsub(/\\n/, "\n", s)
          gsub(/\\t/, "\t", s)
          gsub(/\\\\/, "\\", s)
          gsub(q, q "\\" q q, s)
          return q s q
        }
        NR == 1 {
          for (n = 1; n<= NF; n++) column[n] = $n "=("
          next
        }
        {
          for (i = 1; i < n; i++)
            column[i] = column[i] " " quote($i)
        }
        END {
          for (i = 1; i < n; i++)
            print column[i] ") "
        }'
  ) || return
  eval "$output"
}

To be used as:

query mydb 'select "First Name" as firstname, 
                         "Last Name" as lastname from mytable' || exit

printf '%s\n' "${firstname[1]}"

Add a set -o localoptions with zsh or local - with bash4.4+ before the set -o pipefail for the setting of that option to be local to the function like with the ksh93 approach.

Note that in all the above, we're not converting back the \0s to real NULs as bash or ksh93 would choke on them. You may want to do it if using zsh to be able to work with BLOBs but note that the gsub(/\\0/, "\0", s) would not work with all awk implementations.

In any case, here, I'd use more advanced languages than a shell like perl or python to do this kind of thing.