您的位置: 首页  >  技术问答  >  将mysql结果存储在bash数组变量中

将mysql结果存储在bash数组变量中

分类: 技术问答 • 2022-03-01 13:59:54

将mysql结果存储在bash数组变量中

问题描述：

我正在尝试将MySQL结果存储到全局bash数组变量中，但是我不知道该怎么做.

I am trying to store MySQL result into a global bash array variable but I don't know how to do it.

我应该将MySQL命令结果保存到文件中，然后在for循环中逐行读取文件以进行其他处理吗?

Should I save the MySQL command result in a file and read the file line by line in my for loop for my other treatment?

示例:

user password
Pierre aaa
Paul bbb

命令:

$results = $( mysql –uroot –ppwd –se  « SELECT * from users );

我希望results包含两行.

答

用于将整个表包含到一个bash变量中的映射文件

您可以尝试以下方法:

Mapfile for containing whole table into one bash variable

You could try this:

mapfile result < <(mysql –uroot –ppwd –se  "SELECT * from users;")

比

echo ${result[0]%$'\t'*}
echo ${result[0]#*$'\t'}

或

for row in "${result[@]}";do
    echo Name:  ${row%$'\t'*}  pass: ${row#*$'\t'}
done

Nota ，当按行只有2个字段时，此方法可以正常工作.可能更多，但会变得棘手

Nota This will work fine while there is only 2 fields by row. More is possible but become tricky

while IFS=$'\t' read name pass ;do
    echo name:$name pass:$pass
  done  < <(mysql -uroot –ppwd –se  "SELECT * from users;")

读取并循环以将整个表保存为多个变量:

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]}

新(2018年2月)外壳连接器

有一个小工具(在 github 上)或在我自己的网站上: shellConnector.sh 您可以使用:

New (feb 2018) shell connector

There is a little tool (on github) or on my own site: (shellConnector.sh you could use:

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

命令myMysql myarray ';'将发送;然后执行内联命令， 但是由于mysql不会提供任何内容，因此变量$myarray将不存在.

The command myMysql myarray ';' will send ; then execute inline command, but as mysql wont anwer anything, variable $myarray wont exist.

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

相关推荐