how to use mysql fields in shell

I have table ,and i need access them from shell script and do the following.

> USERNAME="usr" PASSWORD="pwd"
> DBNAME="mydb"
> 
> mysql -u$USERNAME -p$PASSWORD $DBNAME<<EOF 
> selectfield1,field2,field3,field4 from table;
> EOF

gives me the records.But i need to process each fields wi开发者_JAVA百科th normal linux commands-

for example;

> mysql -u$USERNAME -p$PASSWORD $DBNAME<<EOF
>  select field1,field2,field3,field4 from table;
> EOF 
> scp field1@field2: field3@field4:/tmp && rm -rf field2

something like that - basic stuff is to use the database records for invoking linux commands on local machine.

One approach would be to use this construct (try it out, the syntax is a bit weird):

cat << EOF | while read a b c ; do echo "a:$a b:$b c:$c" ; done
one two three
EOF

[Edit: obviously replace cat and one two three with your actual sql stuff]

You will need to make sure that mysql does not print anything other than the result (no logo/copyright notice/version info header etc...) - I believe there are command line switches for that.

You'll also run into problems if there are spaces in the data returned, or if it's separated by something else than just whitespace. For those, you can usually get away with setting $IFS to something that never happens in your columns (maybe |), and making your query output that character between two fields.

Thanks for the answers and suggestions - But I found following method much more easier. Is it safe to use this method?

variable=`mysql -u$USERNAME -p$PASSWORD $DBNAME <<EOF  
 select name,pass,email,flag from UserRemap where flag="Y" ;  
EOF` 

echo $variable 

name=`echo $variable | cut -d' ' -f5`

pass=`echo $variable | cut -d' ' -f6` 

mail=`echo $variable | cut -d' ' -f7`