如何将shell脚本写入多行文本从mysql读取文件？

Question

I am facing a problem while writing a shell script. i have table in a database where there is a text field.. i need to write that field into a file.

database fields are id, file_name, text

here is my script

#!/bin/bash
while read -a row
do
cat <<EOF >/root/${row[1]}
${row[2]}
EOF
sleep 1
done < <(echo "SELECT * FROM installation WHERE status = 1" | mysql -h 127.0.0.1 -u root -padmin -D data -s)

I have tried with

echo "${row[2]}" > /root/${row[1]}

But its writing the 1st line only, but the text field is multiline text and i need write full text in that file

please help

Answer 1

By default read -r row will do word splitting by space/tab/new-line. Because the text field from table contains new-lines, word splitting would cut your text result. In this situation it's hard to avoid it.

I recommend you should replace the new-line to another mark, e.g. ### or just new-line, using REPLACE(text, " ", 'new-line')(I am not familiar with sql REPlACE function) in SQL, then get result from shell, then use ${row[2]//new-line/$' '} to convert the new-line back.