将 mysql 查询输出存储到 shell 变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1636977/
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 query output into a shell variable
提问by imnotneo
I need a variable to hold results retrieved from the database. So far this is basically what I'm trying with no success.
我需要一个变量来保存从数据库中检索到的结果。到目前为止，这基本上是我正在尝试但没有成功的方法。
myvariable=$(mysql database -u $user -p $password | SELECT A, B, C FROM table_a)
My understanding of bash commands is not very good as you can see.
如您所见，我对 bash 命令的理解不是很好。
回答by ennuikiller
A more direct way would be:
更直接的方法是：
myvar=$(mysql mydatabase -u $user -p$password -se "SELECT a, b, c FROM table_a")
回答by falstro
I don't know much about the MySQL command line interface, but assuming you only need help with the bashing, you should try to either swap the commands around like so:
我对 MySQL 命令行界面了解不多，但假设您只需要打击方面的帮助，您应该尝试像这样交换命令：
myvariable=$(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p$password)
which echos the string into MySQL. Or, you can be more fancy and use some new bash-features (the here string)
它将字符串回显到 MySQL 中。或者，您可以更花哨并使用一些新的 bash 功能（here 字符串）
myvariable=$(mysql database -u $user -p$password<<<"SELECT A, B, C FROM table_a")
resulting in the same thing (assuming you're using a recent enough bash version), without involving echo.
导致同样的事情（假设您使用的是足够新的 bash 版本），而不涉及回声。
Please note that the -p$password is not a typo, but is the way MySQL expects passwords to be entered through the command line (with no space between the option and value).
请注意 -p$password 不是拼写错误，而是 MySQL 期望通过命令行输入密码的方式（选项和值之间没有空格）。
Note that myvariable will contain everything that MySQL outputs on standard out (usually everything but error messages), including any and all column headers, ASCII-art frames and so on, which may or may not be what you want.
请注意， myvariable 将包含 MySQL 在标准输出上输出的所有内容（通常除了错误消息之外的所有内容），包括任何和所有列标题、ASCII 艺术帧等，这些内容可能是您想要的，也可能不是。
EDIT:
As has been noted, there appears to be a -eparameter to MySQL, I'd go for that one, definitely.
编辑：
正如已经指出的那样，-eMySQL似乎有一个参数，我肯定会选择那个参数。
回答by Paused until further notice.
To read the data line-by-line into a Bash array you can do this:
要将数据逐行读入 Bash 数组，您可以执行以下操作：
while read -a row
do
echo "..${row[0]}..${row[1]}..${row[2]}.."
done < <(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p $password)
Or into individual variables:
或进入单个变量：
while read a b c
do
echo "..${a}..${b}..${c}.."
done < <(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p $password)
回答by Vinko Vrsalovic
You have the pipe the other way around and you need to echo the query, like this:
您有相反的管道，您需要回显查询，如下所示：
myvariable=$(echo "SELECT A, B, C FROM table_a" | mysql db -u $user -p $password)
Another alternative is to use only the mysql client, like this
另一种选择是只使用 mysql 客户端，像这样
myvariable=$(mysql db -u $user -p $password -se "SELECT A, B, C FROM table_a")
(-sis required to avoid the ASCII-art)
（-s需要避免 ASCII 艺术）
Now, BASH isn't the most appropriate language to handle this type of scenarios, especially handling strings and splitting SQL results and the like. You have to work a lot to get things that would be very, very simple in Perl, Python or PHP.
现在，BASH 不是处理此类场景的最合适语言，尤其是处理字符串和拆分 SQL 结果等。您必须付出很多努力才能获得在 Perl、Python 或 PHP 中非常非常简单的东西。
For example, how will you get each of A, B and C on their own variable? It's certainly doable, but if you do not understand pipes and echo (very basic shell stuff), it will not be an easy task for you to do, so if at all possible I'd use a better suited language.
例如，您将如何获得 A、B 和 C 各自的变量？这当然是可行的，但是如果您不了解管道和回声（非常基本的 shell 内容），这对您来说不是一件容易的事，所以如果可能的话，我会使用更合适的语言。
回答by Isidoros
If you want to use a single value in bash use:
如果要在 bash 中使用单个值，请使用：
companyid=$(mysql --user=$Username --password=$Password --database=$Database -s --execute="select CompanyID from mytable limit 1;"|cut -f1)
echo "$companyid"
回答by puchrojo
myvariable=$(mysql database -u $user -p$password | SELECT A, B, C FROM table_a)
without the blank space after -p. Its trivial, but without don't work.
后没有空格-p。它微不足道，但没有不起作用。
回答by Prasad Tamgale
If you have particular database name and a host on which you want the query to be executed then follow below query:
如果您有特定的数据库名称和要在其上执行查询的主机，请遵循以下查询：
outputofquery=$(mysql -u"$dbusername" -p"$dbpassword" -h"$dbhostname" -e "SELECT A, B, C FROM table_a;" $dbname)
So to run the mysql queries you need to install mysql client on linux
因此，要运行 mysql 查询，您需要在 linux 上安装 mysql 客户端
回答by Ivan Mendoza
Other way:
另一种方式：
Your Script:
你的脚本：
#!/bin/sh
# Set these variables
MyUSER="root" # DB_USERNAME
MyPASS="yourPass" # DB_PASSWORD
MyHOST="yourHost" # DB_HOSTNAME
DB_NAME="dbName"
CONTAINER="containerName" #if use docker
# Get data
data=$($MyHOST -u $MyUSER -p$MyPASS $DB_NAME -h $CONTAINER -e "SELECT data1,data2 from table_name LIMIT 1;" -B --skip-column-names)
# Set data
data1=$(echo $data | awk '{print }')
data2=$(echo $data | awk '{print }')
# Print data
echo $data1 $data2
回答by Xtreme Biker
My two cents here:
我的两分钱在这里：
myvariable=$(mysql database -u $user -p$password -sse "SELECT A, B, C FROM table_a" 2>&1 \
| grep -v "Using a password")
Removes both the column names and the annoying (but necessary) password warning. Thanks @Dominic Bartl and John for this answer.
删除列名和烦人的（但必要的）密码警告。感谢 @Dominic Bartl 和 John 的回答。
回答by Dima
myvariable=$(mysql -u user -p'password' -s -N <<QUERY_INPUT
use databaseName;
SELECT fieldName FROM tablename WHERE filedName='fieldValue';
QUERY_INPUT
)
echo "myvariable=$myvariable"
