how can use bash and mysql with while and output

Question

how can I write the different queries into the same file.

#!/bin/bash
while IFS= read -r line
do
mysql -udata -pdata database <<EOF
SELECT ID,name,grp,Status,ctime FROM database WHERE name LIKE '$line' INTO OUTFILE '/tmp/out.txt'
EOF
done < /tmp/input.txt



Error:
ERROR 1086 (HY000) at line 1: File '/tmp/out.txt' already exists

.

and with ">>" instead of "INTO OUTFILE" is error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>> '/tmp/out.txt'' at line 1

chepner · Accepted Answer · 2019-12-18 17:09:56Z

1

Put the redirection on the command line, not in the here document.

while IFS= read -r line
do
mysql -udata -pdata database <<EOF >> /tmp/out.txt
SELECT ID,name,grp,Status,ctime FROM database WHERE name LIKE '$line'
EOF
done < /tmp/input.txt

or

while IFS= read -r line
do
mysql -udata -pdata database <<EOF
SELECT ID,name,grp,Status,ctime FROM database WHERE name LIKE '$line'
EOF
done < /tmp/input.txt > /tmp/out.txt

Before running this code, though, be sure to read about SQL injection; it is generally a bad idea to construct dynamic SQL queries using string interpolation.

answered Dec 18, 2019 at 17:09

chepner

538k77 gold badges594 silver badges746 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user385411 Over a year ago

I only want to use it once to read a lot of data. it is possible to add: FIELDS TERMINATED BY ';'

Collectives™ on Stack Overflow

how can use bash and mysql with while and output

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related