在 bash 中处理 MySQL 结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3683424/
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
Processing MySQL result in bash
提问by mark
I'm currently having a already a bash script with a few thousand lines which sends various queries MySQL to generate applicable output for munin.
我目前已经有一个包含几千行的 bash 脚本，它发送各种查询 MySQL 以生成适用于 munin 的输出。
Up until now the results were simply numbers which weren't a problem, but now I'm facing a challenge to work with a more complex query in the form of:
到目前为止，结果只是数字，这不是问题，但现在我面临着以以下形式处理更复杂查询的挑战：
$ echo "SELECT id, name FROM type ORDER BY sort" | mysql test
id name
2 Name1
1 Name2
3 Name3
From this result I need to store the id and name (and their respective association) and based on the IDs need to perform further queries, e.g. SELECT COUNT(*) FROM somedata WHERE type = 2and later output thatresult paired with the associated namecolumn from the first result.
从这个结果，我需要存储的ID和名称（及其各自的关联），并基于这些ID需要进行进一步的查询，如SELECT COUNT(*) FROM somedata WHERE type = 2后来输出该结果与相关成对name从第一个结果列。
I'd know easily how to do it in PHP/Ruby , but I'd like to spare to fork another process especially since it's polled regularly, but I'm complete lost where to start with bash.
我很容易知道如何在 PHP/Ruby 中做到这一点，但我想分叉另一个进程，特别是因为它定期轮询，但我完全迷失了从哪里开始使用 bash。
Maybe using bash is the wrong approach anyway and I should just fork out?
也许无论如何使用 bash 都是错误的方法，我应该放弃吗？
I'm using GNU bash, version 3.2.39(1)-release (i486-pc-linux-gnu).
我正在使用GNU bash, version 3.2.39(1)-release (i486-pc-linux-gnu).
采纳答案by O. Jones
You're going to "fork out," as you put it, to the mysql command line client program anyhow. So either way you're going to have process-creation overhead. With your approach of using a new invocation of mysql for each query you're also going to incur the cost of connecting to and authenticating to the mysqld server multiple times. That's expensive, but the expense may not matter if this app doesn't scale up.
无论如何，您将“分叉”到 mysql 命令行客户端程序。因此，无论哪种方式，您都会有流程创建开销。通过为每个查询使用新的 mysql 调用的方法，您还将产生多次连接到 mysqld 服务器并对其进行身份验证的成本。这很昂贵，但如果此应用程序不扩展，费用可能无关紧要。
Making it secure against sql injection is another matter. If you prompt a user for her name and she answers "sally;drop table type;" she's laughing and you're screwed.
使其免受 sql 注入的影响是另一回事。如果您提示用户输入她的名字并且她回答“sally;drop table type;” 她在笑，你就完蛋了。
You might be wise to use a language that's more expressive in the areas that are important for data-base access for some of your logic. Ruby, PHP, PERL are all good choices. PERL happens to be tuned and designed to run snappily under shell script control.
在对某些逻辑的数据库访问很重要的领域中，使用一种更具表现力的语言可能是明智之举。Ruby、PHP、PERL 都是不错的选择。PERL 恰好经过调整和设计，可以在 shell 脚本控制下快速运行。
回答by Paused until further notice.
You would use a while readloop to process the output of that command.
您将使用while read循环来处理该命令的输出。
echo "SELECT id, name FROM type ORDER BY sort" | mysql test | while read -r line
do
# you could use an if statement to skip the header line
do_something "$line"
done
or store it in an array:
或将其存储在数组中：
while read -r line
do
array+=("$line")
done < <(echo "SELECT id, name FROM type ORDER BY sort" | mysql test)
That's a general overview of the technique. If you have more specific questions post them separately or if they're very simple post them in a comment or as an edit to your original question.
这是该技术的一般概述。如果您有更具体的问题，请将它们单独发布，或者如果它们非常简单，请将它们发布在评论中或作为对原始问题的编辑。
回答by Leo
My example is not Bash, but I'd like to point out my parameters at invoking the mysql command, they surpress the boxing and the headers.
我的例子不是 Bash，但我想指出我在调用 mysql 命令时的参数，它们抑制了装箱和标题。
#!/bin/sh
mysql dbname -B -N -s -e "SELECT * FROM tbl" | while read -r line
do
echo "$line" | cut -f1 # outputs col #1
echo "$line" | cut -f2 # outputs col #2
echo "$line" | cut -f3 # outputs col #3
done
