Bash 脚本循环通过 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5918436/
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
Bash Script Loop Through MySQL
提问by Brian Leishman
I need a bash script that can retrieve MySQL data from a remote data base. Actually I have that done, but what I need it to do now is loop through the records somehow and pass a variable to another bash file. Here's my MySQL call:
我需要一个可以从远程数据库检索 MySQL 数据的 bash 脚本。实际上我已经完成了，但是我现在需要做的是以某种方式循环遍历记录并将变量传递给另一个 bash 文件。这是我的 MySQL 调用：
mysql -X -u $MyUSER -p$MyPASS -h$MyHOST -D$MyDB -e'SELECT `theme_name`, `guid` FROM `themes` WHERE `theme_purchased`="1" AND `theme_compiled`='0';' > themes.xml
download_themes.sh
It exports the data into an xml file called theme.xml right now, I was just trying to figure out some way to loop through the data. I am trying to avoid PHP and perl and just trying to use bash. Thanks in advance.
它现在将数据导出到一个名为 theme.xml 的 xml 文件中，我只是想找出某种方法来遍历数据。我试图避免使用 PHP 和 perl，而只是尝试使用 bash。提前致谢。
回答by Javier
something like:
就像是：
mysql -e "SELECT `theme_name`, `guid` FROM `themes` WHERE `theme_purchased`='1' AND `theme_compiled`='0'" | while read theme_name guid; do
# use $theme_name and $guid variables
echo "theme: $theme_name, guid: $guid"
done
in short: the mysqlcommand outputs record separated by '\n' and fields separated by '\t' when the output is a pipe. the readcommand reads a line, splits in fields, and puts each on a variable.
简而言之：mysql当输出是管道时，命令输出由 '\n' 分隔的记录和由 '\t' 分隔的字段。该read命令读取一行，在字段中拆分，并将每个字段放在一个变量上。
if your data has spaces in the fields, you get some problems with the default readsplitting. there are some ways around it; but if you're only reading two fields and one of them shouldn't have any spaces (like the guid), then you can put the 'dangerous' field at the end, and readwill put everything 'extra' in the last variable.
如果您的数据在字段中有空格，则默认read拆分会出现一些问题。有一些方法可以解决它；但是如果您只读取两个字段并且其中一个不应有任何空格（如guid），那么您可以将“危险”字段放在最后，read并将所有“额外”字段放在最后一个变量中。
like this:
像这样：
mysql -e "SELECT `guid` `theme_name`, FROM `themes` WHERE `theme_purchased`='1' AND `theme_compiled`='0'" | while read guid theme_name; do
# use $theme_name and $guid variables
echo "theme: $theme_name, guid: $guid"
done
回答by Michael Berkowski
Rather than outputting XML, may I suggest you simply use the SELECT INTO OUTFILEsyntax or mysql --batch --rawto output tab-separated values. You then have much easier access through bash to the rest of the Unix toolchain, like cutand awkto retrieve the fields you need and reuse them with Bash. No other scripting language is necessary and you needn't mess with XML.
我建议您不要输出 XML，而是建议您简单地使用SELECT INTO OUTFILE语法或mysql --batch --raw输出制表符分隔的值。然后，您可以通过庆典到Unix工具的时候更容易访问，就像cut和awk检索所需的字段，并猛砸重用他们。不需要其他脚本语言，您也不必弄乱 XML。
mysql --batch --raw -u $MyUSER -p$MyPASS -h$MyHOST -D$MyDB -e'SELECT `theme_name`, `guid` FROM `themes` WHERE `theme_purchased`="1" AND `theme_compiled`='0';' \
| awk '{print "theme: " " guid: " }'
回答by CSTobey
The accepted answer does not work when spaces are in the output. It is an easy fix (IFS=$'\t' -- Note the $ -- it is weird):
当输出中有空格时，接受的答案不起作用。这是一个简单的解决方法（IFS=$'\t' -- 注意 $ -- 这很奇怪）：
>mysql ... -BNr -e "SELECT 'funny man', 'wonderful' UNION SELECT 'no_space', 'I love spaces';" | while IFS=$'\t' read theme_name guid; do echo "theme: $theme_name guid: $guid"; done
theme: funny man guid: wonderful
theme: no_space guid: I love spaces
You will, of course, want to substitute your own query.
当然，您会想要替换您自己的查询。
回答by GZ92
Pipe '|' to while is dangerous, for the changes inside the loop happen in another subprocess and would not take effect in the current script.
管道'|' to while 是危险的，因为循环内的更改发生在另一个子进程中，并且不会在当前脚本中生效。
By the way, I hate to turn to the external file solution.
顺便说一句，我讨厌转向外部文件解决方案。
I suggest to use "Process Substitute".
我建议使用“进程替代”。
while read field1 field2 field3
do
done < <(mysql -NB -e "$sql")
# ^
# space needed
