I am trying to store MySQL result into a global bash array variable but I don't know how to do it.
Should I save the MySQL command result in a file and read the file line by line in my for loop for my other treatment?
Example:
user password
Pierre aaa
Paul bbb
Command:
$results = $( mysql –uroot –ppwd –se « SELECT * from users );
I want that results contains the two rows.
You could try this:
mapfile result < <(mysql –uroot –ppwd –se "SELECT * from users;")
Than
echo ${result[0]%$'\t'*}
echo ${result[0]#*$'\t'}
or
for row in "${result[@]}";do
echo Name: ${row%$'\t'*} pass: ${row#*$'\t'}
done
Nota This will work fine while there is only 2 fields by row. More is possible but become tricky
while IFS=$'\t' read name pass ;do
echo name:$name pass:$pass
done < <(mysql -uroot –ppwd –se "SELECT * from users;")
i=0
while IFS=$'\t' read name[i] pass[i++];do
:;done < <(mysql -uroot –ppwd –se "SELECT * from users;")
echo ${name[0]} ${pass[0]}
echo ${name[1]} ${pass[1]}
There is a little tool on my own site: (shellConnector.sh you could use for creating a persistent connection to mysql server:
cd /tmp/
wget -q http://f-hauri.ch/vrac/shell_connector.sh
. shell_connector.sh
newSqlConnector /usr/bin/mysql '–uroot –pspaced\ password'
Note: if your password do contain spaces, you have to escaped them. Don't try to enclose with quotes nor double-quotes!
Add -f to tell mysql to continue after SQL errors.
Once newSqlConnector run, they create three file descriptors:
$SQLIN $SQLOUT and $SQLERR:
declare -p ${!SQL*}
declare -- SQLERR="10"
declare -- SQLIN="60"
declare -- SQLOUT="63"
From there, you could send SQL commands to $SQLIN fd...
cat >&$SQLIN <<eof
CREATE TEMPORARY TABLE users (
id bigint(20) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30), date DATE);
INSERT INTO users VALUES (1,'alice','2015-06-09 22:15:01'),
(2,'bob','2016-08-10 04:13:21'),(3,'charlie','2017-10-21 16:12:11');
eof
Ok, then now:
mySqlReq myarray "SELECT * from users;"
printf "%s\n" "${myarray[@]}"
1 alice 2015-06-09
2 bob 2016-08-10
3 charlie 2017-10-21
declare -p ${!myarray*}
declare -a myarray=([0]=$'1\talice\t2015-06-09' [1]=$'2\tbob\t2016-08-10' [2]=$'3\tcharlie\t2017-10-21')
declare -a myarray_e=()
declare -a myarray_h=([0]="id" [1]="name" [2]="date")
This tool are in early step of built... You have to manually clear your variable before re-using them:
unset myarray
mySqlReq myarray "SELECT name from users where id=2;"
echo $myarray
bob
declare -p ${!myarray*}
declare -a myarray=([0]="bob")
declare -a myarray_e=()
declare -a myarray_h=([0]="name")
unset myarray
mySqlReq myarray "SELECT name from users where wrongCol=42;"
[1]+ Exit 1 coproc COPROC stdbuf -o0 $command "${args[@]}" 2>&$SQLERR
From there, the mysql client die. You could add -f flag to mysql command to avoid this. Anyway:
declare -p ${!myarray*}
declare -a myarray=()
declare -a myarray_e=([0]="ERROR 1054 (42S22) at line 6: Unknown column 'wrongCol' in 'where clause'")
declare -a myarray_h=()
while IFS=$'\t' read name[i] pass[i];do ((i++));done < <(mysql... if you prefer ;-)If you're looking to get a global variable inside your script you can simply assign a value to a varname:
VARNAME=('var' 'name') # no space between the variable name and value
Doing this you'll be able to access VARNAME's value anywhere in your script after you initialize it.
If you want your variable to be shared between multiple scripts you have to use export:
script1.sh:
export VARNAME=('var' 'name')
echo ${VARNAME[0]} # will echo 'var'
script2.sh
echo ${VARNAME[1]} # will echo 'name', provided that
# script1.sh was executed prior to this one
NOTE that export will work only when running scripts in the same shell instance. If you want it to work cross-instance you would have to put the export variable code somewhere in .bashrc or .bash_profile
The answer from @F. Hauri seems really complicated.
https://stackoverflow.com/a/38052768/470749 helped me realize that I needed to use parentheses () wrapped around the query result to treat is as an array.
#You can ignore this function since you'll do something different.
function showTbl {
echo $1;
}
MOST_TABLES=$(ssh -vvv -t -i ~/.ssh/myKey ${SERVER_USER_AND_IP} "cd /app/ && docker exec laradock_mysql_1 mysql -u ${DB} -p${REMOTE_PW} -e 'SELECT table_name FROM information_schema.tables WHERE table_schema = \"${DB}\" AND table_name NOT LIKE \"pma_%\" AND table_name NOT IN (\"mail_webhooks\");'")
#Do some string replacement to get rid of the query result header and warning. https://stackoverflow.com/questions/13210880/replace-one-substring-for-another-string-in-shell-script
warningToIgnore="mysql\: \[Warning\] Using a password on the command line interface can be insecure\."
MOST_TABLES=${MOST_TABLES/$warningToIgnore/""}
headerToIgnore="table_name"
MOST_TABLES=${MOST_TABLES/$headerToIgnore/""}
#HERE WAS THE LINE THAT I NEEDED TO ADD! Convert the string to array:
MOST_TABLES=($MOST_TABLES)
for i in ${MOST_TABLES[@]}; do
if [[ $i = *[![:space:]]* ]]
then
#Remove whitespace from value https://stackoverflow.com/a/3232433/470749
i="$(echo -e "${i}" | tr -d '[:space:]')"
TBL_ARR+=("$i")
fi
done
for t in ${TBL_ARR[@]}; do
showTbl $t
done
This successfully shows me that ${TBL_ARR[@]} has all the values from the query result.
mapfile ...* and Read for reading table row by row are both simplier and lignther than your!connector is something complicated, they present a way to use re-usable staying alive connection, with the ability to use CREATE TEMP TABLE, for sample...results=($( mysql –uroot –ppwd –se "SELECT * from users" ))
if [ "$?" -ne 0 ]
then
echo fail
exit
fi
