bash 读取包含多列和空格的 MySQL 结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43525843/
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
Read MySQL result set with multiple columns and spaces
提问by Jimmy P
Pretend I have a MySQL table testthat looks like:
假设我有一个 MySQL 表test，它看起来像：
+----+---------------------+
| id | value |
+----+---------------------+
| 1 | Hello World |
| 2 | Foo Bar |
| 3 | Goodbye Cruel World |
+----+---------------------+
And I execute the query SELECT id, value FROM test.
How would I assign each column to a variable in Bash using read?
我执行查询SELECT id, value FROM test。我将如何使用 Bash 将每一列分配给变量read？
read -atruncates everything after the first space in value:
read -a截断第一个空格后的所有内容value：
mysql -D "jimmy" -NBe "SELECT id, value FROM test" | while read -a row;
do
id="${row[0]}"
value="${row[1]}"
echo "$id : $value"
done;
and output looks like:
和输出看起来像：
1 : Hello
2 : Foo
3 : Goodbye
but I need it to look like:
但我需要它看起来像：
1 : Hello World
2 : Foo Bar
3 : Goodbye Cruel World
I'm aware there are args I could pass to MySQL to format the results in table format, but I need to parse each value in each row. This is just a simplified example of my problem.
我知道有一些参数可以传递给 MySQL 以将结果格式化为表格格式，但我需要解析每一行中的每个值。这只是我的问题的一个简化示例。
回答by codeforester
Use individual fields in the read loopinstead of the array:
在读取循环中使用单个字段而不是数组：
mysql -D "jimmy" -NBe "SELECT id, value FROM test" | while read -r id value;
do
echo "$id : $value"
done
This will make sure that idwill be read into the idfield and everything else would be read into the valuefield - that's how readbehaves when input has more fields than the number of variables being read into. If there are more columns to be read, using a delimiter (such as @) that doesn't clash with actual data would help:
这将确保id将被读入该id字段，而其他所有内容都将被读入该value字段 - 这就是read当输入的字段多于被读入的变量数量时的行为方式。如果要读取更多列，使用@不与实际数据冲突的分隔符（例如）会有所帮助：
mysql -D "jimmy" -NBe "SELECT CONCAT(id, '@', value, '@', column3) FROM test" | while IFS='@' read -r id value column3;
do
echo "$id : $value : $column3"
done
回答by stobiewankenobi
You can do this, also avoid piping a command to a while read loop if possible to avoid creating a subshell.
您可以这样做，如果可能，也避免将命令传递给 while 读取循环以避免创建子shell。
while read -r line; do
id=$(echo $line | awk '{print }')
value=$(echo $line | awk '{print =""; print #!/bin/bash
declare -A -g arr
while read -r line; do
id=$(echo $line | awk '{print }')
value=$(echo $line | awk '{print =""; print dumbledore@ansible1a [OPS]:~/tmp/tmp > bash test.sh
1: Hello World
2: Foo Bar
3: Goodbye Cruel World
}'|sed ':a;N;$!ba;s/\n/ /g'| sed 's/^[ \t]*//g')
arr[$id]=$value
done< <(mysql -D "jimmy" -NBe "SELECT id, value FROM test")
for key in "${!arr[@]}"; do
echo "$key: ${arr[$key]}"
done
}'|sed ':a;N;$!ba;s/\n/ /g'| sed 's/^[ \t]*//g')
echo "ID: $id"
echo "VALUE: $value"
done< <(mysql -D "jimmy" -NBe "SELECT id, value FROM test")
If you want to store all the id's and values in an array for later use, you can modify it to look like this.
如果您想将所有 id 和值存储在一个数组中以备后用，您可以将其修改为如下所示。
##代码##Which gives you this output
这给你这个输出
##代码##