将 mysql 结果存储在 bash 数组变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35274597/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Store mysql result in a bash array variable
提问by gsi
I am trying to store MySQL result into a global bash array variable but I don't know how to do it.
我正在尝试将 MySQL 结果存储到全局 bash 数组变量中，但我不知道该怎么做。
Should I save the MySQL command result in a file and read the file line by line in my forloop for my other treatment?
我应该将 MySQL 命令结果保存在一个文件中并在for循环中逐行读取文件以进行其他处理吗？
Example:
例子：
user password
Pierre aaa
Paul bbb
Command:
命令：
$results = $( mysql –uroot –ppwd –se ? SELECT * from users );
I want that resultscontains the two rows.
我想要results包含两行。
回答by F. Hauri
Mapfile for containing whole table into one bash variable
用于将整个表包含在一个 bash 变量中的映射文件
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
NotaThis will work fine while there is only 2 fields by row. More is possible but become tricky
注意这将正常工作，而每行只有 2 个字段。更多是可能的，但变得棘手
Read for reading table row by row
Read 以逐行读取表格
while IFS=$'\t' read name pass ;do
echo name:$name pass:$pass
done < <(mysql -uroot –ppwd –se "SELECT * from users;")
Read and loop to hold whole table into many variables:
读取并循环以将整个表保存到许多变量中：
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]}
New (feb 2018) shell connector
新（2018 年 2 月）外壳连接器
There is a little tool (on github) or on my own site: (shellConnector.shyou could use:
有一个小工具（在github 上）或在我自己的网站上：（您可以使用shellConnector.sh：
Some preparation:
一些准备：
cd /tmp/
wget -q http://f-hauri.ch/vrac/shell_connector.sh
. shell_connector.sh
newSqlConnector /usr/bin/mysql '–uroot –ppwd'
Following is just for demo, skip until test for quick run
以下仅用于演示，跳过直到测试快速运行
Thats all. Now, creating temporary table for demo:
就这样。现在，为演示创建临时表：
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
The command myMysql myarray ';'will send ;then execute inline command,
but as mysql wont anwer anything, variable $myarraywont exist.
该命令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
Operational Test:
操作测试：
Ok, then now:
好的，那么现在：
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')
This tool are in early step of built... You have to manually clear your variable before re-using them:
该工具处于构建的早期阶段...您必须在重新使用它们之前手动清除变量：
unset myarray
myMysql myarray "SELECT name from users where id=2;"
echo $myarray
bob
declare -p myarray
declare -a myarray=([0]="bob")
回答by silverdrop
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 valueDoing 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
如果你想在你的脚本中获得一个全局变量，你可以简单地为 varname 赋值：
VARNAME=('var' 'name') # no space between the variable name and value这样做后，您将能够在脚本中的任何位置访问 VARNAME 的值。
如果您希望变量在多个脚本之间共享，则必须使用导出：
脚本1.sh：
export VARNAME=('var' 'name') echo ${VARNAME[0]} # will echo 'var'脚本2.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
请注意，导出仅在同一 shell 实例中运行脚本时才有效。如果您希望它跨实例工作，则必须将导出变量代码放在 .bashrc 或 .bash_profile 中的某处
回答by Ryan
The answer from @F. Hauri seems really complicated.
来自@F 的答案。Hauri 看起来真的很复杂。
https://stackoverflow.com/a/38052768/470749helped me realize that I needed to use parentheses ()wrapped around the query result to treat is as an array.
https://stackoverflow.com/a/38052768/470749帮助我意识到我需要使用()环绕查询结果的括号来处理 is as an array。
#You can ignore this function since you'll do something different.
function showTbl {
echo ;
}
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.
这成功地向我展示${TBL_ARR[@]}了查询结果中的所有值。
