bash脚本——从数据库中选择到变量

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命令的理解并不如您所见。

9 个解决方案

#1

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中。或者，您可以使用一些新的base -features(这里的字符串)

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版本)，而不涉及echo。

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-art框架等等，这些内容可能是您想要的，也可能不是您想要的。

EDIT:
As has been noted, there appears to be a -e parameter to MySQL, I'd go for that one, definitely.

编辑:如前所述，MySQL似乎有一个-e参数，我肯定会选这个。

#2

A more direct way would be:

更直接的方法是:

myvar=$(mysql mydatabase -u $user -p$password -se "SELECT a, b, c FROM table_a")

#3

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)

#4

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")

(-s is required to avoid the ASCII-art)

(避免使用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 ?这当然是可行的，但是如果您不理解管道和echo(非常基本的shell内容)，那么这对您来说并不是一项容易的任务，所以如果可能的话，我将使用一种更合适的语言。

#5

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"

#6

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后没有空格。它是微不足道的，但没有工作。

#7

myvariable=$(mysql -u user -p'password' -s -N <<QUERY_INPUT
    use databaseName;
    SELECT fieldName FROM tablename WHERE filedName='fieldValue';
QUERY_INPUT
)
echo "myvariable=$myvariable"

#8

Another example when the table name or database contains unsupported characters such as a space, or '-'

另一个例子，当表名或数据库包含不受支持的字符，例如空格，或'-'

db='data-base'

db_d=''
db_d+='`'
db_d+=$db
db_d+='`'

myvariable=`mysql --user=$user --password=$password -e "SELECT A, B, C FROM $db_d.table_a;"`

#9

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)

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客户端

#1