我想将MySQL的结果存储到一个全局bash数组变量中,但不知道如何实现。
我应该将MySQL命令结果保存在文件中,然后逐行在我的for
循环中读取这个文件进行其他处理吗?
示例:
user password
Pierre aaa
Paul bbb
命令:
$results = $( mysql –uroot –ppwd –se « SELECT * from users );
我希望结果包含这两行。
您可以尝试以下方法:
mapfile result < <(mysql –uroot –ppwd –se "SELECT * from users;")
比
echo ${result[0]%$'\t'*}
echo ${result[0]#*$'\t'}
或者
for row in "${result[@]}";do
echo Name: ${row%$'\t'*} pass: ${row#*$'\t'}
done
注意:只有每行有两个字段时,这将正常工作。虽然多个字段也是可能的,但会变得更加棘手。
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]}
有一个小工具(在GitHub上)或者在我的网站上(shellConnector.sh),你可以使用:
cd /tmp/
wget -q http://f-hauri.ch/vrac/shell_connector.sh
. shell_connector.sh
newSqlConnector /usr/bin/mysql '–uroot –ppwd'
好了,现在为演示创建临时表:
echo $SQLIN
3
cat >&3 <<eof
CREATE TEMPORARY TABLE users (
id bigint(20) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30), date DATE)
eof
myMysql myarray ';'
declare -p myarray
bash: declare: myarray: not found
myMysql myarray ';'
会发送;
然后执行内联命令,但是由于mysql不会返回任何内容,变量$myarray
将不存在。cat >&3 <<eof
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
myMysql myarray ';'
declare -p myarray
bash: declare: myarray: not found
好的,现在:
myMysql 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')
这个工具还处于早期构建阶段...在重新使用变量之前,您需要手动清除它们:
unset myarray
myMysql myarray "SELECT name from users where id=2;"
echo $myarray
bob
declare -p myarray
declare -a myarray=([0]="bob")
while IFS=$'\t' read name[i] pass[i];do ((i++));done < <(mysql...)
;-) - F. Hauri - Give Up GitHubIf 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
@F. Hauri的回答看起来非常复杂。
https://stackoverflow.com/a/38052768/470749帮助我意识到我需要使用括号()
将查询结果包装成数组。
#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://dev59.com/KWYs5IYBdhLWcg3wBvRp
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://dev59.com/nHRC5IYBdhLWcg3wOeWB#3232433
i="$(echo -e "${i}" | tr -d '[:space:]')"
TBL_ARR+=("$i")
fi
done
for t in ${TBL_ARR[@]}; do
showTbl $t
done
${TBL_ARR[@]}
包含了查询结果中的所有值。mapfile ...
"和"逐行读取表格的Read命令"**都比你的更简单、更轻量级! - F. Hauri - Give Up GitHub连接器
是一些复杂的东西,他们提供了一种使用可重用的*保持活动连接的方法,并且能够使用CREATE TEMP TABLE
进行示例... - F. Hauri - Give Up GitHubresults=($( mysql –uroot –ppwd –se "SELECT * from users" ))
if [ "$?" -ne 0 ]
then
echo fail
exit
fi