mysqlのselect結果をshell変数に設定したりするサンプル

README

- #!/bin/bash と書いてますが、POSIXモードで動きます
- カラム名を出力しないために -N を付与
- -B で出力形式を変更。フィールド区切りはTAB。レコード区切りは改行
- 複数カラムや複数行の場合に、値の内容にタブや改行あるとつらい
- -X でXML形式に出力することもできる。その場合は jq や xmllint を利用か？
- エラーハンドリングが面倒

database.conf

DATABASE=xxx
USERNAME=xxx
PASSWORD=xxx

example.sh

#!/bin/bash

function get_one_value() {
  local query="select value from foo where id=1"

  local resutl
  result=$(mysql -B --user=${USERNAME} --password=${PASSWORD} --database=${DATABASE} -N -e "${query}")
  if [[ $? -eq 0 ]]; then
    echo "value: [${result}]"
  else
    echo "fail to select from mysql." 1>&2
  fi
}

function get_few_value() {
  local query="select value from foo"

  local resutl
  result=$(mysql -B --user=${USERNAME} --password=${PASSWORD} --database=${DATABASE} -N -e "${query}")
  if [[ $? -eq 0 ]]; then
    for value in ${result}
    do
      echo "value: [${value}]"
    done
  else
    echo "fail to select from mysql." 1>&2
  fi
}

function get_one_columns() {
  local query="select id, value, description from foo where id=3"

  local resutl
  result=$(mysql -B --user=${USERNAME} --password=${PASSWORD} --database=${DATABASE} -N -e "${query}")
  if [[ $? -eq 0 ]]; then
    declare -a columns
    IFS=$'\t' read -ra columns <<< "${result}"
    echo "id         : [${columns[0]}]"
    echo "value      : [${columns[1]}]"
    echo "description: [${columns[2]}]"
  else
    echo "fail to select from mysql." 1>&2
  fi
}

function get_few_columns() {
  local query="select id, value, description from foo"

  local resutl
  result=$(mysql -B --user=${USERNAME} --password=${PASSWORD} --database=${DATABASE} -N -e "${query}")
  if [[ $? -eq 0 ]]; then
    local org_ifs=$IFS
    IFS=$'\n'; for row in ${result}
    do
      declare -a columns
      IFS=$'\t' read -ra columns <<< "${row}"
      echo "id         : [${columns[0]}]"
      echo "value      : [${columns[1]}]"
      echo "description: [${columns[2]}]"
    done
    IFS=$org_ifs
  else
    echo "fail to select from mysql." 1>&2
  fi

}

function get_few_columns_using_tmpfile() {
  local query="select id, value, description from foo"

  local tmpfile=$(mktemp)
  trap "rm -f ${tmpfile}" EXIT

  local resutl
  mysql -B --user=${USERNAME} --password=${PASSWORD} --database=${DATABASE} -N -e "${query}" > ${tmpfile}
  if [[ $? -eq 0 ]]; then
    while IFS=$'\t' read -a row
    do
      echo "id[${row[0]}], value[${row[1]}], description[${row[2]}]"
    done < ${tmpfile}
  else
    echo "fail to select from mysql." 1>&2
  fi

}

function main() {

  get_one_value
  #get_few_value
  #get_one_columns
  #get_few_columns
  #get_few_columns_using_tmpfile
}

. $(dirname $0)/database.conf
main "$@"

mysql info

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.08 sec)

mysql> desc foo
    -> ;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   | PRI | NULL    |       |
| value       | varchar(10) | NO   |     | NULL    |       |
| description | varchar(30) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

mysql> select * from foo;
+----+-------+-------------+
| id | value | description |
+----+-------+-------------+
|  1 | a     | NULL        |
|  2 | b     | NULL        |
|  3 | c     | The C       |
+----+-------+-------------+
3 rows in set (0.00 sec)